ORACLE中DBMS_STATS.GATHER_SCHEMA_STATS詳解

dbms_stats能良好地估計統計數據（尤其是針對較大的分區表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計劃。

       為了充分認識dbms_stats的好處，需要仔細體會每一條主要的預編譯指令（directive）。下麵讓我們研究每一條指令，並體會如何用它為基於代價的SQL優化器收集最高質量的統計數據。

options參數

使用4個預設的方法之一，這個選項能控制Oracle統計的刷新方式：

gather——重新分析整個架構（Schema）。 
gather empty——只分析目前還沒有統計的表。 
gather stale——只重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）。 
gather auto——重新分析當前沒有統計的對象，以及統計數據過期（變臟）的對象。註意，使用gather auto類似於組合使用gather stale和gather empty。 
       註意，無論gather stale還是gather auto，都要求進行監視。如果你執行一個alter table xxx monitoring命令，Oracle會用dba_tab_modifications視圖來跟蹤發生變動的表。這樣一來，你就確切地知道，自從上一次分析統計數據以來，發生了多少次插入、更新和刪除操作。

estimate_percent選項

estimate_percent參數是一種比較新的設計，它允許Oracle的dbms_stats在收集統計數據時，自動估計要採樣的一個segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

       要驗證自動統計採樣的準確性，你可檢視dba_tables sample_size列。一個有趣的地方是，在使用自動採樣時，Oracle會為一個樣本尺寸選擇5到20的百分比。記住，統計數據質量越好，CBO做出的決定越好。

method_opt選項
method_opt：for table --只統計表　
                      for all indexed columns --只統計有索引的表列 
                      for all indexes --只分析統計相關索引 
                      for all columns

dbms_stats的method_opt參數尤其適合在表和索引數據發生變化時刷新統計數據。method_opt參數也適合用於判斷哪些列需要直方圖（histograms）。

      某些情況下，索引內的各個值的分佈會影響CBO是使用一個索引還是執行一次全表掃描的決策。例如，假如在where子句中指定的值的數量不對稱，全表掃描就顯得比索引訪問更經濟。

       如果你有一個高度傾斜的索引（某些值的行數不對稱），就可創建Oracle直方圖統計。但在現實世界中，出現這種情況的機率相當小。使用CBO時，最常見的錯誤之一就是在CBO統計中不必要地引入直方圖。根據經驗，只有在列值要求必須修改執行計劃時，才應使用直方圖。

為了智能地生成直方圖，Oracle為dbms_stats準備了method_opt參數。在method_opt子句中，還有一些重要的新選項，包括skewonly，repeat和auto：
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

     skewonly選項會耗費大量處理時間，因為它要檢查每個索引中的每個列的值的分佈情況。

     假如dbms_stat發現一個索引的各個列分佈得不均勻，就會為那個索引創建直方圖，幫助基於代價的SQL優化器決定是進行索引訪問，還是進行全表掃描訪問。例如，在一個索引中，假定有一個列在50%的行中，如清單B所示，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。

        重新分析統計數據時，使用repeat選項，重新分析任務所消耗的資源就會少一些。使用repeat選項（清單C）時，只會為現有的直方圖重新分析索引，不再搜索其他直方圖機會。定期重新分析統計數據時，你應該採取這種方式。

       使用alter table xxx monitoring;命令來實現Oracle表監視時，需要使用dbms_stats中的auto選項。如清單D所示，auto選項根據數據分佈以及應用程式訪問列的方式（例如通過監視而確定的一個列的工作量）來創建直方圖。使用method_opt=>’auto’類似於在dbms_stats的option參數中使用gather auto。

並行統計收集degree參數
Oracle推薦設置DBMS_STATS的DEGREE參數為DBMS_STATS.AUTO_DEGREE，該參數允許Oracle根據對象的大小和並行性初始化參數的設置選擇恰當的並行度。
聚簇索引，域索引，點陣圖連接索引不能並行收集。

 

如何使用dbms_stats分析統計信息？
--創建統計信息歷史保留表 

--導出整個scheme的統計信息 

--分析scheme

--分析表

--分析索引

--如果發現執行計划走錯，刪除表的統計信息

--導入表的歷史統計信息

--如果進行分析後，大部分表的執行計劃都走錯，需要導回整個scheme的統計信息

--導入索引的統計信息

--檢查是否導入成功

分析資料庫（包括所有的用戶對象和系統對象）：gather_database_stats 
分析用戶所有的對象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats 
刪除資料庫統計信息：delete_database_stats
刪除用戶方案統計信息：delete_schema_stats
刪除表統計信息：delete_table_stats
刪除索引統計信息：delete_index_stats
刪除列統計信息：delete_column_stats 
設置表統計信息：set_table_stats
設置索引統計信息：set_index_stats
設置列統計信息：set_column_stats 

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

這個自動任務預設情況下在工作日晚上10：00-6：00和周末全天開啟。調用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計信息。
該過程首先檢測統計信息缺失和陳舊的對象。然後確定優先順序，再開始進行統計信息。

可以通過以下查詢這個JOB的運行情況：

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

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

所以建議最好關閉這個自動統計信息收集功能

方法之一:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
恢復自動分析:
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
Pfile可以直接修改初始化參數文件，重新啟動資料庫。