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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7734298/viewspace-680627/，如需轉載，請註明出處，否則將追究法律責任。