PL/SQL Packages DBMS_UTILITY

Utility routines, Analyze, Time, Conversion etc.

Subprocedures:

COMPILE_SCHEMA   Compile all procedures, functions, packages, and
                 triggers in the specified schema. 

ANALYZE_SCHEMA   Analyze all the tables, clusters, and indexes in
                 a schema. 

ANALYZE_DATABASE Analyze all the tables, clusters, and indexes in
                 a database. 

FORMAT_ERROR_STACK Function 
                 Format the current error stack. 

FORMAT_CALL_STACK Function 
                 Format the current call stack. 

IS_CLUSTER_DATABASE Function 
                 Is this database running in cluster database mode. 

GET_TIME Function 
                 Current time in 100th's of a second. 

GET_PARAMETER_VALUE Function 
                 Get an init.ora parameter. 

NAME_RESOLVE     Resolve the given name. 

NAME_TOKENIZE    Parse the given name. 

COMMA_TO_TABLE   Convert a comma-separated list of names into a
                 PL/SQL table of names. 

TABLE_TO_COMMA   Converts a PL/SQL table of names into a comma-separated
                 list of names. 

PORT_STRING Function 
                 Return a string that uniquely identifies the version of Oracle
                 and the operating system. 

DB_VERSION       Version information for the database. 

MAKE_DATA_BLOCK_ADDRESS Function 
                 Create a data block address given a file number and a block number. 

DATA_BLOCK_ADDRESS_FILE Function 
                 Get the file number part of a data block address. 

DATA_BLOCK_ADDRESS_BLOCK Function 
                 Get the block number part of a data block address. 

GET_HASH_VALUE Function 
                 Compute a hash value for the given string. 

ANALYZE_PART_OBJECT 
                 Equivalent to ANALYZE TABLE|INDEX [<object_name>.]

EXEC_DDL_STATEMENT Execute the DDL statement in parse_string. 

CURRENT_INSTANCE Function 
                 Return the current connected instance number. 

ACTIVE_INSTANCES Active instance numbers and names 

Examples:

To collect stats for the schema SCOTT:

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE')

Do not collect stats for either the SYS or SYSTEM schemas.
Stats can be removed thus:

CONNECT SYS/password
EXEC DBMS_UTILITY.analyze_schema('SYS','DELETE')

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.

Analyze_schema() is available in Oracle version 7.3.4 and above, however if you have Oracle 8i or 9 you will obtain faster and better statistics from DBMS_STATS.GATHER_SCHEMA_STATS

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 fitter you are, the better you will survive. Start training now - SAS Survival Guide

Related Commands:

DBMS_DDL
DBMS_STATS.GATHER_SCHEMA_STATS
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:

 V$TIMER                                                       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