ORACLE10g自動收集CBO統計資訊

從Oracle Database 10g開始，Oracle在建庫後就預設建立了一個名為GATHER_STATS_JOB的定時任務，用於自動收集CBO的統計資訊。

這個自動任務預設情況下在工作日晚上10：00-6：00和週末全天開啟。
呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計資訊。

該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序，再開始進行統計資訊。

說明：當做完統計資訊後，如果對物件的行數修改達到10%，DBMS_STATS就認為是統計資訊過舊。

可以透過以下查詢這個JOB的執行情況：
SQL> select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其實同在10點執行的Job還有一個AUTO_SPACE_ADVISOR_JOB：

SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME        LAST_START_DATE
------------------------------                   ----------------------------------------
AUTO_SPACE_ADVISOR_JOB    04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB      04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG            05-DEC-07 03.00.00.169059 AM PRC

然而這個自動化功能已經影響了很多系統的正常執行，晚上10點對於大部分生產系統也並非空閒時段。
而自動分析可能導致極為嚴重的閂鎖競爭，進而可能導致資料庫Hang或者Crash。

所以建議最好關閉這個自動統計資訊收集功能：
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

自動化永遠而嚴重的隱患相伴隨！

對於易變物件的變化，可以人工收集統計資訊(DBMS_STATS)外 主要兩種處理方式：

一種就是刪除統計資訊。使它的統計資訊為空，對於任何統計資訊缺失的表,oracle會用動態取樣特性自動產生統計資訊。如果使用久的統計資訊 就可能產生錯誤的執行計劃。
需要設定optimizer_dynamic_sampling為2（ORACLE10G預設值）或以上都可以啟動此特性。

optimizer_dynamic_sampling ,提供在SQL分析的時候，自動根據不同的Level（0-10）以不同的準確度分析SQL中未被analyze過的表，意在為CBO提供更多的統計信 息。在Oracle9iR2中引入，預設為Level 1，10g預設為2 。

Level 0: Do not dynamically sample the table(s)

Level 1：Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects

Level 2:Sample all unanalyzed tables referenced in the query using default sampling amounts(small sample)

Level 3 -- Level 10 ........更詳細的sample而已。

例如：dbms_stats.delete_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)

另一種就是設定為表進行鎖定,這樣就可以不更新統計資訊（注意LOCK表以後，表就不能修改了）。

dbms_stats.lock_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)；