






options參數(shù)
使用4個預(yù)設(shè)的方法之一,這個選項能控制Oracle統(tǒng)計的刷新方式:
gather——重新分析整個架構(gòu)(Schema)。
gather empty——只分析目前還沒有統(tǒng)計的表。
gather stale——只重新分析修改量超過10%的表(這些修改包括插入、更新和刪除)。
gather auto——重新分析當(dāng)前沒有統(tǒng)計的對象,以及統(tǒng)計數(shù)據(jù)過期(變臟)的對象。注意,使用gather auto類似于組合使用gather stale和gather empty。
注意,無論gather stale還是gather auto,都要求進行監(jiān)視。如果你執(zhí)行一個alter table xxx monitoring命令,Oracle會用dba_tab_modifications視圖來跟蹤發(fā)生變動的表。這樣一來,你就確切地知道,自從上一次分析統(tǒng)計數(shù)據(jù)以來,發(fā)生了多少次插入、更新和刪除操作。
estimate_percent參數(shù)是一種比較新的設(shè)計,它允許Oracle的dbms_stats在收集統(tǒng)計數(shù)據(jù)時,自動估計要采樣的一個segment的最佳百分比:
estimate_percent => dbms_stats.auto_sample_size
要驗證自動統(tǒng)計采樣的準確性,你可檢視dba_tables sample_size列。一個有趣的地方是,在使用自動采樣時,Oracle會為一個樣本尺寸選擇5到20的百分比。記住,統(tǒng)計數(shù)據(jù)質(zhì)量越好,CBO做出的決定越好。
method_opt選項
method_opt:for table --只統(tǒng)計表
for all indexed columns --只統(tǒng)計有索引的表列
for all indexes --只分析統(tǒng)計相關(guān)索引
for all columns
dbms_stats的method_opt參數(shù)尤其適合在表和索引數(shù)據(jù)發(fā)生變化時刷新統(tǒng)計數(shù)據(jù)。method_opt參數(shù)也適合用于判斷哪些列需要直方圖(histograms)。
某些情況下,索引內(nèi)的各個值的分布會影響CBO是使用一個索引還是執(zhí)行一次全表掃描的決策。例如,假如在where子句中指定的值的數(shù)量不對稱,全表掃描就顯得比索引訪問更經(jīng)濟。
如果你有一個高度傾斜的索引(某些值的行數(shù)不對稱),就可創(chuàng)建Oracle直方圖統(tǒng)計。但在現(xiàn)實世界中,出現(xiàn)這種情況的機率相當(dāng)小。使用CBO時,最常見的錯誤之一就是在CBO統(tǒng)計中不必要地引入直方圖。根據(jù)經(jīng)驗,只有在列值要求必須修改執(zhí)行計劃時,才應(yīng)使用直方圖。
為了智能地生成直方圖,Oracle為dbms_stats準備了method_opt參數(shù)。在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發(fā)現(xiàn)一個索引的各個列分布得不均勻,就會為那個索引創(chuàng)建直方圖,幫助基于代價的SQL優(yōu)化器決定是進行索引訪問,還是進行全表掃描訪問。例如,在一個索引中,假定有一個列在50%的行中,如清單B所示,那么為了檢索這些行,全表掃描的速度會快于索引掃描。















重新分析統(tǒng)計數(shù)據(jù)時,使用repeat選項,重新分析任務(wù)所消耗的資源就會少一些。使用repeat選項(清單C)時,只會為現(xiàn)有的直方圖重新分析索引,不再搜索其他直方圖機會。定期重新分析統(tǒng)計數(shù)據(jù)時,你應(yīng)該采取這種方式。



















使用alter table xxx monitoring;命令來實現(xiàn)Oracle表監(jiān)視時,需要使用dbms_stats中的auto選項。如清單D所示,auto選項根據(jù)數(shù)據(jù)分布以及應(yīng)用程序訪問列的方式(例如通過監(jiān)視而確定的一個列的工作量)來創(chuàng)建直方圖。使用method_opt=>’auto’類似于在dbms_stats的option參數(shù)中使用gather auto。








并行統(tǒng)計收集degree參數(shù)
Oracle推薦設(shè)置DBMS_STATS的DEGREE參數(shù)為DBMS_STATS.AUTO_DEGREE,該參數(shù)允許Oracle根據(jù)對象的大小和并行性初始化參數(shù)的設(shè)置選擇恰當(dāng)?shù)牟⑿卸取?br />
聚簇索引,域索引,位圖連接索引不能并行收集。
如何使用dbms_stats分析統(tǒng)計信息?
--創(chuàng)建統(tǒng)計信息歷史保留表

--導(dǎo)出整個scheme的統(tǒng)計信息

--分析scheme






--分析表

--分析索引

--如果發(fā)現(xiàn)執(zhí)行計劃走錯,刪除表的統(tǒng)計信息

--導(dǎo)入表的歷史統(tǒng)計信息

--如果進行分析后,大部分表的執(zhí)行計劃都走錯,需要導(dǎo)回整個scheme的統(tǒng)計信息

--導(dǎo)入索引的統(tǒng)計信息

--檢查是否導(dǎo)入成功

分析數(shù)據(jù)庫(包括所有的用戶對象和系統(tǒng)對象):gather_database_stats
分析用戶所有的對象(包括表、索引、簇):gather_schema_stats
分析表:gather_table_stats
分析索引:gather_index_stats
刪除數(shù)據(jù)庫統(tǒng)計信息:delete_database_stats
刪除用戶方案統(tǒng)計信息:delete_schema_stats
刪除表統(tǒng)計信息:delete_table_stats
刪除索引統(tǒng)計信息:delete_index_stats
刪除列統(tǒng)計信息:delete_column_stats
設(shè)置表統(tǒng)計信息:set_table_stats
設(shè)置索引統(tǒng)計信息:set_index_stats
設(shè)置列統(tǒng)計信息:set_column_stats
從Oracle Database 10g開始,Oracle在建庫后就默認創(chuàng)建了一個名為GATHER_STATS_JOB的定時任務(wù),用于自動收集CBO的統(tǒng)計信息。
這個自動任務(wù)默認情況下在工作日晚上10:00-6:00和周末全天開啟。調(diào)用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統(tǒng)計信息。
該過程首先檢測統(tǒng)計信息缺失和陳舊的對象。然后確定優(yōu)先級,再開始進行統(tǒng)計信息。
可以通過以下查詢這個JOB的運行情況:

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









然而這個自動化功能已經(jīng)影響了很多系統(tǒng)的正常運行,晚上10點對于大部分生產(chǎn)系統(tǒng)也并非空閑時段。
而自動分析可能導(dǎo)致極為嚴重的閂鎖競爭,進而可能導(dǎo)致數(shù)據(jù)庫Hang或者Crash。
所以建議最好關(guān)閉這個自動統(tǒng)計信息收集功能
方法之一:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
恢復(fù)自動分析:
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可以直接修改初始化參數(shù)文件,重新啟動數(shù)據(jù)庫。