1Z0-062 · Question #283
Examine the following command: SQL> DBMS_STATS. SET_TABLE_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false'); What is the effect of executing this command?
The correct answer is D. Statistics subsequently gathered on the CUSTOMERS table are stored as pending statistics.. Executing DBMS_STATS.SET_TABLE_PREFS with PUBLISH set to false configures the database to store any newly gathered statistics as pending statistics, rather than immediately overwriting existing ones.
Question
Examine the following command:
SQL> DBMS_STATS. SET_TABLE_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false'); What is the effect of executing this command?
Options
- AExisting statistics for the CUSTOMERS table become unusable for the query optimizer.
- BAutomatic statistics collection is stopped for the CUSTOMERS table.
- CStatistics for the CUSTOMERS table are locked and cannot be overwritten.
- DStatistics subsequently gathered on the CUSTOMERS table are stored as pending statistics.
How the community answered
(32 responses)- A3% (1)
- B3% (1)
- C9% (3)
- D84% (27)
Why each option
Executing `DBMS_STATS.SET_TABLE_PREFS` with `PUBLISH` set to `false` configures the database to store any newly gathered statistics as pending statistics, rather than immediately overwriting existing ones.
Setting `PUBLISH` to `false` only affects future statistics gathering operations; existing, currently published statistics remain usable by the query optimizer until new statistics are explicitly published.
The `PUBLISH` preference controls how collected statistics are handled (published versus pending), not whether automatic statistics collection occurs; other preferences or global settings control collection frequency.
Locking statistics is achieved using `DBMS_STATS.LOCK_TABLE_STATS`; setting the `PUBLISH` preference to `false` does not lock statistics but rather controls their publication behavior.
The `DBMS_STATS.SET_TABLE_PREFS` procedure with the `PUBLISH` preference set to `false` configures the specified table so that any subsequent statistics gathering operations will store the new statistics in a pending area. These pending statistics will not be used by the optimizer until they are explicitly published, allowing for testing before deployment.
Concept tested: Oracle DBMS_STATS PUBLISH preference for pending statistics
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-EBB48641-698C-4C6C-964D-032F42F80826
Topics
Community Discussion
No community discussion yet for this question.