DBMS_STATS Package(Components and uses)

Lesson 5	What is DBMS_STATS?
Objective	Components and uses of DBMS_STATS package.

DBMS_STATS Package

Describe the components and uses of the DBMS_STATS package.
The decisions of the cost-based optimizer are all based on statistical information about the composition of the tables and indexes used by a query. In previous releases of the Oracle database, the 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.

How is DBMS_STATS different?

There are two basic ways that the procedures in the DBMS_STATS package differ from the options offered by the ANALYZE command:

Internally, the procedures in the DBMS_STATS package are run with parallelism^[2], whereas the ANALYZE command does not take advantage of the parallel capabilities of Oracle.
Functionally, the 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.

Components of the DBMS_STATS package

There are a number of procedures in the 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.

In the next lesson, you will learn how to use statistics stored outside of the data dictionary.

DBMS_STATS - Quiz

Click the Quiz link below to test your knowledge of Oracle optimization concepts.
DBMS_STATS - Quiz

[1]Package: A group of PL/SQL procedures that are combined into a single logical grouping. The package header contains the interface description for the procedures, whereas the package body contains the actual code for the procedures in the package.

[2]Parallelism:The ability of the Oracle database to divide a single request into multiple tasks that will execute in parallel, which usually results in reduced execution time.