DBMS_STATS in an Oracle Database

DBMS_STATS in an Oracle Database

Using DBMS_STATS package to gather Oracle dictionary statistics.

1. To gather table statistics with DBMS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname =>'EMP')

2. To gather table statistics with DBMS_STATS with estimate percent

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'EMP', estimate_percent =>50);   

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('HR', estimate_percent =>50);

3. To gather a partition’s statistics in full

SQL> EXECUTE DBMS_STATS.gather_table_Stats( ownname => 'SCOTT', tabname => 'SOME_TABLE', partname => 'P69');

  4. To gather statistics on a partition without calculating statistics for indexes, in case of big table’s partitions and lots of indexes that we want to skip them:

EXECUTE DBMS_STATS.gather_table_stats ( ownname=>'HR', tabname=>'EMP', partname=>'P69', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>FALSE, degree=>DBMS_STATS.AUTO_DEGREE, granularity=>'PARTITION' );

This will work exclusively and only on the partition specified (here P69) ignoring all indexes.

4. To gather stats on schema:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'HR', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

5. To gather stats on schema on particular table

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SESSIONS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

6. To gather stats on indexes of particular user schema

exec dbms_stats.gather_index_stats(  
  ownname=> 'EB_OLTP'  
  ,indname=>'PK_MESSENGER_GROUP'  
  ,estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE
  ,degree=> 6  
  ,no_invalidate=> DBMS_STATS.AUTO_INVALIDATE  
  ,granularity=> 'AUTO');

select 'exec dbms_stats.gather_index_stats(  
 ownname=> '''||owner||'''  
 ,indname=>''' || index_name ||'''  
 ,estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE
 , degree=> 6  
 ,no_invalidate=> DBMS_STATS.AUTO_INVALIDATE  
 ,granularity=> ''AUTO'');' 
from dba_indexes where owner ='HR';