关闭特定对象统计信息自动收集

    最近几天都在学习OCP相关考试题目，以后陆续把自己做错或者感觉特别经典的题目记录下来。
    其实之前没有想到数据库还可以关闭特定表的信息自动收集，直到看了以下这道题才知道。
    然后去看了下资料如何关闭：
    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.
简单说为了让特定表的统计信息不被覆盖，可以把这些表的信息收集关闭掉：
 BEGIN
   DBMS_STATS.lock_table_stats(USER,'STAT');
   END;
其中stat为表名，相应的重新开启
 BEGIN
   DBMS_STATS.unlock_table_stats(USER,'STAT');
   END;

进一步，也可以对一个模式进行开关，工具包：

Statistics for a table or schema can be locked. After statistics are locked, you can make no modifications to the statistics until the statistics have been unlocked. Locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking (LOCK_SCHEMA_STATS and LOCK_TABLE_STATS) and two procedures for unlocking statistics (UNLOCK_SCHEMA_STATS and UNLOCK_TABLE_STATS).

另外，手动收集信息：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                TABNAME          => 'STAT',
                                ESTIMATE_PERCENT => 100,
                                METHOD_OPT       => 'for all columns size skewonly',
                                CASCADE          => TRUE);
END;

查询统计结果:
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
  FROM USER_TAB_STATISTICS
 WHERE TABLE_NAME = 'STAT';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14888950/viewspace-1430701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14888950/viewspace-1430701/