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