Lesson 5  What is DBMS_STATS? 
Objective  Components and uses of DBMS_STATS package. 
DBMS_STATS Package
The Oracle `DBMS_STATS` package is a critical component for managing optimizer statistics in an Oracle database. These statistics are essential for the Oracle Cost Based Optimizer (CBO) to make informed decisions about the most efficient way to execute a query. The `DBMS_STATS` package provides a comprehensive suite of procedures and functions to collect, manipulate, and view statistics for database objects. Key components of the `DBMS_STATS` package include:
 GATHER_TABLE_STATS: This procedure collects statistics for a table, its columns, and its indexes. It allows for various options such as granularity of the statistics, estimate percentage, and method of data sampling.
 GATHER_SCHEMA_STATS: This procedure is used to gather statistics for all objects in a schema. It's particularly useful for routine maintenance tasks, allowing DBAs to maintain statistics at the schema level with various options for optimization and efficiency.
 GATHER_DATABASE_STATS: This procedure collects statistics for the entire database. It can be resourceintensive and is typically scheduled during offpeak hours. It supports various options, including gathering statistics for all schemas or just for stale objects.
 GATHER_INDEX_STATS: This specific procedure is used for collecting statistics on individual indexes. It helps in understanding the distribution of data within an index, aiding the optimizer in making better choices for indexbased access paths.
 SET_TABLE_STATS: This function allows for manually setting the statistics for a table. It is useful in cases where the actual data distribution is known and does not change frequently, or when exact values need to be tested for performance tuning.
 SET_INDEX_STATS: Similar to `SET_TABLE_STATS`, this function allows for manually setting the statistics for an index. It's used in specialized situations where the DBA needs to override the statistics gathered by Oracle.
 GET_TABLE_STATS: This function retrieves the current statistics of a table. It is useful for examining the statistics that the optimizer is using to make decisions.
 GET_INDEX_STATS: This function retrieves the current statistics for an index, providing insight into how the optimizer views the index's data distribution.
 DELETE_TABLE_STATS, DELETE_SCHEMA_STATS, and DELETE_DATABASE_STATS: These procedures are used to remove statistics for a table, an entire schema, or the entire database, respectively. Removing statistics might be necessary when the data has changed significantly, and the existing statistics no longer represent the current state.
 EXPORT_SCHEMA_STATS and IMPORT_SCHEMA_STATS: These procedures are used to export statistics from one schema and import them into another. This is particularly useful for transferring statistics from a production environment to a test environment where the data distribution is expected to be the same.
 LOCK_TABLE_STATS and UNLOCK_TABLE_STATS: These procedures are used to lock and unlock statistics for a table. Locking statistics prevents them from being overwritten by automated gather jobs, which is useful when the DBA wants to preserve specific statistics.
The `DBMS_STATS` package is essential for performance tuning and is a powerful tool in the hands of an Oracle DBA. Proper use of this package ensures that the optimizer has accurate and uptodate information, leading to efficient execution plans and optimal database performance.
Describe the components and uses of the DBMS_STATS package. The decisions of the costbased 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.