ORACLE 收集統計信息
今天網上看到一篇關于收集統計信息的文章，還不錯，特轉載下來。
1. 理解什么是統計信息
優(yōu)化器統計信息就是一個更加詳細描述數據庫和數據庫對象的集合，這些統計信息被用于查詢優(yōu)化器，讓其為每條SQL語句選擇最佳的執(zhí)行計劃。優(yōu)化器統計信息包括:
· 表的統計信息
o 行數
o Block數
o 行平均長度
· 列的統計信息
o 列中不同值的數量
o 列中null的數量
o 數據分布(柱狀圖/直方圖)
· 索引的統計信息
o 葉子塊的數量
o 索引的高度
o 聚簇因子(clustering factor)
· 系統的統計信息
o I/O性能和利用
o CPU性能和利用
優(yōu)化器統計信息存儲在下列數據字典中
· 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統計信息
因為數據庫中的對象會經常的變化，所以統計信息必須有規(guī)律的更新以便更加準確的描述這些數據庫對象。統計信息默認是由ORACLE自動維護的，不過我們也可以用DBMS_STATS包手動收集統計信息。DBMS_STATS包同樣提供了過程來維護統計信息。關于DBMS_STATS包更詳細的描述請參閱官方文檔PL/SQL Packages and Types Reference部分。
2. 自動收集統計信息
Oracle10g中，在安裝Oracle的時候，就默認創(chuàng)建了一個名為GATHER_STATS_JOB的job來自動收集優(yōu)化器統計信息。這個job收集數據庫中所有對象的統計信息。默認的情況下這個job是周一到周五每天晚上10點到第二天早上6點以及整個周末來收集統計信息。
可以查看DBA_SCHEDULER_JOBS， DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS，DBA_SCHEDULER_JOB_RUN_DETAILS等視圖來查看JOB設置以及運行信息。
自動收集過期的統計信息依賴于表監(jiān)控特征，在Oracle10g中表監(jiān)控默認是開啟的，同時它也依賴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會動態(tài)采樣以便為查詢優(yōu)化器收集必要的統計信息。動態(tài)采樣這個特征受到參數optimizer_dynamic_sampling的控制，它的默認值為2，同時呢optimizer_mode也能控制動態(tài)采樣，可將其設置為all.
以SCOTT用戶下的DEPT表為例，將一個表的統計信息設置為null的方法如下：
BEGIN DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT'); DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');END;/我們也可以在表具有典型的，代表性的時候收集統計信息，并且鎖住其統計信息，因為在夜晚自動收集的統計信息未必適用于白天的負載，而典型的統計信息具有代表意義，所以這個時候采取lock其典型的統計信息更能讓CBO選擇更優(yōu)的執(zhí)行計劃。
至于上面的兩種方法用哪種，這個還需要根據業(yè)務，實際情況分析之。
· 對于批量處理的表 ，應該在批量處理完成的時候立即對其收集統計信息，可以將收集統計信息的腳本綁定到批量處理的腳本中。
· 對于外部表，只能通過gather_table_stats過程來收集統計信息，并且外部表不支持取樣，所以需要把gather_table_stats中的estimate_percent設置為null。
· 系統的統計信息也需要手動收集，因為這些信息是不會自動收集的。
· 對于固定對象，比如說動態(tài)性能表，需要手動的執(zhí)行gather_fixed_objects_stats過程來收集。固定的對象反映了當前數據庫的活動。當數據庫活動處于具有代表性的時候，就應該收集這類統計信息。
5. 鎖住/解鎖統計信息
· LOCK_SCHEMA_STATS
· LOCK_TABLE_STATS
· UNLOCK_SCHEMA_STATS
· UNLOCK_TABLE_STATS
6. 手動收集統計信息
· 如果你選擇手動收集統計信息，那么你需要手動的收集所有用戶的統計信息，包括系統用戶。如果你數據庫中的數據是有規(guī)律的變化的，那么你可以有規(guī)律的收集統計信息，以便統計信息能夠準確的反映數據庫中的對象的特征。
· 可以利用DBMS_STATS包，來收集表，索引，列，以及分區(qū)表的統計信息，DBMS_STATS不能收集CLUSTER 的統計信息，不過可以收集單個表來代替收集整個CLUSTER的統計信息。
· 當你收集表，列，索引的統計信息的時候，如果ORACLE在數據字典中發(fā)現這個對象已經收集了統計信息，那么ORACLE會更新已經存在的統計信息，舊的統計信息會被保存下來，如果你愿意還能還原舊的統計信息。
· 你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS來收集系統用戶的統計信息，這個過程收集所有的系統用戶的統計信息，包括SYS和SYSTEM，以及其他用戶，比如CTXSYS,DRSYS。
· 當數據庫對象的統計信息被更新之后，ORACLE會使已經解析的SQL語句作廢，當再次運行該SQL語句的時候，ORACLE會重新解析該SQL，優(yōu)化器會自動的根據新的統計信息選擇一條新的執(zhí)行計劃。對于分布式的數據庫，不會作廢。
· 收集統計信息的過程
o GATHER_INDEX_STATS --收集索引統計信息
o GATHER_TABLE_STATS --收集表，列，索引統計信息
o GATHER_SCHEMA_STATS --收集schema所有對象統計信息
o GATHER_DICTIONARY_STATS –-收集所有系統用戶的統計信息
o GATHER_DATABASE_STATS --收集數據庫所有對象統計信息
· 我們利用上面的過程收集統計信息的時候有幾個需要關心的參數
o 采樣
o 并行
o 分區(qū)
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不能使用并行特征。
Ø 對于分區(qū)表和分區(qū)索引，DBMS_STATS既可以單獨的收集分區(qū)統計信息，也可以收集整個表/索引的統計信息。對于組合分區(qū)，DBMS_STATS也能夠收集子分區(qū)，分區(qū)，以及整個表/索引的統計信息。參數GRANULARITY控制分區(qū)統計信息的收集。因為分區(qū)統計信息，全局統計信息對于大多數系統來說都是非常重要的，所以ORACLE公司推薦將其設置為AUTO來收集分區(qū)，以及全局的統計信息。
Ø 當對表收集統計信息的時候，DBMS_STATS會收集列的數據分布信息。數據分布最基本的統計信息就是這個列的最大值與最小值。如果這一列是傾斜的，那么優(yōu)化器僅僅根據列最大值與最小值是無法制定出準確的執(zhí)行計劃的。對于傾斜的數據分布，我們可以收集列的直方圖/柱狀圖統計信息，這樣可以讓優(yōu)化器制定出更加準確的執(zhí)行計劃。
參數METHOD_OPT控制柱狀圖的收集。ORACLE公司推薦設置METHOD_OPT為FOR ALL COLUMNS SIZE AUTO。這樣設置過后ORACLE會自動的判斷哪一列需要收集柱狀圖，并且自動的設置柱狀圖的bucket。你同樣可以手動的設置哪一列需要收集柱狀圖，以及柱狀圖的bucket。
Ø 為了知道統計信息是否過期，ORACLE提供了表監(jiān)控功能。將init參數STATISTICS_LEVEL設置為ALL或者TYPICAL(默認)，就開啟了表監(jiān)控的功能(10g已經不需要alter table monitor了)。表監(jiān)控功能跟蹤表的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會重新收集統計信息。
Ø 在我們創(chuàng)建了函數索引之后，我們要為列收集統計信息，這個時候我們需要設置參數METHOD_OPT為FOR ALL HIDDEN COLUMNS。
7. 收集統計信息的策略
通常情況下，我們會將ORACLE自動收集統計信息功能給關閉，我們會采用手動的方式給數據庫收集統計信息。至于收集統計信息的策略需要根據系統來確定。下面說說幾種常見的情況：
· 如果你系統中的表的數據是增量(有規(guī)律)的增加，也就是說你幾乎不做任何的批量處理操作，比如批量刪除，批量加載操作。對于這樣的表收集統計信息是非常簡單的。你可以通過查看DBA_TAB_MODIFICATIONS視圖來觀察表的變化情況，觀察表中數據量的變化是否超過了10%,并且記錄下天數。這樣你就可以每隔這樣的時間間隔對其收集一次統計信息。你可以用CRONTAB,或者JOB調用GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS過程來收集統計信息。
· 對于經常批量操作的表，那么表的統計信息就必須在批量操作之后對其收集統計信息。
· 對于分區(qū)表，通常只有一個分區(qū)被修改，這種情況下可以只收集單獨分區(qū)的統計信息，不過收集整個表的統計信息還是非常有必要的。
· 最后我會給出兩個腳本，判斷該表是否需要收集統計信息。
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。前面已經講到過，他與表監(jiān)控有關。它有四個選項
Options =>’gather’ 收集所有對象的統計信息
Options =>’gather empty’ 只收集還沒被統計的表
Options =>’gather stale’ 只收集修改量超過10%的表
Options =>’gather auto’ 相當于empty+stale ，所以我們一般設置為AUTO。
例子3 對一個分區(qū)表收集統計信息
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' 收集分區(qū)，子分區(qū)，全局的統計信息
granularity => 'AUTO' 這個是默認的設置，ORACLE會根據分區(qū)類型來決定用ALL,GLOBAL AND PARTITION ,還是其他的
granularity => 'DEFAULT' 這個是過期了的
granularity => 'GLOBAL' 收集全局統計信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分區(qū)統計信息，但是不收集子分區(qū)統計信息
granularity => 'PARTITION' 收集分區(qū)統計信息
granularity => 'SUBPARTITION' 收集子分區(qū)統計信息
當然我們可以指定partname，自己控制對哪個分區(qū)收集統計信息
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;
/
下面的是分區(qū)表
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版本才解決這個問題，臨時解決方案是手動關閉動態(tài)采樣。
順便貼上10個level的動態(tài)采樣介紹
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.
