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');

6.1 To automate the process (make it one line in SQL Editor):

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';