You are administering a database that supports an OLTP application. To set statistics
preferences, you issued the following command:
SQL > DBMS_STATS.SET_GLOBAL_PREFS (‘ESTIMATE_PERCENT’, ‘9’);
What will be the effect of executing this procedure?
A.
It will influence the gathering of statistics for a table based on the value specified for
ESTIMATE_PERCENT provided on table preferences for the same table exist.
B.
It will influence dynamic sampling for a query to estimate the statistics based on
ESTIMATE_PERCENT.
C.
The automatic statistics gathering job running in the maintenance window will use global
preferences even if table preferences for the same table exist.
D.
New objects created will use global preference even if table preferences are specified.
Explanation:
Note:
* With the DBMS_STATS package you can view and modify optimizer statistics gathered for
database objects.
* The SET_GLOBAL_PREFS procedure e is used to set the global statistics preferences.
* ESTIMATE_PERCENT – The value determines the percentage of rows to estimate. The valid
range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle
determine the appropriate sample size for good statistics. This is the default.
Not C:
The DBMS_STATS.SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run as new objects will pick up the GLOBAL_PREF values for all parameters.
https://blogs.oracle.com/optimizer/entry/understanding_dbms_statsset__prefs_procedures
Not A for the same reason above
Not B
So D is correct
None of them are correct
I think A . Not D because new objects will use the new global setting unless specified at table or statement level
https://community.oracle.com/thread/3549339?start=0&tstart=0
A.
The SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by
the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an
existing table preference. All parameters default to the global setting unless there is a table preference set, or the parameter is explicitly set in the GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREFS values for all parameters.
taken from http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.pdf on page 16 (Useful to read for every Set_*_PREFS? but not only for SET_GLOBAL_PREFS).