Saturday, 18 November 2017

DBMS_STATS Package in Oracle

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

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...