收集oracle統計資訊
收集oracle統計資訊
最佳化器統計範圍：
表統計； --行數，塊數，行平均長度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
列統計； --列中唯一值的數量（NDV），NULL值的數量，資料分佈；
--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
索引統計；--葉塊數量，等級，聚簇因子；
--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；
系統統計；--I/O效能與使用率；
--CPU效能與使用率；
--儲存在aux_stats$中，需要使用dbms_stats收集，I/O統計在X$KCFIO中；
-------------
analyze
-------------
需要使用ANALYZE統計的統計：
使用LIST CHAINED ROWS和VALIDATE子句；
收集空閒列表塊的統計；
Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
ANALYZE 不適合做分割槽表的分析
----------------------
dbms_stats
----------------------
dbms_stats能良好地估計統計資料（尤其是針對較大的分割槽表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計劃。
這個包的下面四個儲存過程分別收集index、table、schema、database的統計資訊:
dbms_stats.gather_table_stats 收集表、列和索引的統計資訊；
dbms_stats.gather_schema_stats 收集SCHEMA下所有物件的統計資訊；
dbms_stats.gather_index_stats 收集索引的統計資訊；
dbms_stats.gather_system_stats 收集系統統計資訊
dbms_stats.GATHER_DICTIONARY_STATS： 所有字典物件的統計；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系統模式的統計
dbms_stats.delete_table_stats 刪除表的統計資訊
dbms_stats.delete_index_stats 刪除索引的統計資訊
dbms_stats.export_table_stats 輸出表的統計資訊
dbms_stats.create_state_table
dbms_stats.set_table_stats 設定表的統計
dbms_stats.auto_sample_size
統計收集的許可權
==========================
必須授予普通使用者許可權
> grant execute_catalog_role to hr;
> grant connect,resource,analyze any to hr;
統計收集的時間考慮
==========================
當引數STATISTICS_LEVEL設定為TYPICAL或者ALL，系統會在夜間自動收集統計資訊。
檢視系統自動收集統計資訊的job：
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';
也可以disable自動收集統計資訊：
BEGIN
dbms_scheduler.disable('GATHER_STATS_JOB');
END;
使用手工統計
對所有更改活動中等的物件自動統計應該足夠充分，由於自動統計收集在夜間進行，因此對於一些更新頻繁的物件其統計可能已經過期。兩種典型的物件：
高度變化的表在白天的活動期間被TRUNCATE/DROP並重建；
塊載入超過本身總大小10%的物件；
對於第一種物件可以使用以下兩種方法：
1 將這些表上的統計設定為NULL，當Oracle遇到沒有統計的表時，將動態收集必要的統計作為查詢最佳化的一部分；
動態收集特徵由OPTIMIZER_DYNAMIC_SAMPLING控制，這個引數應該設定為大於等於2，預設為2。可以透過刪除並鎖住統計將統計設定為NULL：
DBMS_STATS.DELETE_TABLE_STATS('SCHEMA','TABLE');
DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE');
2 將這些表上的統計設定為代表表典型狀態的值。在表具有某個有代表性的值時收集統計，然後鎖住統計；
由於夜間收集的統計未必適合於白天的負載，因此這些情況下使用手工收集比GATHER_STATS_JOB更有效。
對於塊載入，統計應該在載入後立刻收集，通常合併在載入語句的後面防止遺忘。
對於外部表，統計不能透過GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自動統計收集收集。因此需要使用GATHER_TABLE_STATS在單個表上收集統計，並且在外部表上不支援取樣，ESTIMATE_PERCENT應該被顯示設定為NULL。
如果STATISTICS_LEVEL設定為BASIC禁用了監控特徵，自動統計收集將不會檢測過期的統計，此時需要手工收集。
3 需要手工收集的另一個地方是系統統計，其不會自動收集。
對於固定表，如動態效能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，這些表上的統計應該在資料庫具有有代表性的活動後收集。
統計收集考慮
==========================
1 統計收集使用取樣
不使用抽樣的統計收集需要全表掃描並且排序整個表，抽樣最小化收集統計的必要資源。
Oracle推薦設定DBMS_STATS的ESTIMATE_PERCENT引數為DBMS_STATS.AUTO_SAMPLE_SIZE在達到必要的統計精確性的同時最大化效能。
2 並行統計收集
Oracle推薦設定DBMS_STATS的DEGREE引數為DBMS_STATS.AUTO_DEGREE，該引數允許Oracle根據物件的大小和並行性初始化引數的設定選擇恰當的並行度。
聚簇索引，域索引，點陣圖連線索引不能並行收集。
3 分割槽物件的統計收集
對於分割槽表和索引，DBMS_STATS可以收集單獨分割槽的統計和全域性分割槽，對於組合分割槽，可以收集子分割槽，分割槽，表/索引上的統計，分割槽統計的收集可以透過宣告引數GRANULARITY。根據將最佳化的SQL語句，最佳化器可以選擇使用分割槽統計或全域性統計，對於大多數系統這兩種統計都是很重要的，Oracle推薦將GRANULARITY設定為AUTO同時收集全部資訊。
4 列統計和直方圖
當在表上收集統計時，DBMS_STATS收集表中列的資料分佈的資訊，資料分佈最基本的資訊是最大值和最小值，但是如果資料分佈是傾斜的，這種級別的統計對於最佳化器來說不夠的，對於傾斜的資料分佈，直方圖通常用來作為列統計的一部分。
直方圖透過METHOD_OPT引數宣告，Oracle推薦設定METHOD_OPT為FOR ALL COLUMNS SIZE AUTO，使用該值時Oracle自動決定需要直方圖的列以及每個直方圖的桶數。也可以手工設定需要直方圖的列以及桶數。
如果在使用DBMS_STATS的時候需要刪除表中的所有行，需要使用TRUNCATE代替drop/create，否則自動統計收集特徵使用的負載資訊以及RESTORE_*_STATS使用的儲存的統計歷史將丟失。這些特徵將無法正常發揮作用。
5 確定過期的統計
對於那些隨著時間更改的物件必須週期性收集統計，為了確定過期的統計，Oracle提供了一個表監控這些更改，這些監控預設情況下在STATISTICS_LEVEL為TYPICAL/ALL時啟用，該表為USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映記憶體中超過監控的資訊。在OPTIONS引數設定為GATHER STALE or GATHER AUTO時，DBMS_STATS收集過期統計的物件的統計。
6 使用者定義統計
在建立了基於索引的統計後，應該在表上收集新的列統計，這可以透過呼叫過程設定METHOD_OPT的FOR ALL HIDDEN COLUMNS。
7 何時收集統計
對於增量更改的表，可能每個月/每週只需要收集一次，而對於載入後表，通常在載入指令碼中增加收集統計的指令碼。對於分割槽表，如果僅僅是一個分割槽有了較大改動，只需要收集一個分割槽的統計，但是收集整個表的分割槽也是必要的。
系統統計
==========================
系統統計描述系統硬體的特徵，包括I/O和CPU。在選擇執行計劃時，最佳化器考慮查詢所需的CPU和I/O代價。系統統計允許最佳化器更加精確的評價CPU和IO代價，選擇更好的查詢計劃。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系統統計，Oracle推薦收集系統統計。收集系統統計需要DBA許可權。
收集的最佳化器系統統計包括：
cpuspeedNW：代表無負載CPU速度，CPU速度為每秒鐘CPU週期數；透過設定gathering_mode = NOWORKLOAD或手工設定統計；單位Millions/sec。
ioseektim：I/O查詢時間=查詢時間+延遲時間+OS負載時間；透過設定gathering_mode = NOWORKLOAD或手工設定統計；單位為ms。
Iotfrspeed：I/O傳輸速度；透過設定gathering_mode = NOWORKLOAD或手工設定統計；單位為Bytes/ms.
Cpuspeed：代表有負載CPU速度，CPU速度為每秒鐘CPU週期數；透過設定gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設定統計；單位Millions/sec。
Maxthr：最大I/O吞吐量；透過設定gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設定統計；單位Bytes/sec.
Slavethr：服務I/O吞吐量是平均並行服務I/O吞吐量；透過設定gathering_mode = INTERVAL，START|STOP或手工設定統計；Bytes/sec.
Sreadtim：隨機讀取單塊的平均時間；透過設定gathering_mode =INTERVAL，START|STOP或手工設定統計；單位為ms。
Mreadtim：順序讀取多塊的平均時間，透過設定透過設定gathering_mode = INTERVAL，START|STOP或手工設定統計；單位為ms。
Mbrc: 多塊讀平均每次讀取的塊數量；透過設定透過設定gathering_mode = INTERVAL，START|STOP或手工設定統計；單位為blocks。
系統統計的重新收集不會導致當前的SQL無效，只是所有的新SQL語句使用新的統計。
Oracle提供兩個選項收集統計：負載統計；非負載統計。
負載統計
==========================
在負載視窗的開始執行dbms_stats.gather_system_stats(’start’)，然後執行dbms_stats.gather_system_stats(’stop’)結束負載視窗。
執行dbms_stats.gather_system_stats(’interval’, interval=>N)，N表示N分鐘後系統統計收集結束。
執行dbms_stats.delete_system_stats()刪除負載統計。
非負載統計
==========================
執行不帶引數的dbms_stats.gather_system_stats()收集非負載統計，執行非負載統計時會有一定的I/O負載。在某些情況下，非負載統計的值可能會保持預設，此時需要使用dbms_stats.set_system_stats設定。
管理統計
==========================
轉儲先前版本的統計
使用RESTORE過程轉儲先前版本的統計，這些過程使用一個時間戳作為引數，包含統計時間的檢視包括：
1 DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系統級別執行的統計操作；
2 *_TAB_STATS_HISTORY：包含了表統計更改的歷史。
舊的統計定期重新整理，根據DBMS_STATS的ALTER_STATS_HISTORY_RETENTION過程設定而定，預設為31天。
預設情況下，如果STATISTICS_LEVEL為TYPICAL/ALL，自動重新整理啟用；否則需要使用PURGE_STAT手工重新整理。
其他轉儲與重新整理相關的資訊包括：
PURGE_STATS： 手工重新整理超過某個時間戳的舊統計；
GET_STATS_HISTORY_RENTENTION： 得到當前歷史統計保留值；
GET_STATS_HISTORY_AVAILABILTY： 得到可用的最舊的統計的時間戳。
轉儲的限制：
1 不能轉儲使用者定義統計；
2 如果使用了ANALYZE收集，舊的統計將無法轉儲。
匯入/匯出統計
==========================
匯出統計前需要使用DBMS_STATS.CREATE_STAT_TABLE建立一個統計表保留統計，在表建立後可以使用DBMS_STATS.EXPORT_*_STATS匯出統計到自定義表，這些統計可以使用DBMS_STATS.IMPORT_*_STATS重新匯入。
也可以使用IMP/EXP導到其他資料庫。
轉儲統計與匯入匯出統計
使用轉儲的情況：
1 恢復舊版本的統計；
2 希望資料庫管理統計歷史的保留和重新整理；
使用EXPORT/IMPORT_*_STATS的情況：
1 實驗各種值的不同情況；
2 移動統計到不同資料庫；
3 保留統計資料更長的時間。
鎖住表和模式的統計
==========================
一旦統計被鎖住，將無法在更改這些統計直到被解鎖。DBMS_STAT提供兩個過程用於解鎖，兩個用於加鎖：
1 LOCK_SCHEMA_STATS；?¤LOCK_TABLE_STATS；
2 UNLOCK_SCHEMA_STATS；?¤UNLOCK_TABLE_STATS；
設定統計
==========================
可以使用SET_*_STATISTICS設定表，索引，列，系統統計。
使用動態取樣評價統計
==========================
動態取樣的目的是透過為謂詞選擇性和表/索引統計確定更加精確的估計提高伺服器效能，估計越精確產生的效能更好。
可以使用動態取樣的情況：
1 在收集的統計不能使用或會導致嚴重的估計錯誤時估計單表的謂詞選擇性；
2 估計沒有統計的表/索引的統計；
3 估計統計過期的表和索引的統計；
動態取樣特徵由引數OPTIMIZER_DYNAMIC_SAMPLING控制，預設級別為2。
動態取樣的工作機制
主要的效能特徵是編譯時，Oracle在編譯時決定一個查詢是否能透過取樣獲益，如果可以，將用遞迴SQL隨機掃描一小部分表塊，然後應用相關的單表謂詞評價謂詞選擇性。
使用動態取樣的時間
使用動態取樣將獲益的情況：
1 可以發現更好的執行計劃；
2 取樣時間僅佔總時間的一小部分；
3 查詢將執行多次；
取樣級別
==========================
範圍從1..10
缺失統計處理
==========================
當Oracle遇到丟失統計時，最佳化器動態必要的統計。在某些情況下，Oracle無法執行動態取樣，包括：遠端表/外部表，此時將使用預設統計。
缺失統計時的表預設值：
1 Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
2 Average row length：100位元組；
3 Number of blocks：100或基於分割槽對映的實際值；
4 Remote cardinality：2000行；
5 Remote average row length：100位元組；
缺失統計時的索引預設值：
Levels：1
Leaf blocks：25
Leaf blocks/key：1
Data blocks/key：1
Distinct keys：100
Clustering factor：800
gather_schema_stats
==========================
begin
dbms_stats.gather_schema_stats( wnname => 'SCOTT',
ptions => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15 );
end;
options引數使用4個預設的方法：
gather——重新分析整個架構（Schema）。
gather empty——只分析目前還沒有統計的表。
gather stale——只重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）。
gather auto——重新分析當前沒有統計的物件，以及統計資料過期（變髒）的物件。類似於組合使用gather stale和gather empty。
注意，無論gather stale還是gather auto，都要求進行監視。
如果你執行一個alter table xxx monitoring命令，Oracle會用dba_tab_modifications檢視來跟蹤發生變動的表。
這樣一來，你就確切地知道，自從上一次分析統計資料以來，發生了多少次插入、更新和刪除操作。
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'SCOTT';
使用alter table xxx monitoring命令來實現Oracle表監視時，需要使用dbms_stats中的auto選項。
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;
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%的行中，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。
--*************************************************************
-- 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選項時，只會為現有的直方圖重新分析索引，不再搜尋其他直方圖機會。定期重新分析統計資料時，你應該採取這種方式。
--**************************************************************
-- 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;
Oracle中關於表的統計資訊是在資料字典中的，可以下SQL查詢到:
SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE wner = 'SCOTT' ;
這是對命令與工具包的一些總結
1、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。
a) 可以並行進行，對多個使用者，多個Table
b) 可以得到整個分割槽表的資料和單個分割槽的資料。
c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽 ,但不收集聚簇統計
d) 可以倒出統計資訊
e) 可以使用者自動收集統計資訊
2、DBMS_STATS的缺點
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的資訊，這兩個仍舊需要使用Analyze語句。
c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True
3、對於External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。
GATHER_TABLE_STATS
==========================
DBMS_STATS.gather_table_stats
(ownname varchar2,
tabname varchar2,
partname varchar2 default null,
estimate_percent number default to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample boolean default FALSE,
method_opt varchar2 default get_param('METHOD_OPT'),
degree number default to_degree_type(get_param('DEGREE')),
granularity varchar2 default get_param('GRANULARITY'),
cascade boolean default to_cascade_type(get_param('CASCADE')),
stattab varchar2 default null, statid varchar2 default null,
statown varchar2 default null,
no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype varchar2 default 'DATA',
force boolean default FALSE);
引數說明:
ownname: 要分析表的擁有者
tabname: 要分析的表名.
partname: 分割槽的名字,只對分割槽表或分割槽索引有用.
estimate_percent:取樣行的百分比,取值範圍[0.000001,100],null為全部分析,不取樣. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是預設值,由oracle決定最佳取取樣值.
block_sapmple:是否用塊取樣代替行取樣.
method_opt: 決定histograms資訊是怎樣被統計的.method_opt的取值如下:
for all columns:統計所有列的histograms.
for all indexed columns:統計所有indexed列的histograms.
for all hidden columns:統計你看不到列的histograms
for columns SIZE
統計指定列的histograms.N的取值範圍[1,254]; R
EPEAT上次統計過的histograms;
AUTO由oracle決定N的大小;
SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data
degree: 設定收集統計資訊的並行度.預設值為null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascade: 是收集索引的資訊.預設為falase.
stattab 指定要儲存統計資訊的表,statid如果多個表的統計資訊儲存在同一個stattab中用於進行區分.statown儲存統計資訊表的擁有者.以上三個引數若不指定,統計資訊會直接更新到資料字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force: 即使表鎖住了也收集統計資訊
例子:
execute dbms_stats.gather_table_stats(ownname => 'owner',
tabname => 'table_name' ,
estimate_percent => null ,
method_opt => 'for all indexed columns' ,
cascade => true);
GATHER_INDEX_STATS
==========================
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => 'ABC',
IndName => 'IDX_FUNC_ABC',
Estimate_Percent => 10,
Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
No_Invalidate => FALSE);
END;
---------------------------------------
10g自動收集統計資訊
---------------------------------------
從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：
SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;
JOB_NAME LAST_START_DATE
------------------------------ ------------------------------------
AUTO_SPACE_ADVISOR_JOB 30-OCT-08 10.00.01.463000 PM +08:00
GATHER_STATS_JOB 30-OCT-08 10.00.01.463000 PM +08:00
然而這個自動化功能已經影響了很多系統的正常執行，晚上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;
---------------------------------------
檢視統計
---------------------------------------
表/索引/列上的統計
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
---------------------------------------
直方圖統計
---------------------------------------
直方圖的型別儲存在*TAB_COL_STATISTICS檢視的HISTOGRAM列上。
------------------------------------------------------------------------------
bde_last_analyzed.sql - Verifies CBO Statistics
------------------------------------------------------------------------------
bde_last_analyzed.sql verifies the CBO statistics in the data dictionary for all tables, indexes, and partitions. It also validates the statistics on tables and indexes owned by 'SYS'.
The 5 generated reports bde_last_analyzed_xxx.html, present the total of tables and indexes analyzed per module and per date.
Script. bde_last_analyzed.sql provided in this Note can be used on any 8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12 instances
如果是ERP資料庫,則用APPS連線，否則用其他任何SYS許可權使用者連線都可以
#sqlplus
SQL> START bde_last_analyzed.sql
Review spool output files bde_last_analyzed_xxx.html files. Spool files get created on same directory from which this script. is executed. On NT, files may get created under $ORACLE_HOME/bin.
If some modules have not been analyzed, or they have but not recently, these Apps objects must be analyzed using FND_STATS or coe_stats.sql if belonging to Oracle Apps. Otherwise use DBMS_STATS.
If Oracle Apps, use corresponding concurrent program with an estimate of 10%, or execute equivalent FND_STATS procedure from SQL*Plus:
SQL> exec fnd_stats.gather_schema_statistics('APPLSYS'); Where 'APPLSYS' is the module (schema) that requires new statistics.
If only a few tables require to have their statistics gathered, use the corresponding concurrent program to gather stats by table, or execute equivalent FND_STATS procedure from SQL*Plus:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');
Where 'MRP' is the schema owner, and 'MRP_FORECAST_DATES' is the table name. This syntax is only for non-partitioned Tables.
If any Partitioned Table requires its Global Stats being rebuilt, it is because at some point you gathered Stats on the table using a granularity of PARTITION. See second method below:
begin
dbms_stats.delete_table_stats(ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES');
fnd_stats.gather_table_stats (ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES',
granularity => 'DEFAULT');
end;
/
Once you fix your stats, be sure to ALWAYS use the granularity of DEFAULT for partitioned tables.
If you want to execute this bde_last_analyzed.sql script. against only one schema, modify DEF SCHEMA code line.
---------------------------------------
分割槽表的統計資訊例項
---------------------------------------
ORATEA ORACLE的統計資訊在執行SQL的過程中扮演著非常重要的作用，而且ORACLE在表的各個層次都會有不同的統計資訊，透過這些統計資訊來描述表的，列的各種各樣的統計資訊。下面透過一個複合分割槽表來說明一些常見的和常見的統計資訊。
SQL>
create table test
partition by range(object_id)
subpartition by hash(object_type) subpartitions 4
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(maxvalue))
as
select * from dba_objects;
表已建立。
sql>
BEGIN
dbms_stats.gather_table_stats(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
block_sample => FALSE,
method_opt => 'FOR ALL COLUMNS SIZE 10',
granularity => 'ALL',
cascade => TRUE);
END;
1,表級的統計資訊
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 'TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
TEST 50705 788 0 0
2,表上列的統計資訊
SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 'TEST';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------------------------ ------------ ----------
TEST OWNER 25 .365014295
TEST OBJECT_NAME 30275 .000039205
TEST SUBOBJECT_NAME 191 .015657993
TEST OBJECT_ID 50705 .000019722
TEST DATA_OBJECT_ID 4334 .000248075
TEST OBJECT_TYPE 42 .271207855
TEST CREATED 2305 .001608457
TEST LAST_DDL_TIME 2369 .001566737
TEST TIMESTAMP 2412 .001610251
TEST STATUS 2 .000009861
TEST TEMPORARY 2 .000009861
TEST GENERATED 2 .000009861
TEST SECONDARY 2 .000009861
13 rows selected.
3,表上列的直方圖資訊
SQL>
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
TEST OBJECT_ID 0 2
TEST OBJECT_ID 1 5160
TEST OBJECT_ID 2 10587
TEST OBJECT_ID 3 15658
TEST OBJECT_ID 4 20729
TEST OBJECT_ID 5 25800
TEST OBJECT_ID 6 30870
TEST OBJECT_ID 7 35940
TEST OBJECT_ID 8 41089
TEST OBJECT_ID 9 46821
TEST OBJECT_ID 10 53497
4,分割槽的統計資訊
SQL>
select partition_name,num_rows,blocks,empty_blocks,avg_space
from user_tab_partitions
where table_name = 'TEST';
PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
--------------- ---------- ---------- ------------ ----------
P1 9581 140 0 0
P2 9973 164 0 0
P3 10000 158 0 0
P4 21151 326 0 0
5,分割槽上列的統計資訊
SQL> select column_name,num_distinct,density,num_nulls
from user_part_col_statistics
where table_name = 'TEST'
and partition_name = 'P1';
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
--------------- ------------ ---------- ----------
OWNER 7 .000052187 0
OBJECT_NAME 7412 .000156925 0
SUBOBJECT_NAME 26 .47017301 9496
OBJECT_ID 9581 .000104373 0
DATA_OBJECT_ID 1765 .000664385 7780
OBJECT_TYPE 34 .18494854 0
CREATED 913 .001977449 0
LAST_DDL_TIME 994 .001882695 0
TIMESTAMP 982 .001928775 0
STATUS 2 .000052187 0
TEMPORARY 2 .000052187 0
GENERATED 2 .000052187 0
SECONDARY 1 .000052187 0
6,分割槽上列的直方圖資訊
SQL> select column_name,bucket_number,endpoint_value
from user_part_histograms
where table_name = 'TEST'
and partition_name = 'P1'
and column_name = 'OBJECT_ID';
COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID 0 2
OBJECT_ID 1 1005
OBJECT_ID 2 1963
OBJECT_ID 3 2921
OBJECT_ID 4 3888
OBJECT_ID 5 4859
OBJECT_ID 6 5941
OBJECT_ID 7 6899
OBJECT_ID 8 7885
OBJECT_ID 9 8864
OBJECT_ID 10 9999
7,子分割槽的統計資訊
SQL> select subpartition_name,num_rows,blocks,empty_blocks
from user_tab_subpartitions
where table_name = 'TEST'
and partition_name = 'P1';
SUBPARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
SYS_SUBP21 3597 50 0
SYS_SUBP22 3566 52 0
SYS_SUBP23 637 11 0
SYS_SUBP24 1781 27 0
8,子分割槽上的列的統計資訊
SQL> select column_name,num_distinct,density
from user_subpart_col_statistics
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21';
COLUMN_NAME NUM_DISTINCT DENSITY
--------------- ------------ ----------
OWNER 6 .000139005
OBJECT_NAME 3595 .000278319
SUBOBJECT_NAME 4 .014285714
OBJECT_ID 3597 .000278009
DATA_OBJECT_ID 155 .006451613
OBJECT_TYPE 8 .000139005
CREATED 751 .002392334
LAST_DDL_TIME 784 .002302524
TIMESTAMP 768 .00235539
STATUS 1 .000139005
TEMPORARY 2 .000139005
GENERATED 2 .000139005
SECONDARY 1 .000139005
9,子分割槽上的列的直方圖資訊
SQL> select column_name,bucket_number,endpoint_value
from user_subpart_histograms
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21'
and column_name = 'OBJECT_ID';
COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID 0 208
OBJECT_ID 1 1525
OBJECT_ID 2 2244
OBJECT_ID 3 2892
OBJECT_ID 4 3252
OBJECT_ID 5 4047
OBJECT_ID 6 5238
OBJECT_ID 7 6531
OBJECT_ID 8 7661
OBJECT_ID 9 8474
OBJECT_ID 10 9998
我們對這個複合分割槽分析之後產生了上面這九種不同層次的統計資訊。CBO想要得要一個高效的執行計劃需要如此多的統計資訊.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28869493/viewspace-1134463/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- ORACLE 收集統計資訊Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- Oracle統計資訊自動收集Oracle
- oracle收集統計資訊job停止Oracle
- oracle 統計資訊檢視與收集Oracle
- oracle 11g統計資訊收集Oracle
- ORACLE 統計資訊的收集與管理Oracle
- [Oracle] Oracle收集統計資訊的取樣比例Oracle
- Oracle運維指令碼-收集統計資訊Oracle運維指令碼
- oracle統計資訊--直方圖的收集：Oracle直方圖
- 重新收集oracle表的統計資訊Oracle
- 關於oracle自動收集統計資訊Oracle
- Oracle統計資訊的收集和維護Oracle
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- Oracle 11g手工收集表統計資訊Oracle
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- ORACLE 11g 自動收集統計資訊Oracle
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- oracle統計資訊 使用dbms_stats包收集統計資料Oracle
- Oracle收集統計資訊之NO_INVALIDATE引數Oracle
- Oracle 11g 之自動收集統計資訊Oracle
- Oracle檢視查詢慢之統計資訊收集Oracle
- Oracle10g 統計資訊的自動收集Oracle
- ORACLE10g自動收集CBO統計資訊Oracle
- 收集全庫統計資訊
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- [zt] Oracle10g 自動收集收集CBO統計資訊設定Oracle
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- MySQL系統如何收集統計資訊MySql
- ORACLE19c新特性-實時統計資訊收集Oracle
- Oracle 11g無法自動收集統計資訊Oracle