dbms_stats能良好地估計統計數據（尤其是針對較大的分區表）並能獲得更好的統計結果最終制定出速度更快的SQL執行計劃
exec dbms_statsgather_schema_stats(
ownname => SCOTT
options => GATHER AUTO
estimate_percent => dbms_statsauto_sample_size
method_opt => for all columns size repeat
degree =>
) 為了充分認識dbms_stats的好處需要仔細體會每一條主要的預編譯指令（directive）下面讓我們研究每一條指令並體會如何用它為基於代價的SQL優化器收集最高質量的統計數據
options參數
使用個預設的方法之一這個選項能控制Oracle統計的刷新方式
gather——重新分析整個架構（Schema）
gather empty——只分析目前還沒有統計的表
gather stale——只重新分析修改量超過%的表（這些修改包括插入更新和刪除）
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_statsauto_sample_size
要驗證自動統計采樣的准確性你可檢視dba_tables sample_size列一個有趣的地方是在使用自動采樣時Oracle會為一個樣本尺寸選擇到的百分比記住統計數據質量越好CBO做出的決定越好
method_opt選項
method_optfor 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子句中還有一些重要的新選項包括skewonlyrepeat和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優化器決定是進行索引訪問還是進行全表掃描訪問例如在一個索引中假定有一個列在%的行中如清單B所示那麼為了檢索這些行全表掃描的速度會快於索引掃描
*************************************************************
SKEWONLY option—Detailed analysis
Use this method for a firsttime analysis for skewed indexes
This runs a long time because all indexes are examined
*************************************************************
begin
dbms_statsgather_schema_stats(
ownname => SCOTT
estimate_percent => dbms_statsauto_sample_size
method_opt => for all columns size skewonly
degree =>
);
end;
重新分析統計數據時使用repeat選項重新分析任務所消耗的資源就會少一些使用repeat選項（清單C）時只會為現有的直方圖重新分析索引不再搜索其他直方圖機會定期重新分析統計數據時你應該采取這種方式
**************************************************************
REPEAT OPTION Only reanalyze histograms for indexes
that have histograms
Following the initial analysis the weekly analysis
job will use the repeat option The repeat option
tells dbms_stats that no indexes have changed and
it will only reanalyze histograms for
indexes that have histograms
**************************************************************
begin
dbms_statsgather_schema_stats(
ownname => SCOTT
estimate_percent => dbms_statsauto_sample_size
method_opt => for all columns size repeat
degree =>
);
end;
使用alter table xxx monitoring;命令來實現Oracle表監視時需要使用dbms_stats中的auto選項如清單D所示auto選項根據數據分布以及應用程序訪問列的方式（例如通過監視而確定的一個列的工作量）來創建直方圖使用method_opt=>auto類似於在dbms_stats的option參數中使用gather auto
begin
dbms_statsgather_schema_stats(
ownname => SCOTT
estimate_percent => dbms_statsauto_sample_size
method_opt => for all columns size auto
degree =>
);
end;
並行統計收集degree參數
Oracle推薦設置DBMS_STATS的DEGREE參數為DBMS_STATSAUTO_DEGREE該參數允許Oracle根據對象的大小和並行性初始化參數的設置選擇恰當的並行度
聚簇索引域索引位圖連接索引不能並行收集
如何使用dbms_stats分析統計信息？
創建統計信息歷史保留表
sql> exec dbms_statscreate_stat_table(ownname => scottstattab => stat_table) ;
導出整個scheme的統計信息
sql> exec dbms_statsexport_schema_stats(ownname => scottstattab => stat_table) ;
分析scheme
Exec dbms_statsgather_schema_stats(
ownname => scott
options => GATHER AUTO
estimate_percent => dbms_statsauto_sample_size
method_opt => for all indexed columns
degree => )
分析表
sql> exec dbms_statsgather_table_stats(ownname => scotttabname => work_listestimate_percent => method_opt=> for all indexed columns) ;
分析索引
SQL> exec dbms_statsgather_index_stats(ownname => crmindname => IDX_ADM_PERMISSION_PID_MIDestimate_percent => degree => ) ;
如果發現執行計劃走錯刪除表的統計信息
SQL>dbms_statsdelete_table_stats(ownname => scotttabname => work_list) ;
導入表的歷史統計信息
sql> exec dbms_statsimport_table_stats(ownname => scotttabname => work_liststattab => stat_table) ;
如果進行分析後大部分表的執行計劃都走錯需要導回整個scheme的統計信息
sql> exec dbms_statsimport_schema_stats(ownname => scottstattab => stat_table);
導入索引的統計信息
SQL> exec dbms_statsimport_index_stats(ownname => crmindname => IDX_ADM_PERMISSION_PID_MIDstattab => stat_table)
檢查是否導入成功
SQL> select table_namenum_rowsablocksalast_analyzed from all_tables a where atable_name=WORK_LIST;
分析數據庫（包括所有的用戶對象和系統對象）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 g開始Oracle在建庫後就默認創建了一個名為GATHER_STATS_JOB的定時任務用於自動收集CBO的統計信息
這個自動任務默認情況下在工作日晚上和周末全天開啟調用DBMS_STATSGATHER_DATABASE_STATS_JOB_PROC收集統計信息
該過程首先檢測統計信息缺失和陳舊的對象然後確定優先級再開始進行統計信息
可以通過以下查詢這個JOB的運行情況
select * from Dba_Scheduler_Jobs where JOB_NAME =GATHER_STATS_JOB
其實同在點運行的Job還有一個AUTO_SPACE_ADVISOR_JOB
SQL> select JOB_NAMELAST_START_DATE from dba_scheduler_jobs;
JOB_NAME LAST_START_DATE
AUTO_SPACE_ADVISOR_JOB DEC PM +:
GATHER_STATS_JOB DEC PM +:
FGR$AUTOPURGE_JOB
PURGE_LOG DEC AM PRC
然而這個自動化功能已經影響了很多系統的正常運行晚上點對於大部分生產系統也並非空閒時段
而自動分析可能導致極為嚴重的闩鎖競爭進而可能導致數據庫Hang或者Crash
所以建議最好關閉這個自動統計信息收集功能
方法之一:
exec dbms_schedulerdisable(SYSGATHER_STATS_JOB);
恢復自動分析:
exec dbms_schedulerenable(SYSGATHER_STATS_JOB);
方法二
alter system set _optimizer_autostats_job=false scope=spfile;
alter system set _optimizer_autostats_job=true scope=spfile;
Pfile可以直接修改初始化參數文件重新啟動數據庫
From:http://tw.wingwit.com/Article/program/Oracle/201311/18864.html
