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:

1. 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.
2. 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.
3. GATHER_DATABASE_STATS: This procedure collects statistics for the entire database. It can be resource-intensive and is typically scheduled during off-peak hours. It supports various options, including gathering statistics for all schemas or just for stale objects.
4. 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 index-based access paths.
5. 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.
6. 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.
7. 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.
8. GET_INDEX_STATS: This function retrieves the current statistics for an index, providing insight into how the optimizer views the index's data distribution.
9. 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.
10. 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.
11. 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 up-to-date information, leading to efficient execution plans and optimal database performance.

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.

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:

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.