關閉特定物件統計資訊自動收集

    最近幾天都在學習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';