PL/SQL Packages DBMS_STATS

Gather, View, Modify or Delete optimizer statistics for database objects.

Subprocedures:
GATHER_DATABASE_STATS
    (estimate_percent,block_sample,method_opt,degree, granularity,cascade,stattab, 
statid, options,statown,gather_sys,no_invalidate,gather_temp,gather_fixed,stattype); GATHER_INDEX_STATS (ownname,indname,partname,estimate_percent,stattab,statid
statown,degree,granularity,no_invalidate,stattype); GATHER_SCHEMA_STATS (ownname,estimate_percent,block_sample,method_opt,degree,granularity,cascade,
stattab,statid,options,statown,no_invalidate,gather_temp,gather_fixed); GENERATE_STATS (ownname,objname,organized); GATHER_SYSTEM_STATS (gathering_mode,interval,stattab,statid,statown); GATHER_TABLE_STATS (ownname,tabname,partname,estimate_percent,block_sample,method_opt,
degree,granularity,cascade,stattab,statid,statown,no_invalidate,stattype); PREPARE_COLUMN_VALUES (srec,values); SET_COLUMN_STATS SET_INDEX_STATS SET_SYSTEM_STATS SET_TABLE_STATS CONVERT_RAW_VALUE GET_COLUMN_STATS GET_INDEX_STATS GET_SYSTEM_STATS GET_TABLE_STATS DELETE_COLUMN_STATS (ownname,tabname,colname,partname,stattab,statid,
cascade_parts,statown,no_invalidate,force); DELETE_DATABASE_STATS (stattab,statid,statown,no_invalidate,stattype,force); DELETE_INDEX_STATS (ownname,indname,partname,stattab,statid,cascade_parts,statown
no_invalidate,stattype,force); DELETE_SCHEMA_STATS (ownname,stattab,statid,statown,no_invalidate
stattype,force); DELETE_SYSTEM_STATS (stattab,statid,statown); DELETE_TABLE_STATS (ownname,tabname,partname,stattab,statid,cascade_parts,cascade_columns,
cascade_indexes,statown,no_invalidate,stattype,force); CREATE_STAT_TABLE DROP_STAT_TABLE EXPORT_COLUMN_STATS EXPORT_INDEX_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS EXPORT_SCHEMA_STATS EXPORT_DATABASE_STATS IMPORT_COLUMN_STATS IMPORT_INDEX_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS IMPORT_SCHEMA_STATS IMPORT_DATABASE_STATS FLUSH_SCHEMA_MONITORING_INFO FLUSH_DATABASE_MONITORING_INFO ALTER_SCHEMA_TABLE_MONITORING ALTER_DATABASE_TABLE_MONITORING

Oracle Corporation recommend setting the ESTIMATE_PERCENT parameter of the gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE. This will maximize performance gains while achieving good statistical accuracy.

Examples:

To collect stats for the schema SCOTT:

EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

Alternatively specify the parameters explicitly, cascade will include indexes:

EXEC DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

To schedule this command to run regularly see the DBMS_JOB package


SYS/SYSTEM schema:
In version 9i and greater gathering statistics for SYS/SYSTEM is supported, in previous versions it
is not recommended and not supported. Although you are now free to analyse the system schema I havent seen any great performance benefits (or penalties) from doing so.

Delete Statistics:

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

To check if statistics have been deleted from a schema:

SELECT count(*) from user_histograms;
or
SELECT * from user_tables WHERE avg_space is not null;
This will return 'no rows selected' when no stats are present.

In a small test environment the ability to modify the statistics allows you to simulate running a large production database.

The DBMS_STATS package is available for Oracle 8i and above, for Oracle 7 use DBMS_UTILITY

"The only people for me are the mad ones, the ones who are mad to live, mad to talk, mad to be saved; the ones who never yawn or say a commonplace thing, but burn, burn, burn, like fabulous yellow roman candles exploding like spiders across the stars." - Jack Kerouac

Related Commands:

DBMS_UTILITY.ANALYZE_SCHEMA
DBMS_DDL - compile/analyze objects
DBMS_JOB - Schedule PL/SQL procedures
DBMS_IOT.BUILD_CHAIN_ROWS_TABLE
ANALYZE - Analyse Table and/or Index
EXPLAIN PLAN
STATISTICS - ASSOCIATE STATISTICS
STATISTICS - DISASSOCIATE STATISTICS
Oracle9i Database Performance Tuning Guide and Reference - how to use DBMS_STATS
Oracle9i Supplied PL/SQL Packages and Types Reference - full description of the DBMS_STATS package
Oracle Built in Packages by Steven Feuerstein et al

Related Views:

                                                               INDEX_STATS
 DBA_PART_COL_STATISTICS  ALL_PART_COL_STATISTICS USER_PART_COL_STATISTICS 
 DBA_SUBPART_COL_STATISTICS  ALL_SUBPART_COL_STATISTICS  USER_SUBPART_COL_STATISTICS 
 DBA_TAB_COL_STATISTICS   ALL_TAB_COL_STATISTICS  USER_TAB_COL_STATISTICS 
 DBA_USTATS               ALL_USTATS              USER_USTATS

Equivalent SQL Server command:

CREATE STATISTICS


© Copyright SS64.com 1999-2013
Some rights reserved