dbms_stats包問世以后,Oracle專家可通過一種簡單的方式來為CBO收集統計數據。目前,已經不再推薦你使用老式的分析表和dbms_utility方法來生成CBO統計數據。那些古老的方式甚至有可能危及SQL的性能,因為它們並非總是能夠捕捉到有關表和索引的高質量信息。CBO使用對象統計,為所有SQL語句選擇最佳的執行計划。
dbms_stats能良好地估計統計數據(尤其是針對較大的分區表),並能獲得更好的統計結果,最終制定出速度更快的SQL執行計划。清單A展示了dbms_stats的一次示范執行情況,其中使用了options子句。
execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)為了充分認識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選項
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所示,那么為了檢索這些行,全表掃描的速度會快於索引掃描。--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
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_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
使用alter table xxx monitoring;命令來實現Oracle表監視時,需要使用dbms_stats中的auto選項。如清單D所示,auto選項根據數據分布以及應用程序訪問列的方式(例如通過監視而確定的一個列的工作量)來創建直方圖。使用method_opt=>’auto’類似於在dbms_stats的option參數中使用gather auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
並行收集
Oracle允許以並行方式來收集CBO統計數據,這就顯著提高了收集統計數據的速度。但是,要想並行收集統計數據,你需要一台安裝了多個CPU的SMP服務器。
更快的執行速度
dbms_stats是提高SQL執行速度的一種出色機制。通過使用dbms_stats來收集最高質量的統計數據,CBO能夠正確判斷執行任何SQL查詢時的最快途徑。dbms_stats還在不斷地改進。目前,它的一些令人激動的新特性(自動樣本大小和自動直方圖生成)已經顯著簡化了Oracle專家的工作。
如何使用dbms_stats分析統計信息?Dbms_stats是oracle8i新增的程序包,它使統計數據的生成和處理更加方便,很多人都在推薦使用dbms_stats替代analyze,我倒是不怎么用過,記錄一下
estimate_percent --估算抽樣百分比
method_opt:for table --只統計表 for all indexed columns --只統計有索引的表列 for all indexes --只分析統計相關索引
--創建統計信息歷史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;
pl/sql procedure successfully completed
--導出整個scheme的統計信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;
pl/sql procedure successfully completed
--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )
--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
pl/sql procedure successfully completed
--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
pl/sql procedure successfully completed
--如果發現執行計划走錯,刪除表的統計信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;
pl/sql procedure successfully completed
--導入表的歷史統計信息
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;
pl/sql procedure successfully completed
--如果進行分析后,大部分表的執行計划都走錯,需要導回整個scheme的統計信息
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');
pl/sql procedure successfully completed
--導入索引的統計信息
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')
--檢查是否導入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
WORK_LIST 4005 186 2007-10-12 15
SQL>
analyze和dbms_stats不同的地方:
analyze是同時更新表和索引的統計信息,而dbms_stats會先更新表的統計信息,然后再更新索引的統計信息,這里就有一個問題,就是當表的統計信息更新后,而索引的統計信息沒有被更新,這時候cbo就有可能選擇錯誤的plan