oracle 11g自動收集統計資訊介紹

   對於oracle而言，準確的統計資訊對於CBO來說是非常重要的，因為這直接關係到CBO能否對目標SQL生成合適的、正確的執行計劃。所以我們應該使自己維護的資料庫中的統計資訊儘量準確。

在oracle 10g之前並沒有自動收集統計資訊的機制，對統計資訊的收集一般都是由DBA手工編寫SHELL指令碼來實現。這樣就會出現根據表或者其他物件的變更，需要頻繁的變更SHELL指令碼來適應資料庫物件的變更。另外，可能很多DBA沒有意識到統計資訊對最佳化器的重要性，導致很多的SQL無法得到正確的執行計劃。

基於統計資訊對CBO的重要性，所以oracle從10g版本開始引入了自動收集統計資訊的功能。自動統計資訊收集作業能夠每天收集統計資訊。預設情況下，該收集作業會自動判斷如下方面的內容。

1、需要對哪些物件收集統計資訊。

2、需要對哪些統計資訊已經過期的物件重複收集統計資訊。

3、以估算模式收集統計資訊的取樣比例（預設是以估算模式進行收集）

4、需要對哪些列收集直方圖統計資訊以及相應的Bucket 的數量。

5、是否啟用並行收集以及相應的並行度。

當然使用oracle自動收集統計資訊，必須要確保oracle的引數statistics_level的值為：TYPICAL或者 ALL 。

現在我們詳細介紹一下oracle11g中的自動統計資訊收集作業的詳情：

oracle 11g 引入了一個名為gather_stats_prog的自動執行任務專門用於收集統計資訊。

SELECT t.CLIENT_NAME, t.TASK_NAME, t.OPERATION_NAME, t.STATUS

  FROM dba_autotask_task t;

gather_stats_prog實際上也是呼叫了dbms_stats包裡面的儲存過程dbms_stats.gather_database_stats_job_proc：

SELECT t.PROGRAM_NAME

  FROM dba_scheduler_programs t

 where t.PROGRAM_NAME = 'GATHER_STATS_PROG';

oracle 11g的自動統計資訊收集作業可配置的維護視窗從10g 的2個增加到了7個，具體的維護視窗可以根據下面SQL語句檢視：

SELECT t.WINDOW_NAME,t.AUTOTASK_STATUS FROM dba_autotask_window_clients t  ;

可以根據下面的SQL看到每個收集統計資訊的維護視窗的執行開始時間和每次執行的持續時間，透過該檢視中的RESOURCE_PLAN欄位可以看出oracle在收集統計資訊的過程中新增了資源的限制：

SELECT t.WINDOW_NAME, t.REPEAT_INTERVAL, t.duration, t.enabled,t.RESOURCE_PLAN

  FROM dba_scheduler_windows t

 where enabled = 'TRUE';

自動執行任務GATHER_STATS_PROG每次執行的時候都會先 生成名為ORA$AT_OS_OPT_XXX 的作業，然後再執行這個作業，從檢視dba_scheduler_job_run_details 中可以看到具體的執行資訊，

相關的查詢SQL和執行結果 如下：

SELECT  a.job_name, a.actual_start_date, a.status

  FROM (SELECT *

          FROM dba_scheduler_job_run_details t

         where t.JOB_NAME like 'ORA$AT_OS_OPT_%'

         order by t.LOG_DATE desc) a

 where rownum <= 4;

從上圖 我們可以看出最近今天的自動收集統計資訊作業實際執行時間和是否執行成功。另外 我們也可以根據oracle 提供的指令碼$ORACLE_HOME/rdbms/admin 下的catmwin.sql指令碼中看到 統計資訊收集作業的整體搭建流程。有興趣的同學可以研究下此指令碼的內容。

我們來總結一下oracle 11g中的自動統計資訊收集的特點。

（1）oracle 11g 中的自動統計資訊收集作業每天自動執行任務gather_stats_prog來實現，每次執行的時候會先 生成名為 ORA$AT_OS_OPT_XXX的作業，然後再執行這個作業來自動收集統計資訊，其本質也是執行DBMS_STATS 包裡的儲存過程 gather_database_stats_job_proc。

（2）oracle 11g 中的自動統計資訊收集作業的可維護視窗有7個，視窗的名字分別是從週一到週日的英文名字。

（3）在11g 的版本中新增了對7個維護視窗的資源控制，其對應的Resource Plan 的名稱為DEFAULT_MAINTENANCE_PLAN。


---fdh