40.You have statistics collected for some selected tables. Your requirement is that the statistics for the
tables and all dependent indexes must not be overwritten by further statistics collection until a certain
point of time.
How would you achieve this?
A.Lock statistics for the tables.
B.Change STALE_PERCENT to zero for the tables.
C.Set the TIMED_STATISTICS parameter to TRUE.
D.Set the STATISTICS_LEVEL parameter to BASIC.
E.Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
答案:A
解析:
参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68491
题目说的是保证statistics不被覆盖,那么可以使用Lock statistics
B:TALE_PERCENT: Determines the percentage of rows in a table that have to change
before the statistics on that table are deemed stale and should be regathered.
The valid domain for stale_percent is non-negative numbers.The default value is 10%.
Note that if you set stale_percent to zero the AUTO STATS gathering job will gather
statistics for this table every time a row in the table is modified.
大概意思就是,这个参数是确定当表中行改变达到多大的比例的时候应该收集,该值不能为负数,默认为10%
需要注意的是,如果设置为0,那么只要有1行发生改变就会进行收集
所以这个东西与题目中说的不是一回事
C:TIMED_STATISTICS:这个主要是指定与时间相关的统计是否收集,如果为true,那么就收集并且存储在trace 文件或者
在V$SESSTATS和V$SYSSTATS动态视图中,如果为false,那么就不收集,这么做可以避免频繁从操作系统请求时间
D:OPTIMIZER_USE_PENDING:这里应该是OPTIMIZER_USE_PENDING_STATISTICS,指的是编译sql语句的时候,优化器是否使用最新的还没有验证的统计信息,默认情况下使用的是
存储在数据字典中的已经发布的统计信息
By default, the optimizer uses the published statistics stored in the data dictionary views.
If you want the optimizer to use the newly collected pending statistics, then set the initialization
parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE (the default value is FALSE),
and run a workload against the table or schema:
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
The optimizer will use the pending statistics instead of the published statistics
when compiling SQL statements. If the pending statistics are valid,
then you can make them public by executing the following statement:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS(null, null);
You can also publish the pending statistics for a specific database object.
For example, by using the following statement:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');
If you do not want to publish the pending statistics, delete them by executing the following statement:
EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');
You can export pending statistics using DBMS_STATS.EXPORT_PENDING_STATS function.
Exporting pending statistics to a test system enables you to run a full workload against the new statistics.
参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
tables and all dependent indexes must not be overwritten by further statistics collection until a certain
point of time.
How would you achieve this?
A.Lock statistics for the tables.
B.Change STALE_PERCENT to zero for the tables.
C.Set the TIMED_STATISTICS parameter to TRUE.
D.Set the STATISTICS_LEVEL parameter to BASIC.
E.Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
答案:A
解析:
参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68491
题目说的是保证statistics不被覆盖,那么可以使用Lock statistics
B:TALE_PERCENT: Determines the percentage of rows in a table that have to change
before the statistics on that table are deemed stale and should be regathered.
The valid domain for stale_percent is non-negative numbers.The default value is 10%.
Note that if you set stale_percent to zero the AUTO STATS gathering job will gather
statistics for this table every time a row in the table is modified.
大概意思就是,这个参数是确定当表中行改变达到多大的比例的时候应该收集,该值不能为负数,默认为10%
需要注意的是,如果设置为0,那么只要有1行发生改变就会进行收集
所以这个东西与题目中说的不是一回事
C:TIMED_STATISTICS:这个主要是指定与时间相关的统计是否收集,如果为true,那么就收集并且存储在trace 文件或者
在V$SESSTATS和V$SYSSTATS动态视图中,如果为false,那么就不收集,这么做可以避免频繁从操作系统请求时间
D:OPTIMIZER_USE_PENDING:这里应该是OPTIMIZER_USE_PENDING_STATISTICS,指的是编译sql语句的时候,优化器是否使用最新的还没有验证的统计信息,默认情况下使用的是
存储在数据字典中的已经发布的统计信息
By default, the optimizer uses the published statistics stored in the data dictionary views.
If you want the optimizer to use the newly collected pending statistics, then set the initialization
parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE (the default value is FALSE),
and run a workload against the table or schema:
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
The optimizer will use the pending statistics instead of the published statistics
when compiling SQL statements. If the pending statistics are valid,
then you can make them public by executing the following statement:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS(null, null);
You can also publish the pending statistics for a specific database object.
For example, by using the following statement:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');
If you do not want to publish the pending statistics, delete them by executing the following statement:
EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');
You can export pending statistics using DBMS_STATS.EXPORT_PENDING_STATS function.
Exporting pending statistics to a test system enables you to run a full workload against the new statistics.
参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003