【效能優化】dbms_stats在ORACLE中的使用

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

　　exec dbms_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選項

　　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所示，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。

　　--*************************************************************

　　-- 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;

　　並行統計收集degree引數

　　Oracle推薦設定DBMS_STATS的DEGREE引數為DBMS_STATS.AUTO_DEGREE，該引數允許Oracle根據物件的大小和並行性初始化引數的設定選擇恰當的並行度。

　　聚簇索引，域索引，點陣圖連線索引不能並行收集。

　　如何使用dbms_stats分析統計資訊？

　　--建立統計資訊歷史保留表

　　sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

　　--匯出整個scheme的統計資訊

　　sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

　　--分析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') ;

　　--分析索引

　　SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

　　--如果發現執行計劃走錯，刪除表的統計資訊

　　SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

　　--匯入錶的歷史統計資訊

　　sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;

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

　　sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

　　--匯入索引的統計資訊

　　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';

　　分析資料庫（包括所有的使用者物件和系統物件）：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的執行情況：

　　select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

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

　　SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

　　JOB_NAME                       LAST_START_DATE

　　------------------------------ ----------------------------------------

　　AUTO_SPACE_ADVISOR_JOB        04-DEC-07 10.00.00.692269 PM +08:00

　　GATHER_STATS_JOB              04-DEC-07 10.00.00.701152 PM +08:00

　　FGR$AUTOPURGE_JOB

　　PURGE_LOG                      05-DEC-07 03.00.00.169059 AM PRC

　　然而這個自動化功能已經影響了很多系統的正常執行，晚上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可以直接修改初始化引數檔案，重新啟動資料庫

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-671424/，如需轉載，請註明出處，否則將追究法律責任。