In general, restore statistics instead of exporting them in the following situations:

• You want to recover older versions of the statistics. For example, you want to restore the optimizer behavior to an earlier date.

• You want the database to manage the retention and purging of statistics histories.

Export statistics rather than restoring them in the following situations:

• You want to experiment with multiple sets of statistics and change the values back and forth.

• You want to move the statistics from one database to another database. For example, moving statistics from a production system to a test system.

• You want to preserve a known set of statistics for a longer period than the desired retention date for restoring statistics.

Limitations include the following:

• DBMS_STATS.RESTORE_*_STATS procedures cannot restore user-defined statistics.

• Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.

• When you drop a table, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures is lost. Without this data, these features do not function properly. To remove all rows from a table, and to restore these statistics with DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table.

The procedures listed in the following table accept a timestamp as an argument and restore statistics as of the specified time (as_of_timestamp).

Dictionary views display the time of statistics modifications. You can use the following views to determine the time stamp to be use for the restore operation:

• The DBA_OPTSTAT_OPERATIONS view contain history of statistics operations performed at schema and database level using DBMS_STATS.

• The DBA_TAB_STATS_HISTORY views contains a history of table statistics modifications.

Assumptions

This tutorial assumes the following:

• After the most recent statistics collection for the oe.orders table, the optimizer began choosing suboptimal plans for queries of this table.

• You want to restore the statistics from before the most recent statistics collection to see if the plans improve.

To restore optimizer statistics:

1. Start SQL*Plus and connect to the database with administrator privileges.

2. Query the statistics history for oe.orders.

   For example, run the following query:

   COL TABLE_NAME FORMAT a10
   SELECT TABLE_NAME,
          TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME
   FROM   DBA_TAB_STATS_HISTORY 
   WHERE  TABLE_NAME='ORDERS'
   AND    OWNER='OE'
   ORDER BY STATS_UPDATE_TIME DESC;
   

   Sample output is as follows:

   TABLE_NAME STATS_MOD_TIME
   ---------- -------------------
   ORDERS     2012-08-20:11:36:38
   ORDERS     2012-08-10:11:06:20
   

3. Restore the optimizer statistics to the previous modification time.

   For example, restore the oe.orders table statistics to August 10, 2012:

   BEGIN
     DBMS_STATS.RESTORE_TABLE_STATS( 'OE','ORDERS', 
                  TO_TIMESTAMP('2012-08-10:11:06:20','YYYY-MM-DD:HH24:MI:SS') );
   END;
   /
   

   You can specify any date between 8/10 and 8/20 because DBMS_STATS restores statistics as of the specified time.

Historical information is useful when you want to determine how long the database retains optimizer statistics, and how far back these statistics can be restored. You can use the following procedure to obtain information about the optimizer statistics history:

• GET_STATS_HISTORY_RETENTION

  This function can retrieve the current statistics history retention value.

• GET_STATS_HISTORY_AVAILABILITY

  This function retrieves the oldest time stamp when statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.

To obtain optimizer statistics history information:

1. Start SQL*Plus and connect to the database with the necessary privileges.

2. Execute the following PL/SQL program:

   DECLARE
     v_stats_retn  NUMBER;
     v_stats_date  DATE;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('The retention setting is ' || v_stats_retn || '.');
     v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
     DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' || v_stats_date || '.');
   END;
   /

Prerequisites

To run this procedure, you must have either the SYSDBA privilege, or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

Assumptions

This tutorial assumes the following:

• The current retention period for optimizer statistics is 31 days.

• You run queries annually as part of an annual report. To keep the statistics history for more than 365 days so that you have access to last year's plan (in case a suboptimal plan occurs now), you set the retention period to 366 days.

• You want to create a PL/SQL procedure set_opt_stats_retention that you can use to change the optimizer statistics retention period.

To change the optimizer statistics retention period:

1. Start SQL*Plus and connect to the database with the necessary privileges.

2. Create a procedure that changes the retention period.

   For example, create the following procedure:

   CREATE OR REPLACE PROCEDURE set_opt_stats_retention
     ( p_stats_retn   IN NUMBER )
   IS
     v_stats_retn NUMBER;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('Old retention setting is ' ||v_stats_retn|| '.');
     DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(p_stats_retn);
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('New retention setting is ' ||v_stats_retn|| '.');
   END;
   /
   

3. Change the retention period to 366 days.

   For example, execute the procedure that you created in the previous step (sample output included):

   SQL> EXECUTE set_opt_stats_retention(366)
   
   The old retention setting is 31.
   The new retention setting is 366.
    
   PL/SQL procedure successfully completed.

The database purges all history older than the older of (current time - the ALTER_STATS_HISTORY_RETENTION setting) and (time of the most recent statistics gathering - 1).

You can purge old statistics manually using the PURGE_STATS procedure. If you do not specify an argument, then this procedure uses the automatic purging policy. If you specify the before_timestamp parameter, then the database purges statistics saved before the specified timestamp.

Prerequisites

To run this procedure, you must have either the SYSDBA privilege, or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

Assumptions

This tutorial assumes that you want to purge statistics more than one week old.

To purge optimizer statistics:

1. In SQL*Plus, log in to the database with the necessary privileges.

2. Execute the DBMS_STATS.PURGE_STATS procedure.

   For example, execute the procedure as follows:

   EXEC DBMS_STATS.PURGE_STATS( SYSDATE-7 );