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.

DEMO:

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

 

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'EMP_TAB' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SCOTT','EMP_TAB')
--------------------------------------------------------------------------------
TRUE

SQL> exec dbms_stats.set_table_prefs('SCOTT','EMP_TAB','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'EMP_TAB' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SCOTT','EMP_TAB')
--------------------------------------------------------------------------------
FALSE

 

Now gather stats:

 

SQL> select sysdate from dual;

SYSDATE
---------
28-AUG-15

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP_TAB',degree=> 4 ,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

 

Check the last_analyzed date in dba_tables and DBA_TAB_PENDING_STATS

 

  1* select owner,table_name,last_analyzed from dba_tables where table_name='EMP_TAB'
SQL> /

OWNER	    TABLE_NAME LAST_ANAL
----------- ---------- ---------
SCOTT	    EMP_TAB       05-AUG-15


SQL> SELECT TABLE_NAME,LAST_ANALYZED  FROM DBA_TAB_PENDING_STATS;

TABLE_NAME LAST_ANAL
---------- ---------
EMP_TAB	   28-AUG-15

 

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.

 

alter session set optimizer_use_pending_statistics=TRUE;

Session altered.

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:

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SCOTT','EMP_TAB');

PL/SQL procedure successfully completed.

SQL> select owner,TABLE_NAME,LAST_ANALYZED  FROM DBA_TAB_PENDING_STATS;


no rows selected
SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='EMP_TAB';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
SCOTT	     EMP_TAB	  28-AUG-15

We can see there are no pending statistics.