Which action would help to use the previous set of statistics on the objects?

You have recently collected statistics on certain objects of a schema in your database. But
you observe suboptimal execution plans for the queries on these objects after two days of
statistics collection. The optimizer statistics retention period is set to its default value. Which
action would help to use the previous set of statistics on the objects?

You have recently collected statistics on certain objects of a schema in your database. But
you observe suboptimal execution plans for the queries on these objects after two days of
statistics collection. The optimizer statistics retention period is set to its default value. Which
action would help to use the previous set of statistics on the objects?

A.
Reduce the optimizer statistics retention period by 2 days.

B.
Set the OPTIMIZER_PENDING_STATISTICS parameter to TRUE.

C.
Restore statistics from statistics history.

D.
Reduce the Automatic Workload Repository (AWR) retention period by 2 days.

Show Hint

Leave a Reply 1

Your email address will not be published. Required fields are marked *

jrporto

jrporto

C

http://docs.oracle.com/cd/E25178_01/server.1111/e16638/stats.htm

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoration. You can restore statistics using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
…
The database purges old statistics automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. You can configure retention using the ALTER_STATS_HISTORY_RETENTION procedure of DBMS_STATS. The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days.

Automatic purging is enabled when STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, then you must purge the old versions of statistics manually using the PURGE_STATS procedure.

Reply