DBMS_STATS Package

DBMS_STATS

DBMS_STATS Package:-The DBMS_STATS package is used for gathering statistics. This package is also used to modify, view, export, import, and delete statistics.
The DBMS_STATS package can gather statistics on table, indexes and individual columns and partitions of tables.


Note:-When we generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. The older statistics are saved and can be restored later if necessary.
Below are the examples to gather statistics through DBMS_STATS


1) Complete Database Stats :-

EXEC DBMS_STATS.GATHER_DATABASE_STATS; 
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>20); 


Note:- Never run "GATHER_DATABASE_STATS" in production,it will take huge time depend on the size of Database

 2) Complete Schema Stats :-

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, options, statown, no_invalidate, gather_temp, gather_fixed); 
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'HR');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',ESTIMATE_PERCENT=>10); EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);
 

3) Table Stats :- 

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',ESTIMATE_PERCENT=>15);

 4) Table Partition Stats :- 

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'ORDERS', GRANULARITY => 'PARTITION',PARTNAME => 'P_1997');

 5) Index Stats:- 

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','IDX_EMPNO'); 
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','IDX_EMPNO',ESTIMATE_PERCENT=>15);


Delete Stats:-We can delete statistics by this package

EXEC DBMS_STATS.DELETE_DATABASE_STATS; 
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT'); 
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT','EMP'); 
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','EMP_PK'); 
EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','SALES');


Data Dictionaries where Stats available :-

    DBA_TAB_STATISTICS
    DBA_TAB_COL_STATISTICS
    DBA_TAB_HISTOGRAMS
    DBA_TABLES
    DBA_OBJECT_TABLES
    DBA_TAB_HISTOGRAMS
    DBA_INDEXES
    DBA_IND_STATISTICS
    DBA_CLUSTERS
    DBA_TAB_PARTITIONS
    DBA_TAB_SUBPARTITIONS
    DBA_IND_PARTITIONS
    DBA_IND_SUBPARTITIONS
    DBA_PART_COL_STATISTICS
    DBA_PART_HISTOGRAMS
    DBA_SUBPART_COL_STATISTICS
    DBA_SUBPART_HISTOGRAMS