DBMS_STATS Package Components

In Oracle Database 23c, the `DBMS_STATS` package is a core component of the cost-based optimizer (CBO) infrastructure. It is used to gather, manage, and manipulate optimizer statistics for database objects. These statistics influence the optimizer's decisions about the most efficient way to execute SQL queries. The `DBMS_STATS` package consists of several components, which can be grouped into functional categories:

1. Statistics Gathering Procedures
   • GATHER_DATABASE_STATS
   • GATHER_SCHEMA_STATS
   • GATHER_TABLE_STATS
   • GATHER_INDEX_STATS
   • GATHER_FIXED_OBJECTS_STATS (for X$ tables and V$ views)
   • GATHER_DICTIONARY_STATS (for dictionary tables)
   • Parameters for fine-tuning:
     • CASCADE
     • ESTIMATE_PERCENT
     • METHOD_OPT
     • DEGREE
2. Statistics Management Procedures
   • EXPORT_*_STATS
   • IMPORT_*_STATS
   • DELETE_*_STATS
   • RESTORE_*_STATS
   • CREATE_STAT_TABLE / DROP_STAT_TABLE – for storing exported stats
3. Preferences Management
   • SET_*_PREFS – e.g., SET_TABLE_PREFS, SET_SCHEMA_PREFS
   • GET_*_PREFS – retrieve current preference settings
   • DELETE_*_PREFS – remove preferences
   • Preferences include:
     • STALE_PERCENT
     • ESTIMATE_PERCENT
     • INCREMENTAL
     • GRANULARITY
4. Incremental Statistics and Hybrid Histograms
   • INCREMENTAL statistics
   • Hybrid histograms and Top-N frequency histograms
   • Use of synopsis data (for partitioned tables)
   • APPROXIMATE_NDV for cardinality estimation

System Statistics and Optimizer Features

• GATHER_SYSTEM_STATS – collects stats about the system I/O and CPU performance
• SET_SYSTEM_STATS, DELETE_SYSTEM_STATS
• GET_SYSTEM_STATS – fetch current system-level stats

Reporting and Diagnostics

• REPORT_STATS_OPERATIONS – shows stats jobs history
• SHOW_*_STATS – to review object statistics
• DIFF_TABLE_STATS_* – compare two sets of stats
• VALIDATE_STATS – verify the presence and quality of statistics

Advanced Features in Oracle 23c

• AutoStats Advisor integration
• SQL Plan Management (SPM) tie-in
• Hybrid partitioning-aware gathering
• Extended statistics (multi-column, expressions)
• Better handling of in-memory statistics

Scheduler Integration

• Via AutoTask framework
• Configured using DBA_AUTOTASK_* views
• Controlled using ENABLE, DISABLE, SET_GLOBAL_PREFS

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.

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.

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.