ORACLE收集統計信息
原文鏈接：http://blog.itpub.net/2317695/viewspace-1226138/
今天網上看到一篇關於收集統計信息的文章，還不錯，特轉載下來。 1. 理解什麼是統計信息
優化器統計信息就是一個更加詳細描述數據庫和數據庫對象的集合，這些統計信息被用於查詢優化器，讓其為每條SQL語句選擇最佳的執行計劃。優化器統計信息包括: · 表的統計信息
o 行數
o Block數 o 行平均長度
· 列的統計信息
o 列中不同值的數量
o 列中null的數量 o 數據分布(柱狀圖/直方圖) · 索引的統計信息
o 葉子塊的數量
o 索引的高度
o 聚簇因子(clustering factor) · 系統的統計信息
o I/O性能和利用 o CPU性能和利用 優化器統計信息存儲在下列數據字典中
· DBA_TABLES · DBA_OBJECT_TABLES · DBA_TAB_STATISTICS · DBA_TAB_COL_STATISTICS · DBA_TAB_HISTOGRAMS · DBA_INDEXES · DBA_IND_STATISTICS · DBA_CLUSTERS · DBA_TAB_PARTITIONS · DBA_TAB_SUBPARTITIONS · DBA_IND_PARTITIONS · DBA_IND_SUBPARTITIONS · DBA_PART_COL_STATISTICS · DBA_PART_HISTOGRAMS · DBA_SUBPART_COL_STATISTICS · DBA_SUBPART_HISTOGRAMS · INDEX_STATS 存儲ANALYZE ..VALIDATE STRUCTURE統計信息 · AUX_STATS$ 存儲CPU統計信息 · X$KCFIO 存儲I/O統計信息 因為數據庫中的對象會經常的變化，所以統計信息必須有規律的更新以便更加准確的描述這些數據庫對象。統計信息默認是由ORACLE自動維護的，不過我們也可以用DBMS_STATS包手動收集統計信息。DBMS_STATS包同樣提供了過程來維護統計信息。關於DBMS_STATS包更詳細的描述請參閱官方文檔PL/SQL Packages and Types Reference部分。
2. 自動收集統計信息
Oracle10g中，在安裝Oracle的時候，就默認創建了一個名為GATHER_STATS_JOB的job來自動收集優化器統計信息。這個job收集數據庫中所有對象的統計信息。默認的情況下這個job是周一到周五每天晚上10點到第二天早上6點以及整個周末來收集統計信息。 可以查看DBA_SCHEDULER_JOBS， DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS，DBA_SCHEDULER_JOB_RUN_DETAILS等視圖來查看JOB設置以及運行信息。
自動收集過期的統計信息依賴於表監控特征，在Oracle10g中表監控默認是開啟的，同時它也依賴STATISTICS_LEVEL參數的值，10g中默認為typical,只有將STATISTICS_LEVEL參數設置為ALL或者TYPICAL才能讓ORACLE識別過期的統計信息。
3. 關閉自動收集統計信息
在某些情況下，我們想關閉自動收集統計信息那麼我們可以利用如下方法: BEGIN DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); END; /
4. 何時該手動收集統計信息
有時候自動收集統計並不合適，因為自動收集統計信息是在午夜運行的，然而由於對象是在白天被修改了，導致導致的統計信息變得陳舊，這裡有2種這類對象: · 白天經常被delete,或者truncated之後又rebuild的表(經常變化的表) · 批量操作之後有10%或者以上的數據被更改的表(批量處理的表) · 對於經常變化的表，可以將其統計信息設置為null，當ORACLE遇到一個表沒有統計信息，ORACLE會動態采樣以便為查詢優化器收集必要的統計信息。動態采樣這個特征受到參數optimizer_dynamic_sampling的控制，它的默認值為2，同時呢optimizer_mode也能控制動態采樣，可將其設置為all. 以SCOTT用戶下的DEPT表為例，將一個表的統計信息設置為null的方法如下： BEGIN DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT'); DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');END;/我們也可以在表具有典型的，代表性的時候收集統計信息，並且鎖住其統計信息，因為在夜晚自動收集的統計信息未必適用於白天的負載，而典型的統計信息具有代表意義，所以這個時候采取lock其典型的統計信息更能讓CBO選擇更優的執行計劃。
至於上面的兩種方法用哪種，這個還需要根據業務，實際情況分析之。
· 對於批量處理的表 ，應該在批量處理完成的時候立即對其收集統計信息，可以將收集統計信息的腳本綁定到批量處理的腳本中。
· 對於外部表，只能通過gather_table_stats過程來收集統計信息，並且外部表不支持取樣，所以需要把gather_table_stats中的estimate_percent設置為null。 · 系統的統計信息也需要手動收集，因為這些信息是不會自動收集的。
· 對於固定對象，比如說動態性能表，需要手動的執行gather_fixed_objects_stats過程來收集。固定的對象反映了當前數據庫的活動。當數據庫活動處於具有代表性的時候，就應該收集這類統計信息。 5. 鎖住/解鎖統計信息
· LOCK_SCHEMA_STATS · LOCK_TABLE_STATS · UNLOCK_SCHEMA_STATS · UNLOCK_TABLE_STATS
6. 手動收集統計信息
· 如果你選擇手動收集統計信息，那麼你需要手動的收集所有用戶的統計信息，包括系統用戶。如果你數據庫中的數據是有規律的變化的，那麼你可以有規律的收集統計信息，以便統計信息能夠准確的反映數據庫中的對象的特征。
· 可以利用DBMS_STATS包，來收集表，索引，列，以及分區表的統計信息，DBMS_STATS不能收集CLUSTER 的統計信息，不過可以收集單個表來代替收集整個CLUSTER的統計信息。
· 當你收集表，列，索引的統計信息的時候，如果ORACLE在數據字典中發現這個對象已經收集了統計信息，那麼ORACLE會更新已經存在的統計信息，舊的統計信息會被保存下來，如果你願意還能還原舊的統計信息。
· 你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS來收集系統用戶的統計信息，這個過程收集所有的系統用戶的統計信息，包括SYS和SYSTEM，以及其他用戶，比如CTXSYS,DRSYS。
· 當數據庫對象的統計信息被更新之後，ORACLE會使已經解析的SQL語句作廢，當再次運行該SQL語句的時候，ORACLE會重新解析該SQL，優化器會自動的根據新的統計信息選擇一條新的執行計劃。對於分布式的數據庫，不會作廢。
· 收集統計信息的過程
o GATHER_INDEX_STATS --收集索引統計信息 o GATHER_TABLE_STATS --收集表，列，索引統計信息 o GATHER_SCHEMA_STATS --收集schema所有對象統計信息 o GATHER_DICTIONARY_STATS –-收集所有系統用戶的統計信息 o GATHER_DATABASE_STATS --收集數據庫所有對象統計信息
· 我們利用上面的過程收集統計信息的時候有幾個需要關心的參數
o 采樣
o 並行
o 分區
o 列統計以及直方圖/柱狀圖
o 過期的統計
o 自定義統計
? 在收集統計信息的操作過程中我們可以使用采樣來評估統計信息。采樣對於收集統計信息來說是一項很重要的技術。如果在收集統計信息的時候不使用采樣，那麼就需要對表進行全表掃描，以及排序整個表。通過采樣可以降低收集必要的統計信息所花費的資源。
控制采樣的參數是ESTIMATE_PERCENT，采樣的參數可以設置任意值(當然要在范圍內)，不過ORACLE公司推薦設置ESTIMATE_PERCENT為DBMS_STATS.AUTO_SAMPLE_SIZE。 AUTO_SAMPLE_SILE可以讓ORACLE自己決定最好的采樣值，因為不同類型(table,index,column)的統計信息有不同的需求。采樣的例子： EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’,DBMS_STATS.AUTO_SAMPLE_SIZE);
當ESTIMATE_PERCENT參數是手動指定的，如果手動指定的參數過小，不能收集到足夠的信息，那麼DBMS_STATS可能會自動增長ESTIMATE_PERCENT的值，這樣就能確保收集到足夠的統計信息。
? 我們既可以串行的收集統計信息，也可以並行的收集統計信息。參數DEGREE控制DBMS_STATS是否使用並行特征。ORACLE公司推薦將DEGREE參數設置為DBMS_STATS.AUTO_DEGREE。這樣設置過後，ORACLE就能夠根據OBJECT的SIZE，以及與並行有關的init參數來決定一個恰當的並行度，收集統計信息。注意:cluster index,domain index,bitmap join index不能使用並行特征。
? 對於分區表和分區索引，DBMS_STATS既可以單獨的收集分區統計信息，也可以收集整個表/索引的統計信息。對於組合分區，DBMS_STATS也能夠收集子分區，分區，以及整個表/索引的統計信息。參數GRANULARITY控制分區統計信息的收集。因為分區統計信息，全局統計信息對於大多數系統來說都是非常重要的，所以ORACLE公司推薦將其設置為AUTO來收集分區，以及全局的統計信息。
? 當對表收集統計信息的時候，DBMS_STATS會收集列的數據分布信息。數據分布最基本的統計信息就是這個列的最大值與最小值。如果這一列是傾斜的，那麼優化器僅僅根據列最大值與最小值是無法制定出准確的執行計劃的。對於傾斜的數據分布，我們可以收集列的直方圖/柱狀圖統計信息，這樣可以讓優化器制定出更加准確的執行計劃。 參數METHOD_OPT控制柱狀圖的收集。ORACLE公司推薦設置METHOD_OPT為FOR ALL COLUMNS SIZE AUTO。這樣設置過後ORACLE會自動的判斷哪一列需要收集柱狀圖，並且自動的設置柱狀圖的bucket。你同樣可以手動的設置哪一列需要收集柱狀圖，以及柱狀圖的bucket。
? 為了知道統計信息是否過期，ORACLE提供了表監控功能。將init參數STATISTICS_LEVEL設置為ALL或者TYPICAL(默認)，就開啟了表監控的功能(10g已經不需要alter table monitor了)。表監控功能跟蹤表的insert,update,delete，truncate，操作，並且記錄在DBA_TAB_MODIFICATIONS視圖裡面。我們在查詢DBA_TAB_MODIFICATIONS視圖的時候有可能查詢不到結果，或者查詢的結果不准確，這個時候需要用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO過程將內存中的信息刷新到 該視圖中。OPTIONS參數設置為GATHER STALE或者GATHER AUTO，就會讓DBMS_STATS判斷表的統計信息是否過期(注意GATHER_TABLE_STATS中沒有這個參數，只有GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS過程中有這個參數)。判斷表的統計信息是否過期的依據是是否有10%以上的數據被修改過，如果被修改過了，那麼ORACLE就認為之前的統計信息過期了，ORACLE會重新收集統計信息。
? 在我們創建了函數索引之後，我們要為列收集統計信息，這個時候我們需要設置參數METHOD_OPT為FOR ALL HIDDEN COLUMNS。
7. 收集統計信息的策略
通常情況下，我們會將ORACLE自動收集統計信息功能給關閉，我們會采用手動的方式給數據庫收集統計信息。至於收集統計信息的策略需要根據系統來確定。下面說說幾種常見的情況： · 如果你系統中的表的數據是增量(有規律)的增加，也就是說你幾乎不做任何的批量處理操作，比如批量刪除，批量加載操作。對於這樣的表收集統計信息是非常簡單的。你可以通過查看DBA_TAB_MODIFICATIONS視圖來觀察表的變化情況，觀察表中數據量的變化是否超過了10%,並且記錄下天數。這樣你就可以每隔這樣的時間間隔對其收集一次統計信息。你可以用CRONTAB,或者JOB調用GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS過程來收集統計信息。 · 對於經常批量操作的表，那麼表的統計信息就必須在批量操作之後對其收集統計信息。
· 對於分區表，通常只有一個分區被修改，這種情況下可以只收集單獨分區的統計信息，不過收集整個表的統計信息還是非常有必要的。
· 最後我會給出兩個腳本，判斷該表是否需要收集統計信息。
8. 收集統計信息的一些例子
例子1對表收集統計信息 BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'DEPT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/ 上面的例子收集SCOTT.DEPT表的統計信息。這裡面值得關注的一個參數就是method_opt。這個參數控制是否收集列的直方圖信息。通常情況下，是不會收集直方圖的，關於直方圖不是三言兩語可以說明白的。 它的四個選項method_opt=>'for all columns size skewonly' ORACLE會根據數據分布收集直方圖
method_opt=>'for all columns size repeat' 只有以前收集過直方圖，才會收集直方圖信息，所以一般我們會設置method_opt 為repeat
method_opt=>'for all columns size auto' ORACLE會根據數據分布以及列的workload來確定是否收集直方圖
method_opt=>'for all columns size interger' 我們自己指定一個bucket值
例子2對某一個schma收集統計信息
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
ptions => 'gather auto',
degree => DBMS_STATS.AUTO_DEGREE,
method_opt => 'for all columns size repeat',
cascade => TRUE
);
END;
/ 上面的例子收集SCOTT模式下所有對象的統計信息。裡面值得注意的一個參數就是options。前面已經講到過，他與表監控有關。它有四個選項
Options =>’gather’ 收集所有對象的統計信息
Options =>’gather empty’ 只收集還沒被統計的表
Options =>’gather stale’ 只收集修改量超過10%的表
Options =>’gather auto’ 相當於empty+stale ，所以我們一般設置為AUTO。
例子3 對一個分區表收集統計信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE, granularity => 'ALL',
cascade=>TRUE
);
END;
/ 上面的例子收集ROBINSON.P_TEST表的統計信息。裡面值得注意的一個參數就是granularity，他有7個選項。
granularity => 'ALL' 收集分區，子分區，全局的統計信息
granularity => 'AUTO' 這個是默認的設置，ORACLE會根據分區類型來決定用ALL,GLOBAL AND PARTITION ,還是其他的
granularity => 'DEFAULT' 這個是過期了的
granularity => 'GLOBAL' 收集全局統計信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分區統計信息，但是不收集子分區統計信息
granularity => 'PARTITION' 收集分區統計信息
granularity => 'SUBPARTITION' 收集子分區統計信息 當然我們可以指定partname，自己控制對哪個分區收集統計信息
9. 列出表需要收集統計信息的腳本
普通表
set serveroutput on declare -----select OVER THE Change RATE TABLES--------------- cursor overchangerate is select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows from dba_tab_modifications a, dba_tables b where a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1 or a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ; ----select the unanalyzed table--------------- cursor nullmonitor is select owner, table_name from dba_tables where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and last_analyzed is null; begin dbms_output.enable(1000000); ----flush the monitorring information into the dba_tab_modifications DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ----display the unanalyzed table-------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'); dbms_output.put_line('Unalalyzed tables:'); for v_null in nullmonitor loop dbms_output.put_line(v_null.owner || '.' || v_null.table_name || ' has not been analyzed, consider gathering statistics'); end loop; ----display the information------------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' ); dbms_output.put_line('Over the Change_Rate 10%:'); for v_topinsert in overchangerate loop dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' || 'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes || ' rows. consider gathering statistics'); end loop; dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'); end; / 下面的是分區表
set serveroutput on declare -----select OVER THE Change RATE TABLES--------------- cursor overchangerate is select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name and a.partition_name=b.partition_name and a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS') group by a.table_owner,a.table_name,a.partition_name having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 or (sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 or (sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 order by a.table_name; begin dbms_output.enable(1000000); ----flush the monitorring information into the dba_tab_modifications DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ----display the top_n_insert information------------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' ); dbms_output.put_line('Over the Change_Rate 10%:'); for v_topinsert in overchangerate loop dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name || ' once has ' || v_topinsert.num_rows || ' rows, ' || 'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes || ' rows. consider gathering statistics'); end loop; dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' ); end; / 在此特別聲明一點，在oracle11.2版本中有一個相關的BUG Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8 該BUG會導致嚴重的性能問題。 oracle官方申明，只有在12.1版本才解決這個問題，臨時解決方案是手動關閉動態采樣。 順便貼上10個level的動態采樣介紹
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
