nerdexam
Oracle

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.

Performing Database Maintenance

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)
  • A
    77% (53)
  • B
    3% (2)
  • C
    7% (5)
  • D
    1% (1)
  • E
    12% (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.

ALock statistics for the tables.Correct

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.

BChange STALE_PERCENT to zero for the tables.

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.

CSet the TIMED_STATISTICS parameter to TRUE.

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.

DSet the STATISTICS_LEVEL parameter to BASIC.

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.

ESet the OPTIMIZER_USE_PENDING parameter statistics to TRUE.

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

#statistics locking#optimizer statistics#DBMS_STATS#stale statistics

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice