old statistics – Persistent Storage Solutions

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.

Using RESTORE_TABLE_STATS

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
------------------------------ ----------------------------------------
PICKED_SHIPMENT_BATCHES        30-OCT-10 07.50.59.539450 PM +02:00
PICKED_SHIPMENT_BATCHES        31-OCT-10 12.02.58.979300 AM +02:00
PICKED_SHIPMENT_BATCHES        31-OCT-10 12.17.19.315201 AM +02:00

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';
LAST_ANALYZED
----------------
2010-10-31:20:12

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 06.00.04.042555 AM +02:00      31-OCT-10 11.00.02.114381 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.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY  from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
30-SEP-10 11.22.25.817428000 PM +01:00

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

DCFRA1>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                        31

You can change the retention using ALTER_STATS_HISTORY_RETENTION procedure.

Using EXPORT/IMPORT

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

DBMS_STATS.export_schema_stats(‘DEMO_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);

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

DBMS_STATS.import_schema_stats(‘DEMO_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);

Step 6) Drop the stats table

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

Hope this helps !!

Tag: old statistics

Restoring the statistics – Oracle Database 10g