Examine this command:
SQL > exec DBMS_STATS.SET_TABLE_PREFS (‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’);
Which three statements are true about the effect of this command?
A.
Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
B.
Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
C.
Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse
time.
D.
Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as
pending statistics.
E.
Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as
pending statistics.
Explanation:
* SET_TABLE_PREFS Procedure
This procedure is used to set the statistics preferences of the specified table in the specified
schema.
* Example:
Using Pending Statistics
Assume many modifications have been made to the employees table since the last time statistics
were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics
should be gathered once again; however, the user is concerned that new statistics will cause the
optimizer to choose bad plans when the current ones are acceptable. The user can do the
following:
EXEC DBMS_STATS.SET_TABLE_PREFS(‘hr’, ’employees’, ‘PUBLISH’, ‘false’);
By setting the employees tables publish preference to FALSE, any statistics gather from now on
will not be automatically published. The newly gathered statistics will be marked as pending.
Not agree!
By setting tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending. Statistic collection is STILL done when both schema stats and database stats are gathered, just pending, no publishing.
So CDE
Any opinion?
I agree CDE is correct answer
Yes, correct answer is C, D and E.
what about a:
we are setting the PUBLISH preference for the CUSTOMERS table to FALSE. As a result, newly collected statistics will be staged, and will not be used until they are published
so for me ACD
Notice option A says stats collection not done…Infact nothing todo with wether stats are done or not..
ACD
A C and D are more logical answers.
CDE
CDE
(Either AB should be correct of DE should be correct, DE are more logical)
I would agree CDE.
Not A, why statistic would not be gathered with gather schema stats. They would not be only published.
C,D,E is correct
From the 12c documentation:
For example, execute the following procedure so that statistics are marked as pending:
BEGIN
DBMS_STATS.SET_TABLE_PREFS(‘sh’, ‘customers’, ‘publish’, ‘false’);
END;
/
Subsequently, when you gather statistics on the customers table, the database does not automatically publish statistics when the gather job completes. Instead, the database stores the newly gathered statistics in the USER_TAB_PENDING_STATS table.
it is true for previous version of Oracle as well.
CDE.
CDE
CDE
C,D,E are correct
PUBLISH – This value determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.
CDE are correct.
Reason : A and D are contradict each other .
A.
Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
D.
Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as
pending statistics.
CDE
PUBLISH – Determines whether or not newly gathered statistics will be published once the gather job has completed. once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately.
CDE
C,D,E correct