Query Optimizer  «Prev  Next»

Lesson 5What is DBMS_STATS?
ObjectiveComponents 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:
  1. 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.
  2. 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_VALUE), 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.