eMarcel.com

Oracle Database, Fusion Middleware, Linux

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!!

(Visited 442 times, 1 visits today)

, ,

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close