You are logged in as the HR user and you execute the following procedure: SQL > exec DBMS_STATS.SET_TABLE_PREFS (HR', EMPLOYEES', PUBLISH', FALSE'); SQL> exec DBMS_STATS.GATHER_TABLE_STATS (HR'…

The correct answer is D. They are marked as pending and stored in the pending statistics table. In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them. The…

Gathering Optimizer Statistics

Question

You are logged in as the HR user and you execute the following procedure:

SQL > exec DBMS_STATS.SET_TABLE_PREFS (HR', EMPLOYEES', PUBLISH', FALSE'); SQL> exec DBMS_STATS.GATHER_TABLE_STATS (HR', EMPLOYEES'); Which statement is true about the newly gathered statistics?

Options

AThey are temporary and purged when the session exits.
BThey are used by the optimizer for all sessions.
CThey are locked and cannot be overwritten.
DThey are marked as pending and stored in the pending statistics table.

How the community answered

(26 responses)

A
8% (2)
B
4% (1)
C
4% (1)
D
85% (22)

Explanation

In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them. The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state. Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics

Topics

#pending statistics#PUBLISH preference#DBMS_STATS#table preferences

Community Discussion

No community discussion yet for this question.

Full 1Z0-117 Practice