PL/SQL Packages DBMS_STATS

Gather, View, Modify and Delete optimizer statistics for database objects.
In a small test environment the ability to modify stats allows you to simulate running a large production database.

Subprocedures:

PREPARE_COLUMN_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 
DELETE_INDEX_STATS 
DELETE_SYSTEM_STATS 
DELETE_TABLE_STATS 
DELETE_SCHEMA_STATS 
DELETE_DATABASE_STATS 

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 

GATHER_INDEX_STATS 
GATHER_TABLE_STATS 
GATHER_SCHEMA_STATS 
GATHER_DATABASE_STATS 
GATHER_SYSTEM_STATS 

GENERATE_STATS

FLUSH_SCHEMA_MONITORING_INFO 
FLUSH_DATABASE_MONITORING_INFO 

ALTER_SCHEMA_TABLE_MONITORING 
ALTER_DATABASE_TABLE_MONITORING   

Examples:

To gather stats for the current schema:

EXEC DBMS_STATS.gather_schema_stats()

The above will COMPUTE global and partition-level statistics but not subpartition stats.

To collect stats for the schema SCOTT:

EXEC DBMS_STATS.gather_schema_stats('SCOTT',,,'FOR ALL INDEXED COLUMNS','ALL')

The above will gather ALL statistics for all indexed columns of the SCOTT schema:

Do not collect stats for either the SYS or SYSTEM schemas.

Stats can be removed thus:

CONNECT SYS/password
EXEC DBMS_STATS.delete_schema_stats('SYS')

To check if statistics have been deleted from a schema:

SELECT * FROM user_tables WHERE avg_space is not null;
This will return 'no rows selected' when no stats are present.

This package is only available for Oracle8i and above, however if you have Oracle 7.3.4 or 8.0 you will have access to the procedures supplied in DBMS_UTILITY

For full documentation of the packaged procedures above see the Oracle Manual:
"Oracle9i Supplied PL/SQL Packages and Types Reference"
or the book Oracle Built in Packages by Steven Feuerstein et al

"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_IOT.BUILD_CHAIN_ROWS_TABLE

ANALYZE - Analyse Table | Index
EXPLAIN PLAN
STATISTICS - ASSOCIATE STATISTICS
STATISTICS - DISASSOCIATE STATISTICS

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