Restoring Optimizer Statistics

 

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.

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.

 

In Oracle 10g and above, we can also restore table statistics to a previous point in the past by using theDBMS_STATS.RESTORE_TABLE_STATSpackage.

Note: we can also export and import the statistics using the DBMS_STATS.CREATE_STATS_TABLE to create a statistics table which we can use to export and import statistics using the DBMS_STATS.EXPORT_*_STATS and DBMS_STATS.IMPORT_*_STATS procedures.

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

---------------------------------------------------------------------------

14-SEP-11 11.36.33.023724000 AM +08:00

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 31 days to 14 days. The default is 31 days.

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

 

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-SEP-11 10.00.19.496616 PM +08:00

MYOBJECTS                      23-SEP-11 11.29.10.526131 AM +08:00

MYOBJECTS                      23-SEP-11 11.35.47.249720 AM +08:00

MYOBJECTS                      23-SEP-11 11.37.36.050421 AM +08:00

MYOBJECTS                      23-SEP-11 11.38.05.015640 AM +08:00

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’,’8-SEP-11 10.00.19.496616 PM +08:00′);

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-SEP-11 11.38.05.015640 AM +08:00’);

 

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.