Oracle Database

How To Save Database Statistics Before They Are Updated

Step-by-step guide

Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload).

The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very generic. First, create an instance of the table using DBMS_STATS.CREATE_STAT_TABLE

The next two steps are to export data from the current schema. The column STATID identifies a particular set of statistics within this table. It is possible to do multiple exports into a single StatTable by using a different STATID. In this case I am using “CURRENT_STATS” as the STATID.

Export the Table, Index, and Column Statistics

Then Export system statistics (sys.aux_stats$)

If we look at the contents of MY_STATS_TABLE, we will see rows for each different statistic type (T=Table, I=Index, C=Column, S=System)

This table can be exported (Export or Datapump) and imported into another database. If the schema is the same, then the statistics can be imported. (Remember to clear the shared pool anytime statistics are updated)

In this way we can guarantee that queries are using the same statistics for optimization. This is the case even if the underlying data is different. Oracle only uses the stored statistics to perform query optimization (except in the case where there are no statistics; then dynamic sampling is used). This technique is an invaluable way to share optimizer statistics and diagnose query plan problems.

Lets see how long historic statistics are kept for.

If we want to restore the statistics, we can perform the following:

To find statistics history of a particular table:

Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.