In major production database, sometimes gathering stats can degrade the performance of the queries.

So what we can do it, first gather the stats of the table or schema,without publishing it. After testing, once we confirm that stats is not degrading the performance,Then we can go ahead and publish the stats.


First change the publish mode, so that stats won’t be published by default



Now gather stats:



Check the last_analyzed date in dba_tables and DBA_TAB_PENDING_STATS



We can see the last_analyzed date in dba_tables is old one , but it is showing as pending one in dba_tab_pending_stats.

Now we can test the whether stats are good or not at the session level by enabling below parameter.


You can run query in this session, test whether query performance is fine or not.
If  you can’t run the queries in this session, then another way is to  export the stats and import the same in your clone of the production database(PRE-PROD) , and test the queries.

Check :How to export and import stats in oracle :

Once testing is successful , if you decided to publish the stats, then

Publish pending statistics:

We can see there are no pending statistics.