1Z0-052 · Question #154
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…
The correct answer is A. Restore statistics from statistics history. Oracle retains a history of optimizer statistics for a default period of 31 days, allowing restoration of previous statistics when newer ones produce suboptimal plans.
Question
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?
Options
- ARestore statistics from statistics history.
- BReduce the optimizer statistics retention period by 2 days.
- CSet the OPTIMIZER_PENDING_STATISTICS parameter to TRUE.
- DReduce the Automatic Workload Repository (AWR) retention period by 2 days.
How the community answered
(33 responses)- A48% (16)
- B18% (6)
- C27% (9)
- D6% (2)
Why each option
Oracle retains a history of optimizer statistics for a default period of 31 days, allowing restoration of previous statistics when newer ones produce suboptimal plans.
Oracle's DBMS_STATS package automatically saves statistics history before overwriting them, with a default retention period of 31 days. Since only 2 days have elapsed since collection, the prior statistics are still retained and can be restored using DBMS_STATS.RESTORE_TABLE_STATS or DBMS_STATS.RESTORE_SCHEMA_STATS, reverting the optimizer to the previously working execution plans.
Reducing the optimizer statistics retention period would purge older history sooner, making it harder to restore previous statistics rather than easier.
OPTIMIZER_PENDING_STATISTICS is not a valid Oracle initialization parameter; the correct parameter is OPTIMIZER_USE_PENDING_STATISTICS, and it controls pending statistics, not historical ones.
The AWR retention period governs performance snapshot history for diagnostics and is unrelated to optimizer statistics history managed by DBMS_STATS.
Concept tested: Restoring optimizer statistics from history using DBMS_STATS
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html
Topics
Community Discussion
No community discussion yet for this question.