Lesson 5  What is DBMS_STATS? 
Objective  Components and uses of DBMS_STATS package. 
ANALYZE
command was used to collect statistics.
In Oracle, the ANALYZE
command has been replaced by a package^{[1]} of PL/SQL procedures called DBMS_STATS
.
DBMS_STATS
package differ from the options offered by the ANALYZE
command:
DBMS_STATS
package are run with parallelism^{[2]},
whereas the ANALYZE
command does not take advantage of the parallel capabilities of Oracle.
DBMS_STATS
command allows you to gather statistics and store them outside of the data dictionary's capability that is explored in more detail in the next module.
DBMS_STATS
, as detailed in the following table.
The table lists the overall purpose of a group of procedures, the procedures used for that purpose, and a brief description of the procedure:
Purpose  Procedures  Description 
Gather statistics 
GATHER_INDEX_STATS GATHER_TABLE_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS GENERATE_STATS

The GATHER_STATS packages collect statistics from tables and indexes. The GENERATE_STATS procedure
generates the statistics for an object from a group of related objects.

Delete statistics 
DELETE_COLUMN_STATISTICS DELETE_TABLE_STATISTICS DELETE_INDEX_STATISTICS DELETE_SCHEMA_STATISTICS DELETE_DATABASE_STATISTICS

Removes any existing statistics from the data dictionary or an external table, if specified.

Use external table to store statistics 
CREATE_STAT_TABLE DROP_STAT_TABLE

Creates and drops an external table to store statistics. 
Export statistics 
EXPORT_COLUMN_STATISTICS EXPORT_TABLE_STATISTICS EXPORT_INDEX_STATISTICS EXPORT_SCHEMA_STATISTICS EXPORT_DATABASE_STATISTICS

Exports statistics to an external table from the data dictionary. 
Import statistics 
IMPORT_COLUMN_STATISTICS IMPORT_TABLE_STATISTICS IMPORT_INDEX_STATISTICS IMPORT_SCHEMA_STATISTICS IMPORT_DATABASE_STATISTICS

Imports statistics from an external table to the data dictionary. 
Convert values 
CONVERT_RAW_VALUE PREPARE_COLUMN_VALUES

Converts values to (PREPARE_COLUMN_VALUE ) and from (CONVERT_RAW_VALU E), the Oracle internal format for
statistics.

Set statistics 
SET_COLUMN_STATS SET_INDEX_STATS SET_TABLE_STATS

You can use these procedures to set values for statistics. Use only when you have a complete understanding of how statistics are used by the optimizer. 
Get statistics 
GET_COLUMN_STATS GET_INDEX_STATS GET_TABLE_STATS

Used to retrieve values from statistics tables. 