Database Reference
In-Depth Information
These two features are available only with workload system statistics. For this reason, I consider workload
statistics to be superior and usually recommend them over the noworkload option. The additional effort you put into
gathering workload statistics usually pays off in the long term.
Restoring System Statistics
Whenever system statistics are changed through the
dbms_stats
package, instead of simply overwriting current statistics
with the new statistics, the current statistics are saved in another data dictionary table (
wri$_optstat_aux_history
)
that keeps a history of all changes occurring within a retention period. The purpose is to be able to restore old statistics
in case new statistics lead to inefficient execution plans.
For purpose of restoring old statistics, the
dbms_stats
package provides the
restore_system_stats
procedure.
This procedure accepts a single parameter: a timestamp specifying the target time. Statistics are restored to those that
were in use at that specific time. For example, the following PL/SQL block restores the system statistics that were in
use one day ago.
BEGIN
dbms_stats.delete_system_stats();
dbms_stats.restore_system_stats(as_of_timestamp => systimestamp - INTERVAL '1' DAY);
END;
■
to make sure you restore exactly the same system statistics that were in use at a specific time, you have
to delete the current system statistics before the restore. Otherwise, the statistics being restored are in fact
merged
with
whatever statistics are current.
Caution
System statistics (as well as object statistics, because they're maintained by the same underlying functionality)
are kept in the history for an interval specified by a retention period. The default value is 31 days. You can display the
current value by calling the
get_stats_history_retention
function in the
dbms_stats
package, as shown here:
SELECT dbms_stats.get_stats_history_retention() AS retention FROM dual
To change the retention period, the
dbms_stats
package provides the
alter_stats_history_retention
procedure. Here's an example where the call sets the retention period to 14 days:
dbms_stats.alter_stats_history_retention(retention => 14)
Note that with the
alter_stats_history_retention
procedure, the following values have a special meaning:
•
NULL
sets the retention period to the default value.
0 disables the history.
•
•
-1 disables the purging of the history.
When the
statistics_level
initialization parameter is set to
typical
(the default value) or
all
, statistics older
than the retention period are automatically purged. Whenever manual purging is necessary, the
dbms_stats
package
provides the
purge_stats
procedure. The following call purges all statistics placed in the history more than 14 days ago:
dbms_stats.purge_stats(before_timestamp => systimestamp - INTERVAL '14' DAY)
