052试题 42 - pending satistics

题目

42. Examine the command:
SQL> DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');
Which statement describes the effect of the above command?
A.Automatic statistics collection is stopped for the CUSTOMERS table.
B.Statistics for the CUSTOMERS table are locked and cannot be overwritten.
C.Existing statistics for the CUSTOMERS table become unusable for the query optimizer.
D.Subsequently, statistics gathered on the CUSTOMERS table are stored as pending statistics.
 

参考答案 ： D

 

官方文档 ：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68493

Examples

• Using Pending Statistics

• Gathering Daytime System Statistics

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.

EXEC DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees');

To test the newly gathered statistics, set optimizer_pending_statistics to TRUE in a session and run sample queries.

ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

If the pending statistics generate sound execution plans, they can be published:

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('hr', 'employees');
EXEC DBMS_STATS.SET_TABLE_PREF('hr', 'employees', 'PUBLISH', 'true');