1Z0-052 · Question #166
You have statistics collected for some selected tables. Your requirement is that the statistics for the tables and all dependent indexes must not be overwritten by further statistics collection…
The correct answer is A. Lock statistics for the tables. Oracle's DBMS_STATS.LOCK_TABLE_STATS procedure prevents any further statistics gathering from overwriting current statistics for a table and all its dependent indexes.
Question
You have statistics collected for some selected tables. Your requirement is that the statistics for the tables and all dependent indexes must not be overwritten by further statistics collection until a certain point of time. How would you achieve this?
Options
- ALock statistics for the tables.
- BChange STALE_PERCENT to zero for the tables.
- CSet the TIMED_STATISTICS parameter to TRUE.
- DSet the STATISTICS_LEVEL parameter to BASIC.
- ESet the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
How the community answered
(69 responses)- A77% (53)
- B3% (2)
- C7% (5)
- D1% (1)
- E12% (8)
Why each option
Oracle's DBMS_STATS.LOCK_TABLE_STATS procedure prevents any further statistics gathering from overwriting current statistics for a table and all its dependent indexes.
Executing DBMS_STATS.LOCK_TABLE_STATS places a lock on the table's statistics, preventing both manual DBMS_STATS gather calls and the automatic statistics collection job (auto optimizer stats collection) from modifying the statistics for the table and all associated indexes until the lock is explicitly removed with DBMS_STATS.UNLOCK_TABLE_STATS.
STALE_PERCENT is a preference controlling the percentage of rows that must change before statistics are considered stale - setting it to zero would cause statistics to become stale immediately after any change, which is the opposite of protection.
TIMED_STATISTICS enables the collection of elapsed time metrics for internal operations used in dynamic performance views, and has no relationship to protecting optimizer statistics.
Setting STATISTICS_LEVEL to BASIC disables many advisories and automatic statistics but does not lock specific table statistics against future overwrite by explicit gather calls.
OPTIMIZER_USE_PENDING_STATISTICS directs the optimizer to use unpublished pending statistics for testing purposes, and does not protect published statistics from being overwritten.
Concept tested: Locking Oracle optimizer statistics with DBMS_STATS
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-3B2DFCC4-0A3C-4A5D-9A2D-F30E0F27B4C9
Topics
Community Discussion
No community discussion yet for this question.