Author: Jens Paul
Database statistics play an important role in the life of every database administrator. Not only Oracle experts but also Oracle itself recommends saving old statistics before collecting new statistics. However most of us somehow forget to save the statistics for old table, schema or database before we start collecting the new statistics and hence run into performance problems.
It is really important to backup the old statistics before you collect new statistics because not saving old statistics can place you in trouble if new statistics make your optimizer behave differently. While saving old statistics can help you restore old statistics in order to have the optimizer behavior back in time. Note that if new statistics result in suboptimal execution plan then it becomes negligible to restore the old statistics in order to fix the issue!
Luckily Oracle 10g and onwards support automatic saving of statistics whenever the statistics are modified. However automatic saving of old statistics will not take place if you use ANALYZE in 10g for statistics collection.
Oracle DBA_OPTSTAT_OPERATIONS Table:
Oracle DBA_OPTSTAT_OPERATIONS table contains a log history of optimizer statistics operations performed on the database performed at various level such as table, schema or database. The DBA_OPTSTAT_OPERATIONS table contains the columns OPERATION, TARGET, START_TIME and END_TIME. You can restore older statistics anytime by making use of START_TIME and END_TIME values.
SELECT * FROM DBA_OPTSTAT_OPERATIONS WHERE END_TIME >= TRUNC(SYSDATE-1) AND END_TIME < TRUNC(SYSDATE);
GET_STATS_HISTORY_AVAILABILITY Procedure:
GET_STATS_HISTORY_AVAILABILITY is a DBMS_STATS procedure that returns oldest timestamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;
PURGE_STATS Procedure:
PURGE_STATS is also a DBMS_STATS procedure that enables you to manually purge old statistics beyond a time stamp. The old statistics are purged automatically at regular intervals based on statistics history retention setting. The default retention value is 31 days.
DECLARE TMS TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT SYSTIMESTAMP INTO TMS FROM DUAL;
DBMS_STATS.PURGE_STATS (TMS);
END;
/
ALTER_STATS_HISTORY_RETENTION Procedure:
You can modify the default for the history retention by using DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure.
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0);
Where NULL = change to default value , 0 = never save old stats, autopurge statistics history , 1 = statistics history never purged by autopurge
Remember the STATISTICS_LEVEL parameter should be set to ALL or TYPICAL in order to avail Automatic purging feature in Oracle 10g.
CREATE_STAT_TABLE Procedure:
You can use the DBMS_STATS.CREATE_STAT_TABLE procedure to create a table that is capable of holding statistics. DBMS_STATS.CREATE_STAT_TABLE takes ownname , stattab and tblspace as the parameters where ‘ownname' is the name of the schema, ‘stattab' is the name of the table to create and tblspace is the tablespace in which to create the statistics tables. If you do not specify a tablespace then it will be created in the user's default tablespace.
EXEC DBMS_STATS.CREATE_STAT_TABLE (USER, 'MY_STAT_TABLE', 'MY_TBS');
DESC MY_STAT_TABLE
Exporting and Importing Statistics:
You can also save and restore statistics by exporting and importing them. However you will need a table that will hold statistics before you start exporting them. You can use the DBMS_STATS.CREATE_STAT_TABLE procedure to create the table and DBMS_STATS.EXPORT_*_STATS and DBMS_STATS.IMPORT_*_STATS procedures for the purpose of exporting and importing statistics.
RESTORE_TABLE_STATS Procedure:
Oracle RESTORE_TABLE_STATS procedure restores statistics of a table as of a specified timestamp. It also restores the statistics of associated indexes and columns. However it does not restore the user defined statistics. It will lock the statistics if the table statistics are locked. Below command will replace the old statistics with new statistics.
EXEC DBMS_STATS.RESTORE_TABLE_STATS({OWNER} ,{TABLE NAME}, {TIMESTAMP});
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
Improved Statistics Gathering in Oracle 11g
DBA Tips: Handling Recordsets in Oracle Procedures!!
The Secrets of Protecting Data in your Database Tables!!
Quick Oracle Database Recovery with Minimal Downtime!!
|
