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