Restoring the statistics – Oracle Database 10g

Here is the small article on how to restore the statistics on a table. Sometimes we gather stats on a table which causes it to flip the plan of a query accessing that table. It can lead to a great performance for some queries but there are situations where the query performance can degrade.

Certain tables in every databases are “Hot” tables and a DBA should not play around the statistics of those tables as there could be quiet a huge impact of the same.

In case if  a stats are gathered and you need to restore the previous stats in order to bring everything back to normal, here is what one should be doing.


Step 1) Check the history of stats gathered on the table using DBA_TAB_STATS_HISTORY

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name = 'PICKED_SHIPMENT_BATCHES';
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ----------------------------------------

STATS_UPDATE_TIME tells us when the stats were last updated.
Step 2) Check when was the table last analyzed

DCFRA1>select last_analyzed from dba_tables where table_name = 'PICKED_SHIPMENT_BATCHES';


Step 3) Use DBMS_STATS.RESTORE_TABLE_STATS procedure to restore the stats on the table

SQL> execute dbms_stats.restore_table_stats(<OWNER>,<TABLE_NAME>,<TIMESTAMP WITH TIMEZONE>);

Timestamp could be any timestamp in STATS_UPDATE_TIME column of dba_tab_stats_history table.

After restore, Check the last_analyzed date from dba_table and you should see the old date.
Following is a short FAQ on statistics.

Where does oracle store the statistics?

Oracle DBA_OPTSTAT_OPERATIONS table contains a log history of statistics collected.

SQL> select OPERATION, START_TIME, END_TIME from dba_optstat_operations;
OPERATION                      START_TIME                               END_TIME
------------------------------ ---------------------------------------- ----------------------------------------
gather_database_stats(auto)    30-OCT-10 AM +02:00      31-OCT-10 PM +01:00


How does Oracle maintain the Statistics History?

We can check the oldest statistics that can be restore using GET_STATS_HISTORY_AVAILABILITY procedure.

30-SEP-10 PM +01:00

Usually Oracle retains stats for 1 month (31 days).


You can change the retention using ALTER_STATS_HISTORY_RETENTION procedure.



Using export/import of statistics is a 6 steps process

Step 1) Create stats table in the database

EXEC DBMS_STATS.create_stat_table(‘SYSTEM’,’STATS_TABLE’);

Step 2) Export table/schema statistics


Step 3) Export the table STATS_TABLE using exp utility.

This completes the export part. You can later import the stats using following steps

Step 4) Import table STATS_TABLE using imp utility

Step 5) Import table/schema statistics


Step 6) Drop the stats table



Hope this helps !!


Database Statistics and CBO


One of the greatest problems with the Oracle Cost-based optimizer was the failure of the Oracle DBA to gather accurate schema statistics. In order for the CBO to make an intelligent decision about the best execution plan for your SQL, it must have information about the table and indexes that participate in the query. CBO depends on the information about tables and indexes present in data dictionary. This information includes size of the tables, its extent distribution, cardinality, and selectivity of column values etc. Based on these details CBO makes an attempt to come up with a highly optimized plan for executing the query.

Optimizer statistics include the following:

  • Table statistics
    • Number of rows
    • Number of blocks
    • Average row length
  • Column statistics
    • Number of distinct values (NDV) in column
    • Number of nulls in column
    • Data distribution (histogram)
  • Index statistics
    • Number of leaf blocks
    • Levels
    • Clustering factor
  • System statistics
    • I/O performance and utilization
    • CPU performance and utilization

Gathering statistics Automatically and Manually

Usually statistics gathering in 10g is automated using a scheduler job “GATHER_STATS_JOB”. This job is created by default when a database is created and run every day to collect statistics.

We can also gather statistics manually using DBMS_STATS. But why do we need to manually gather statistics? This is because, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. Example in case of bulk load of data into the table. Following are the procedures available in DBMS_STATS package to gather statistics

GATHER_INDEX_STATS – Index statistics
GATHER_TABLE_STATS – Table, column, and index statistics
GATHER_SCHEMA_STATS – Statistics for all objects in a schema
GATHER_DICTIONARY_STATS – Statistics for all dictionary objects
GATHER_DATABASE_STATS – Statistics for all objects in a database

Automatic sampling

While gathering the statistics the important part comes in deciding the sample size to be considered while gathering the stats. Sampling is specified using the ESTIMATE_PERCENT argument to the DBMS_STATS procedures. The better the quality of the statistics, the better the job that the CBO will do when determining your execution plans. Unfortunately, doing a complete analysis on a large  database could take days to finish. So to avoid this we tell DBMS_STATS to consider some percentage of rows on which it can base its analysis.

In earlier releases, the DBA had to guess what percentage of the database provided the best sample size and sometimes underanalyzed the schema. Starting with Oracle9i Database, the  estimate_percent argument is a great way to allow Oracle’s dbms_stats to automatically estimate the “best” percentage of a segment to sample when gathering statistics

estimate_percent => dbms_stats.auto_sample_size

AUTO_SAMPLE_SIZE lets Oracle determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes. For example, to collect table and column statistics for all tables in the OE schema with auto-sampling, you could use:


When the ESTIMATE_PERCENT parameter is manually specified, the DBMS_STATS gathering procedures may automatically increase the sampling percentage if the specified percentage did not produce a large enough sample. This ensures the stability of the estimated values by reducing fluctuations.

Determining Stale Statistics

The important questions comes here is how does Oracle knows that the statistics for the table or index is stale and should be gathered? In Oracle 9i you could check if the data in a table had changed significantly by turning on the table monitoring option (ALTER TABLE … MONITORING) and then checking the view DBA_TAB_MODIFICATIONS for those tables. In 10g, the MONITORING statement is gone. Instead, statistics are collected automatically if the initialization parameter STATISTIC_LEVEL is set to TYPICAL or ALL. This is done by pre defined job “GATHER_STATS_JOB” mentioned previously.

In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

So all the modifications (update, insert, delete) are stored in USER_TAB_MODIFICATIONS table and these are compared with number of rows in a table at the time of statistics collection. If this ratio is greater then 10% then that table is eligible for statistics collection.

Dynamic Sampling

What is dynamic sampling ?

Dynamic sampling is used at compile time when a CBO is making query plan. This involves gathering the information about tables and indexes and other data dictionaly information required in the query. When we gather statistics this information is available ready, but in cases where the statistics are not gathered, we can considered using dynamic samplying where the stats are gathered dynamically and based on this information query plan is generated.

How Dynamic Sampling Works ?

The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table’s blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.

When to Use Dynamic Sampling ?

For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:

  • A better plan can be found using dynamic sampling.
  • The sampling time is a small fraction of total execution time for the query.
  • The query will be executed many times.

How to Use Dynamic Sampling to Improve Performance ?

You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10. The default is 2.

  • A value of 0 means dynamic sampling will not be done.
  • Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.

For dynamic sampling levels refer Oracle documentation


Oracle documentation