Lesson 6 | Handling statistics with DBMS_STATS |
Objective | Use 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:
- How will you know if the new statistics will result in an optimal execution plan for a set of particular queries?
- 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.
- The CREATE_STAT_TABLE procedure creates a table to hold statistics of the data dictionary
- 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
- You would test optimizations with the new statistics at this point
- f the new statistics result in poor execution plans, you begin the process of restoring the old statistics by dropping the existing statistics
- 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:
- creating an external table for storing statistics,
- exporting existing statistics to the table,
- gathering a new set of statistics,
- importing the previously saved statistics, and
- dropping the external statistics table.
Create Stat Table
In the next lesson, you will learn how to monitor operations in the database.