In some cases we
may find that gathering fresh statistics has led to the optimizer executing
sub-optimal execution plans and we would like to restore the statistics as of a
date when we had good performance.
Here we can restore table statistics to a previous point in the past by using the DBMS_STATS.RESTORE_TABLE_STATS package.
We can check how
far back in time we can go to restore statistics.
SQL> select
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
13-MAR-14
10.51.37.266819000 PM +05:30
We can see what the
retention period is for the statistics and can also alter the same. In this
case we are changing the statistics retention from 10 days to 14 days. The
default is 31 days.
SQL> select
DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
10
SQL> execute
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (14);
PL/SQL procedure
successfully completed.
SQL> select
DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
14
Let us now look at
an example of restoring the statistics from a point in time from the past.
SQL> select count(*)
from myobjects;
COUNT(*)
----------
138353
SQL> select
TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where
table_name='MYOBJECTS' and owner='SYSTEM';
TABLE_NAME STATS_UPDATE_TIME
------------------------------
----------------------------------------
MYOBJECTS 18-MAR-14
10.00.19.496616 PM +05:30
MYOBJECTS 23-MAR-14
11.29.10.526131 AM +05:30
MYOBJECTS 23-MAR-14
11.35.47.249720 AM +05:30
MYOBJECTS 23-MAR-14
11.37.36.050421 AM +05:30
MYOBJECTS 23-MAR-14
11.38.05.015640 AM +05:30
The table MYOBJECTS
has been analyzed several times in the past few days. Let us see what happens
if we restore the statistics of the table gathered on the 18th of March.
SQL> execute
dbms_stats.restore_table_stats(‘SYSTEM’,'MYOBJECTS’,’18-MAR-14 10.00.19.496616
PM +05:30′);
PL/SQL procedure
successfully completed.
The table MYOBJECTS
actually has 138353 rows, but the statistics were gathered at a time when it
had much fewer rows as shown by the NUM_ROWS column of the DBA_TABLES view.
SQL> select
num_rows from dba_tables
2 where
table_name='MYOBJECTS' and owner='SYSTEM';
NUM_ROWS
----------
46141
We can now replace
these ‘older’ statistics with the most current statistics available in the
history.
SQL> execute
dbms_stats.restore_table_stats('SYSTEM','MYOBJECTS',’ 23-MAR-14 11.38.05.015640
AM +05:30’);
PL/SQL procedure
successfully completed.
Now the NUM_ROWS
column shows the row count which is the same as the actual number of rows in
the table.
SQL> select
num_rows from dba_tables
2 where
table_name='MYOBJECTS' and owner='SYSTEM';
NUM_ROWS
----------
138353
It should be noted
that we can use the same procedure to not only restore previous statistics of
just a table, but we can also do the same for the full schema or entire
database as well.
REMEMBER – we gather statistics to ensure that the optimizer chooses the optimal execution plans, but gathering statistics invalidates the parsed representation of the SQL statement and reparsing the statement post gather stats can make the optimizer choose an execution plan which is different (and less optimised) than the original plan.
