|
優(yōu)化器統(tǒng)計(jì)范圍：
表統(tǒng)計(jì)； --行數(shù)，塊數(shù)，行平均長(zhǎng)度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
列統(tǒng)計(jì)； --列中唯一值的數(shù)量（NDV），NULL值的數(shù)量，數(shù)據(jù)分布；
--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
索引統(tǒng)計(jì)；--葉塊數(shù)量，等級(jí)，聚簇因子；
--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；
系統(tǒng)統(tǒng)計(jì)；--I/O性能與使用率；
--CPU性能與使用率；
--存儲(chǔ)在aux_stats$中，需要使用dbms_stats收集，I/O統(tǒng)計(jì)在X$KCFIO中；
-------------
analyze
-------------
需要使用ANALYZE統(tǒng)計(jì)的統(tǒng)計(jì)：
使用LIST CHAINED ROWS和VALIDATE子句；
收集空閑列表塊的統(tǒng)計(jì)；
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 不適合做分區(qū)表的分析
----------------------
dbms_stats
----------------------
dbms_stats能良好地估計(jì)統(tǒng)計(jì)數(shù)據(jù)（尤其是針對(duì)較大的分區(qū)表），并能獲得更好的統(tǒng)計(jì)結(jié)果，最終制定出速度更快的SQL執(zhí)行計(jì)劃。
這個(gè)包的下面四個(gè)存儲(chǔ)過(guò)程分別收集index、table、schema、database的統(tǒng)計(jì)信息:
dbms_stats.gather_table_stats 收集表、列和索引的統(tǒng)計(jì)信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有對(duì)象的統(tǒng)計(jì)信息；
dbms_stats.gather_index_stats 收集索引的統(tǒng)計(jì)信息；
dbms_stats.gather_system_stats 收集系統(tǒng)統(tǒng)計(jì)信息
dbms_stats.GATHER_DICTIONARY_STATS： 所有字典對(duì)象的統(tǒng)計(jì)；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系統(tǒng)模式的統(tǒng)計(jì)
dbms_stats.delete_table_stats 刪除表的統(tǒng)計(jì)信息
dbms_stats.delete_index_stats 刪除索引的統(tǒng)計(jì)信息
dbms_stats.export_table_stats 輸出表的統(tǒng)計(jì)信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 設(shè)置表的統(tǒng)計(jì)
dbms_stats.auto_sample_size
統(tǒng)計(jì)收集的權(quán)限
==========================
必須授予普通用戶權(quán)限
sys@ORADB> grant execute_catalog_role to hr;
sys@ORADB> grant connect,resource,analyze any to hr;
統(tǒng)計(jì)收集的時(shí)間考慮
==========================
當(dāng)參數(shù)STATISTICS_LEVEL設(shè)置為TYPICAL或者ALL，系統(tǒng)會(huì)在夜間自動(dòng)收集統(tǒng)計(jì)信息。
查看系統(tǒng)自動(dòng)收集統(tǒng)計(jì)信息的job：
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';
也可以disable自動(dòng)收集統(tǒng)計(jì)信息：
BEGIN
dbms_scheduler.disable('GATHER_STATS_JOB');
END;
使用手工統(tǒng)計(jì)
對(duì)所有更改活動(dòng)中等的對(duì)象自動(dòng)統(tǒng)計(jì)應(yīng)該足夠充分，由于自動(dòng)統(tǒng)計(jì)收集在夜間進(jìn)行，因此對(duì)于一些更新頻繁的對(duì)象其統(tǒng)計(jì)可能已經(jīng)過(guò)期。兩種典型的對(duì)象：
高度變化的表在白天的活動(dòng)期間被TRUNCATE/DROP并重建；
塊加載超過(guò)本身總大小10%的對(duì)象；
對(duì)于第一種對(duì)象可以使用以下兩種方法：
1 將這些表上的統(tǒng)計(jì)設(shè)置為NULL，當(dāng)Oracle遇到?jīng)]有統(tǒng)計(jì)的表時(shí)，將動(dòng)態(tài)收集必要的統(tǒng)計(jì)作為查詢優(yōu)化的一部分；
動(dòng)態(tài)收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，這個(gè)參數(shù)應(yīng)該設(shè)置為大于等于2，默認(rèn)為2。可以通過(guò)刪除并鎖住統(tǒng)計(jì)將統(tǒng)計(jì)設(shè)置為NULL：
DBMS_STATS.DELETE_TABLE_STATS('SCHEMA','TABLE');
DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE');
2 將這些表上的統(tǒng)計(jì)設(shè)置為代表表典型狀態(tài)的值。在表具有某個(gè)有代表性的值時(shí)收集統(tǒng)計(jì)，然后鎖住統(tǒng)計(jì)；
由于夜間收集的統(tǒng)計(jì)未必適合于白天的負(fù)載，因此這些情況下使用手工收集比GATHER_STATS_JOB更有效。
對(duì)于塊加載，統(tǒng)計(jì)應(yīng)該在加載后立刻收集，通常合并在加載語(yǔ)句的后面防止遺忘。
對(duì)于外部表，統(tǒng)計(jì)不能通過(guò)GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自動(dòng)統(tǒng)計(jì)收集收集。因此需要使用GATHER_TABLE_STATS在單個(gè)表上收集統(tǒng)計(jì)，并且在外部表上不支持取樣，ESTIMATE_PERCENT應(yīng)該被顯示設(shè)置為NULL。
如果STATISTICS_LEVEL設(shè)置為BASIC禁用了監(jiān)控特征，自動(dòng)統(tǒng)計(jì)收集將不會(huì)檢測(cè)過(guò)期的統(tǒng)計(jì)，此時(shí)需要手工收集。
3 需要手工收集的另一個(gè)地方是系統(tǒng)統(tǒng)計(jì)，其不會(huì)自動(dòng)收集。
對(duì)于固定表，如動(dòng)態(tài)性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，這些表上的統(tǒng)計(jì)應(yīng)該在數(shù)據(jù)庫(kù)具有有代表性的活動(dòng)后收集。
統(tǒng)計(jì)收集考慮
==========================
1 統(tǒng)計(jì)收集使用取樣
不使用抽樣的統(tǒng)計(jì)收集需要全表掃描并且排序整個(gè)表，抽樣最小化收集統(tǒng)計(jì)的必要資源。
Oracle推薦設(shè)置DBMS_STATS的ESTIMATE_PERCENT參數(shù)為DBMS_STATS.AUTO_SAMPLE_SIZE在達(dá)到必要的統(tǒng)計(jì)精確性的同時(shí)最大化性能。
2 并行統(tǒng)計(jì)收集
Oracle推薦設(shè)置DBMS_STATS的DEGREE參數(shù)為DBMS_STATS.AUTO_DEGREE，該參數(shù)允許Oracle根據(jù)對(duì)象的大小和并行性初始化參數(shù)的設(shè)置選擇恰當(dāng)?shù)牟⑿卸取?br>聚簇索引，域索引，位圖連接索引不能并行收集。
3 分區(qū)對(duì)象的統(tǒng)計(jì)收集
對(duì)于分區(qū)表和索引，DBMS_STATS可以收集單獨(dú)分區(qū)的統(tǒng)計(jì)和全局分區(qū)，對(duì)于組合分區(qū)，可以收集子分區(qū)，分區(qū)，表/索引上的統(tǒng)計(jì)，分區(qū)統(tǒng)計(jì)的收集可以通過(guò)聲明參數(shù)GRANULARITY。根據(jù)將優(yōu)化的SQL語(yǔ)句，優(yōu)化器可以選擇使用分區(qū)統(tǒng)計(jì)或全局統(tǒng)計(jì)，對(duì)于大多數(shù)系統(tǒng)這兩種統(tǒng)計(jì)都是很重要的，Oracle推薦將GRANULARITY設(shè)置為AUTO同時(shí)收集全部信息。
4 列統(tǒng)計(jì)和直方圖
當(dāng)在表上收集統(tǒng)計(jì)時(shí)，DBMS_STATS收集表中列的數(shù)據(jù)分布的信息，數(shù)據(jù)分布最基本的信息是最大值和最小值，但是如果數(shù)據(jù)分布是傾斜的，這種級(jí)別的統(tǒng)計(jì)對(duì)于優(yōu)化器來(lái)說(shuō)不夠的，對(duì)于傾斜的數(shù)據(jù)分布，直方圖通常用來(lái)作為列統(tǒng)計(jì)的一部分。
直方圖通過(guò)METHOD_OPT參數(shù)聲明，Oracle推薦設(shè)置METHOD_OPT為FOR ALL COLUMNS SIZE AUTO，使用該值時(shí)Oracle自動(dòng)決定需要直方圖的列以及每個(gè)直方圖的桶數(shù)。也可以手工設(shè)置需要直方圖的列以及桶數(shù)。
如果在使用DBMS_STATS的時(shí)候需要?jiǎng)h除表中的所有行，需要使用TRUNCATE代替drop/create，否則自動(dòng)統(tǒng)計(jì)收集特征使用的負(fù)載信息以及RESTORE_*_STATS使用的保存的統(tǒng)計(jì)歷史將丟失。這些特征將無(wú)法正常發(fā)揮作用。
5 確定過(guò)期的統(tǒng)計(jì)
對(duì)于那些隨著時(shí)間更改的對(duì)象必須周期性收集統(tǒng)計(jì)，為了確定過(guò)期的統(tǒng)計(jì)，Oracle提供了一個(gè)表監(jiān)控這些更改，這些監(jiān)控默認(rèn)情況下在STATISTICS_LEVEL為TYPICAL/ALL時(shí)啟用，該表為USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映內(nèi)存中超過(guò)監(jiān)控的信息。在OPTIONS參數(shù)設(shè)置為GATHER STALE or GATHER AUTO時(shí)，DBMS_STATS收集過(guò)期統(tǒng)計(jì)的對(duì)象的統(tǒng)計(jì)。
6 用戶定義統(tǒng)計(jì)
在創(chuàng)建了基于索引的統(tǒng)計(jì)后，應(yīng)該在表上收集新的列統(tǒng)計(jì)，這可以通過(guò)調(diào)用過(guò)程設(shè)置METHOD_OPT的FOR ALL HIDDEN COLUMNS。
7 何時(shí)收集統(tǒng)計(jì)
對(duì)于增量更改的表，可能每個(gè)月/每周只需要收集一次，而對(duì)于加載后表，通常在加載腳本中增加收集統(tǒng)計(jì)的腳本。對(duì)于分區(qū)表，如果僅僅是一個(gè)分區(qū)有了較大改動(dòng)，只需要收集一個(gè)分區(qū)的統(tǒng)計(jì)，但是收集整個(gè)表的分區(qū)也是必要的。
系統(tǒng)統(tǒng)計(jì)
==========================
系統(tǒng)統(tǒng)計(jì)描述系統(tǒng)硬件的特征，包括I/O和CPU。在選擇執(zhí)行計(jì)劃時(shí)，優(yōu)化器考慮查詢所需的CPU和I/O代價(jià)。系統(tǒng)統(tǒng)計(jì)允許優(yōu)化器更加精確的評(píng)價(jià)CPU和IO代價(jià)，選擇更好的查詢計(jì)劃。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系統(tǒng)統(tǒng)計(jì)，Oracle推薦收集系統(tǒng)統(tǒng)計(jì)。收集系統(tǒng)統(tǒng)計(jì)需要DBA權(quán)限。
收集的優(yōu)化器系統(tǒng)統(tǒng)計(jì)包括：
cpuspeedNW：代表無(wú)負(fù)載CPU速度，CPU速度為每秒鐘CPU周期數(shù)；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位Millions/sec。
ioseektim：I/O查找時(shí)間=查找時(shí)間+延遲時(shí)間+OS負(fù)載時(shí)間；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位為ms。
Iotfrspeed：I/O傳輸速度；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位為Bytes/ms.
Cpuspeed：代表有負(fù)載CPU速度，CPU速度為每秒鐘CPU周期數(shù)；通過(guò)設(shè)置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設(shè)置統(tǒng)計(jì)；單位Millions/sec。
Maxthr：最大I/O吞吐量；通過(guò)設(shè)置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設(shè)置統(tǒng)計(jì)；單位Bytes/sec.
Slavethr：服務(wù)I/O吞吐量是平均并行服務(wù)I/O吞吐量；通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；Bytes/sec.
Sreadtim：隨機(jī)讀取單塊的平均時(shí)間；通過(guò)設(shè)置gathering_mode =INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為ms。
Mreadtim：順序讀取多塊的平均時(shí)間，通過(guò)設(shè)置通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為ms。
Mbrc: 多塊讀平均每次讀取的塊數(shù)量；通過(guò)設(shè)置通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為blocks。
系統(tǒng)統(tǒng)計(jì)的重新收集不會(huì)導(dǎo)致當(dāng)前的SQL無(wú)效，只是所有的新SQL語(yǔ)句使用新的統(tǒng)計(jì)。
Oracle提供兩個(gè)選項(xiàng)收集統(tǒng)計(jì)：負(fù)載統(tǒng)計(jì)；非負(fù)載統(tǒng)計(jì)。
負(fù)載統(tǒng)計(jì)
==========================
在負(fù)載窗口的開(kāi)始運(yùn)行dbms_stats.gather_system_stats(’start’)，然后運(yùn)行dbms_stats.gather_system_stats(’stop’)結(jié)束負(fù)載窗口。
運(yùn)行dbms_stats.gather_system_stats(’interval’, interval=>N)，N表示N分鐘后系統(tǒng)統(tǒng)計(jì)收集結(jié)束。
運(yùn)行dbms_stats.delete_system_stats()刪除負(fù)載統(tǒng)計(jì)。
非負(fù)載統(tǒng)計(jì)
==========================
運(yùn)行不帶參數(shù)的dbms_stats.gather_system_stats()收集非負(fù)載統(tǒng)計(jì)，運(yùn)行非負(fù)載統(tǒng)計(jì)時(shí)會(huì)有一定的I/O負(fù)載。在某些情況下，非負(fù)載統(tǒng)計(jì)的值可能會(huì)保持默認(rèn)，此時(shí)需要使用dbms_stats.set_system_stats設(shè)置。
管理統(tǒng)計(jì)
==========================
轉(zhuǎn)儲(chǔ)先前版本的統(tǒng)計(jì)
使用RESTORE過(guò)程轉(zhuǎn)儲(chǔ)先前版本的統(tǒng)計(jì)，這些過(guò)程使用一個(gè)時(shí)間戳作為參數(shù)，包含統(tǒng)計(jì)時(shí)間的視圖包括：
1 DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系統(tǒng)級(jí)別執(zhí)行的統(tǒng)計(jì)操作；
2 *_TAB_STATS_HISTORY：包含了表統(tǒng)計(jì)更改的歷史。
舊的統(tǒng)計(jì)定期刷新，根據(jù)DBMS_STATS的ALTER_STATS_HISTORY_RETENTION過(guò)程設(shè)置而定，默認(rèn)為31天。
默認(rèn)情況下，如果STATISTICS_LEVEL為TYPICAL/ALL，自動(dòng)刷新啟用；否則需要使用PURGE_STAT手工刷新。
其他轉(zhuǎn)儲(chǔ)與刷新相關(guān)的信息包括：
PURGE_STATS： 手工刷新超過(guò)某個(gè)時(shí)間戳的舊統(tǒng)計(jì)；
GET_STATS_HISTORY_RENTENTION： 得到當(dāng)前歷史統(tǒng)計(jì)保留值；
GET_STATS_HISTORY_AVAILABILTY： 得到可用的最舊的統(tǒng)計(jì)的時(shí)間戳。
轉(zhuǎn)儲(chǔ)的限制：
1 不能轉(zhuǎn)儲(chǔ)用戶定義統(tǒng)計(jì)；
2 如果使用了ANALYZE收集，舊的統(tǒng)計(jì)將無(wú)法轉(zhuǎn)儲(chǔ)。
導(dǎo)入/導(dǎo)出統(tǒng)計(jì)
==========================
導(dǎo)出統(tǒng)計(jì)前需要使用DBMS_STATS.CREATE_STAT_TABLE創(chuàng)建一個(gè)統(tǒng)計(jì)表保留統(tǒng)計(jì)，在表創(chuàng)建后可以使用DBMS_STATS.EXPORT_*_STATS導(dǎo)出統(tǒng)計(jì)到自定義表，這些統(tǒng)計(jì)可以使用DBMS_STATS.IMPORT_*_STATS重新導(dǎo)入。
也可以使用IMP/EXP導(dǎo)到其他數(shù)據(jù)庫(kù)。
轉(zhuǎn)儲(chǔ)統(tǒng)計(jì)與導(dǎo)入導(dǎo)出統(tǒng)計(jì)
使用轉(zhuǎn)儲(chǔ)的情況：
1 恢復(fù)舊版本的統(tǒng)計(jì)；
2 希望數(shù)據(jù)庫(kù)管理統(tǒng)計(jì)歷史的保留和刷新；
使用EXPORT/IMPORT_*_STATS的情況：
1 實(shí)驗(yàn)各種值的不同情況；
2 移動(dòng)統(tǒng)計(jì)到不同數(shù)據(jù)庫(kù)；
3 保留統(tǒng)計(jì)數(shù)據(jù)更長(zhǎng)的時(shí)間。
鎖住表和模式的統(tǒng)計(jì)
==========================
一旦統(tǒng)計(jì)被鎖住，將無(wú)法在更改這些統(tǒng)計(jì)直到被解鎖。DBMS_STAT提供兩個(gè)過(guò)程用于解鎖，兩個(gè)用于加鎖：
1 LOCK_SCHEMA_STATS；?¤LOCK_TABLE_STATS；
2 UNLOCK_SCHEMA_STATS；?¤UNLOCK_TABLE_STATS；
設(shè)置統(tǒng)計(jì)
==========================
可以使用SET_*_STATISTICS設(shè)置表，索引，列，系統(tǒng)統(tǒng)計(jì)。
使用動(dòng)態(tài)取樣評(píng)價(jià)統(tǒng)計(jì)
==========================
動(dòng)態(tài)取樣的目的是通過(guò)為謂詞選擇性和表/索引統(tǒng)計(jì)確定更加精確的估計(jì)提高服務(wù)器性能，估計(jì)越精確產(chǎn)生的性能更好。
可以使用動(dòng)態(tài)取樣的情況：
1 在收集的統(tǒng)計(jì)不能使用或會(huì)導(dǎo)致嚴(yán)重的估計(jì)錯(cuò)誤時(shí)估計(jì)單表的謂詞選擇性；
2 估計(jì)沒(méi)有統(tǒng)計(jì)的表/索引的統(tǒng)計(jì)；
3 估計(jì)統(tǒng)計(jì)過(guò)期的表和索引的統(tǒng)計(jì)；
動(dòng)態(tài)取樣特征由參數(shù)OPTIMIZER_DYNAMIC_SAMPLING控制，默認(rèn)級(jí)別為2。
動(dòng)態(tài)取樣的工作機(jī)制
主要的性能特征是編譯時(shí)，Oracle在編譯時(shí)決定一個(gè)查詢是否能通過(guò)取樣獲益，如果可以，將用遞歸SQL隨機(jī)掃描一小部分表塊，然后應(yīng)用相關(guān)的單表謂詞評(píng)價(jià)謂詞選擇性。
使用動(dòng)態(tài)取樣的時(shí)間
使用動(dòng)態(tài)取樣將獲益的情況：
1 可以發(fā)現(xiàn)更好的執(zhí)行計(jì)劃；
2 取樣時(shí)間僅占總時(shí)間的一小部分；
3 查詢將執(zhí)行多次；
取樣級(jí)別
==========================
范圍從1..10
缺失統(tǒng)計(jì)處理
==========================
當(dāng)Oracle遇到丟失統(tǒng)計(jì)時(shí)，優(yōu)化器動(dòng)態(tài)必要的統(tǒng)計(jì)。在某些情況下，Oracle無(wú)法執(zhí)行動(dòng)態(tài)取樣，包括：遠(yuǎn)程表/外部表，此時(shí)將使用默認(rèn)統(tǒng)計(jì)。
缺失統(tǒng)計(jì)時(shí)的表默認(rèn)值：
1 Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
2 Average row length：100字節(jié)；
3 Number of blocks：100或基于分區(qū)映射的實(shí)際值；
4 Remote cardinality：2000行；
5 Remote average row length：100字節(jié)；
缺失統(tǒng)計(jì)時(shí)的索引默認(rèn)值：
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參數(shù)使用4個(gè)預(yù)設(shè)的方法：
gather——重新分析整個(gè)架構(gòu)（Schema）。
gather empty——只分析目前還沒(méi)有統(tǒng)計(jì)的表。
gather stale——只重新分析修改量超過(guò)10%的表（這些修改包括插入、更新和刪除）。
gather auto——重新分析當(dāng)前沒(méi)有統(tǒng)計(jì)的對(duì)象，以及統(tǒng)計(jì)數(shù)據(jù)過(guò)期（變臟）的對(duì)象。類似于組合使用gather stale和gather empty。
注意，無(wú)論gather stale還是gather auto，都要求進(jìn)行監(jiān)視。
如果你執(zhí)行一個(gè)alter table xxx monitoring命令，Oracle會(huì)用dba_tab_modifications視圖來(lái)跟蹤發(fā)生變動(dòng)的表。
這樣一來(lái)，你就確切地知道，自從上一次分析統(tǒng)計(jì)數(shù)據(jù)以來(lái)，發(fā)生了多少次插入、更新和刪除操作。
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'SCOTT';
使用alter table xxx monitoring命令來(lái)實(shí)現(xiàn)Oracle表監(jiān)視時(shí)，需要使用dbms_stats中的auto選項(xiàng)。
auto選項(xiàng)根據(jù)數(shù)據(jù)分布以及應(yīng)用程序訪問(wèn)列的方式（例如通過(guò)監(jiān)視而確定的一個(gè)列的工作量）
來(lái)創(chuàng)建直方圖。使用method_opt=>’auto’類似于在dbms_stats的option參數(shù)中使用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選項(xiàng)
以下estimate_percent參數(shù)是一種比較新的設(shè)計(jì)，它允許Oracle的dbms_stats在收集統(tǒng)計(jì)數(shù)據(jù)時(shí)，自動(dòng)估計(jì)要采樣的一個(gè)segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size
要驗(yàn)證自動(dòng)統(tǒng)計(jì)采樣的準(zhǔn)確性，你可檢視dba_tables sample_size列。一個(gè)有趣的地方是，在使用自動(dòng)采樣時(shí)，Oracle會(huì)為一個(gè)樣本尺寸選擇5到20的百分比。記住，統(tǒng)計(jì)數(shù)據(jù)質(zhì)量越好，CBO做出的決定越好。
method_opt選項(xiàng)
dbms_stats的method_opt參數(shù)尤其適合在表和索引數(shù)據(jù)發(fā)生變化時(shí)刷新統(tǒng)計(jì)數(shù)據(jù)。method_opt參數(shù)也適合用于判斷哪些列需要直方圖（histograms）。
某些情況下，索引內(nèi)的各個(gè)值的分布會(huì)影響CBO是使用一個(gè)索引還是執(zhí)行一次全表掃描的決策。例如，假如在where子句中指定的值的數(shù)量不對(duì)稱，全表掃描就顯得比索引訪問(wèn)更經(jīng)濟(jì)。
如果你有一個(gè)高度傾斜的索引（某些值的行數(shù)不對(duì)稱），就可創(chuàng)建Oracle直方圖統(tǒng)計(jì)。但在現(xiàn)實(shí)世界中，出現(xiàn)這種情況的機(jī)率相當(dāng)小。使用CBO時(shí)，最常見(jiàn)的錯(cuò)誤之一就是在CBO統(tǒng)計(jì)中不必要地引入直方圖。根據(jù)經(jīng)驗(yàn)，只有在列值要求必須修改執(zhí)行計(jì)劃時(shí)，才應(yīng)使用直方圖。
為了智能地生成直方圖，Oracle為dbms_stats準(zhǔn)備了method_opt參數(shù)。在method_opt子句中，還有一些重要的新選項(xiàng)，包括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選項(xiàng)會(huì)耗費(fèi)大量處理時(shí)間，因?yàn)樗獧z查每個(gè)索引中的每個(gè)列的值的分布情況。
假如dbms_stat發(fā)現(xiàn)一個(gè)索引的各個(gè)列分布得不均勻，就會(huì)為那個(gè)索引創(chuàng)建直方圖，幫助基于代價(jià)的SQL優(yōu)化器決定是進(jìn)行索引訪問(wèn)，還是進(jìn)行全表掃描訪問(wèn)。例如，在一個(gè)索引中，假定有一個(gè)列在50%的行中，那么為了檢索這些行，全表掃描的速度會(huì)快于索引掃描。
--*************************************************************
-- 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;
重新分析統(tǒng)計(jì)數(shù)據(jù)時(shí)，使用repeat選項(xiàng)，重新分析任務(wù)所消耗的資源就會(huì)少一些。使用repeat選項(xiàng)時(shí)，只會(huì)為現(xiàn)有的直方圖重新分析索引，不再搜索其他直方圖機(jī)會(huì)。定期重新分析統(tǒng)計(jì)數(shù)據(jù)時(shí)，你應(yīng)該采取這種方式。
--**************************************************************
-- 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中關(guān)于表的統(tǒng)計(jì)信息是在數(shù)據(jù)字典中的，可以下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' ;
這是對(duì)命令與工具包的一些總結(jié)
1、對(duì)于分區(qū)表，建議使用DBMS_STATS，而不是使用Analyze語(yǔ)句。
a) 可以并行進(jìn)行，對(duì)多個(gè)用戶，多個(gè)Table
b) 可以得到整個(gè)分區(qū)表的數(shù)據(jù)和單個(gè)分區(qū)的數(shù)據(jù)。
c) 可以在不同級(jí)別上Compute Statistics：?jiǎn)蝹€(gè)分區(qū)，子分區(qū)，全表，所有分區(qū) ,但不收集聚簇統(tǒng)計(jì)
d) 可以倒出統(tǒng)計(jì)信息
e) 可以用戶自動(dòng)收集統(tǒng)計(jì)信息
2、DBMS_STATS的缺點(diǎn)
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個(gè)仍舊需要使用Analyze語(yǔ)句。
c) DBMS_STATS 默認(rèn)不對(duì)索引進(jìn)行Analyze，因?yàn)槟J(rèn)Cascade是False，需要手工指定為True
3、對(duì)于External Table，Analyze不能使用，只能使用DBMS_STATS來(lái)收集信息。
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);
參數(shù)說(shuō)明:
ownname: 要分析表的擁有者
tabname: 要分析的表名.
partname: 分區(qū)的名字,只對(duì)分區(qū)表或分區(qū)索引有用.
estimate_percent:采樣行的百分比,取值范圍[0.000001,100],null為全部分析,不采樣. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默認(rèn)值,由oracle決定最佳取采樣值.
block_sapmple:是否用塊采樣代替行采樣.
method_opt: 決定histograms信息是怎樣被統(tǒng)計(jì)的.method_opt的取值如下:
for all columns:統(tǒng)計(jì)所有列的histograms.
for all indexed columns:統(tǒng)計(jì)所有indexed列的histograms.
for all hidden columns:統(tǒng)計(jì)你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:
統(tǒng)計(jì)指定列的histograms.N的取值范圍[1,254]; R
EPEAT上次統(tǒng)計(jì)過(guò)的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: 設(shè)置收集統(tǒng)計(jì)信息的并行度.默認(rèn)值為null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascade: 是收集索引的信息.默認(rèn)為falase.
stattab 指定要存儲(chǔ)統(tǒng)計(jì)信息的表,statid如果多個(gè)表的統(tǒng)計(jì)信息存儲(chǔ)在同一個(gè)stattab中用于進(jìn)行區(qū)分.statown存儲(chǔ)統(tǒng)計(jì)信息表的擁有者.以上三個(gè)參數(shù)若不指定,統(tǒng)計(jì)信息會(huì)直接更新到數(shù)據(jù)字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force: 即使表鎖住了也收集統(tǒng)計(jì)信息
例子:
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自動(dòng)收集統(tǒng)計(jì)信息
---------------------------------------
從10g開(kāi)始，Oracle在建庫(kù)后就默認(rèn)創(chuàng)建了一個(gè)名為GATHER_STATS_JOB的定時(shí)任務(wù)，用于自動(dòng)收集CBO的統(tǒng)計(jì)信息。
這個(gè)自動(dòng)任務(wù)默認(rèn)情況下在工作日晚上10：00-6：00和周末全天開(kāi)啟。
調(diào)用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統(tǒng)計(jì)信息。該過(guò)程首先檢測(cè)統(tǒng)計(jì)信息缺失和陳舊的對(duì)象。然后確定優(yōu)先級(jí)，再開(kāi)始進(jìn)行統(tǒng)計(jì)信息。
可以通過(guò)以下查詢這個(gè)JOB的運(yùn)行情況：
SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 'GATHER_STATS_JOB';
其實(shí)同在10點(diǎn)運(yùn)行的Job還有一個(gè)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
然而這個(gè)自動(dòng)化功能已經(jīng)影響了很多系統(tǒng)的正常運(yùn)行，晚上10點(diǎn)對(duì)于大部分生產(chǎn)系統(tǒng)也并非空閑時(shí)段。
而自動(dòng)分析可能導(dǎo)致極為嚴(yán)重的閂鎖競(jìng)爭(zhēng)，進(jìn)而可能導(dǎo)致數(shù)據(jù)庫(kù)Hang或者Crash。
所以建議最好關(guān)閉這個(gè)自動(dòng)統(tǒng)計(jì)信息收集功能：
關(guān)閉及開(kāi)啟自動(dòng)搜集功能，有兩種方法，分別如下：
方法一：
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;
---------------------------------------
查看統(tǒng)計(jì)
---------------------------------------
表/索引/列上的統(tǒng)計(jì)
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
---------------------------------------
直方圖統(tǒng)計(jì)
---------------------------------------
直方圖的類型存儲(chǔ)在*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數(shù)據(jù)庫(kù),則用APPS連接，否則用其他任何SYS權(quán)限用戶連接都可以
#sqlplus <user>/<pwd>
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.
---------------------------------------
分區(qū)表的統(tǒng)計(jì)信息實(shí)例
---------------------------------------
ORATEA ORACLE的統(tǒng)計(jì)信息在執(zhí)行SQL的過(guò)程中扮演著非常重要的作用，而且ORACLE在表的各個(gè)層次都會(huì)有不同的統(tǒng)計(jì)信息，通過(guò)這些統(tǒng)計(jì)信息來(lái)描述表的，列的各種各樣的統(tǒng)計(jì)信息。下面通過(guò)一個(gè)復(fù)合分區(qū)表來(lái)說(shuō)明一些常見(jiàn)的和常見(jiàn)的統(tǒng)計(jì)信息。
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;
表已創(chuàng)建。
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,表級(jí)的統(tǒng)計(jì)信息
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,表上列的統(tǒng)計(jì)信息
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,分區(qū)的統(tǒng)計(jì)信息
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,分區(qū)上列的統(tǒng)計(jì)信息
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,分區(qū)上列的直方圖信息
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,子分區(qū)的統(tǒng)計(jì)信息
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,子分區(qū)上的列的統(tǒng)計(jì)信息
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,子分區(qū)上的列的直方圖信息
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
我們對(duì)這個(gè)復(fù)合分區(qū)分析之后產(chǎn)生了上面這九種不同層次的統(tǒng)計(jì)信息。CBO想要得要一個(gè)高效的執(zhí)行計(jì)劃需要如此多的統(tǒng)計(jì)信息.
|
