Query Optimizer  «Prev  Next»

Lesson 6 Handling statistics with DBMS_STATS
ObjectiveUse DBMS_STATS to collect and move statistics.

Statistics Package Manipulation in Oracle

You have seen how statistics are the basis for many of the decisions of the cost-based optimizer. As the statistics for a database change, they can influence the decisions made by the cost-based optimizer. Once you update the statistics in the data dictionary, the cost-based optimizer uses the new statistics.
2 Questions that one must ask:
  1. How will you know if the new statistics will result in an optimal execution plan for a set of particular queries?
  2. Once the new statistics are gathered, are the old ones lost?

Using DBMS_STATS to preserve Statistics

You may have been wondering why DBMS_STATS includes procedures to collect statistics that are not stored in the data dictionary. Because the optimizer only uses the dictionary statistics, and saving statistics outside of the dictionary will not influence the optimizier.
The key lies in the ability to export and import statistics from the data dictionary. By exporting statistics from the data dictionary to an external table, you preserve them, even after running another procedure in DBMS_STATS. You can import the saved statistics later to overwrite the newly gathered statistics. This capability means you can test the new statistics to see if they have a positive effect on the optimization of your queries. If the new statistics are not satisfactory, you can import the old, saved statistics.
To understand the ability to import and export statistics, you have to understand the use of some parameters for the import and export procedures, as shown in the following Slide Show.

  1. The CREATE_STAT_TABLE procedure creates a table to hold statistics of the data dictionary
  2. The GATHER_TABLE_STATS collects new statistics on the emp table, and also exports the existing statistics to the stattab table before collecting the new stats
  3. You would test optimizations with the new statistics at this point
  4. f the new statistics result in poor execution plans, you begin the process of restoring the old statistics by dropping the existing statistics
  5. The final restoration step is to import the saved statistics

Dbms Stats Create Stat Table

Oracle DBA tasks using the DBMS_STATS package

An Oracle DBA should be able to perform the following tasks using the DBMS_STATS package:
  1. creating an external table for storing statistics,
  2. exporting existing statistics to the table,
  3. gathering a new set of statistics,
  4. importing the previously saved statistics, and
  5. dropping the external statistics table.

Create Stat Table
In the next lesson, you will learn how to monitor operations in the database.