ORACLE數(shù)據(jù)庫(kù)對(duì)象統(tǒng)計(jì)分析技術(shù)應(yīng)用
2024-08-29 13:44:51
供稿：網(wǎng)友
數(shù)據(jù)庫(kù)對(duì)象統(tǒng)計(jì)分析技術(shù)應(yīng)用
Oracle在執(zhí)行SQL時(shí)假如使用成本方式分析則所有的成本分析信息來(lái)源依靠于系統(tǒng)的統(tǒng)計(jì)分析表（DBA_TABLES、DBA_INDEXES、DBA_TAB_COLUMNS）數(shù)據(jù)，假如說(shuō)統(tǒng)計(jì)分析的數(shù)據(jù)是不準(zhǔn)確的，那可能會(huì)使ORACLE分析出來(lái)的路徑執(zhí)行性能極差，所以統(tǒng)計(jì)分析數(shù)據(jù)是影響ORACLE性能極重要的信息。
統(tǒng)計(jì)分析主要包括產(chǎn)生表及索引的統(tǒng)計(jì)信息
表的統(tǒng)計(jì)信息主要包括表的行數(shù)，每行的平均長(zhǎng)度（字節(jié)），空閑塊，統(tǒng)計(jì)時(shí)間等信息
索引的統(tǒng)計(jì)信息主要包括行數(shù)、層數(shù)、葉塊數(shù)、統(tǒng)計(jì)時(shí)間等信息。
另外ORACLE還可以統(tǒng)計(jì)列及數(shù)據(jù)不對(duì)稱(chēng)信息，9i還可以統(tǒng)計(jì)系統(tǒng)信息（CPU，I/O）
ORACLE執(zhí)行成本分析時(shí)首先取出所應(yīng)用表及索引的統(tǒng)計(jì)數(shù)據(jù)進(jìn)行分析，其中數(shù)據(jù)行數(shù)是一個(gè)重要的參數(shù)，因?yàn)镺RACLE在分析表大小時(shí)行數(shù)為主要參數(shù)，假如進(jìn)行兩個(gè)表聯(lián)合時(shí)，ORACLE會(huì)通過(guò)分析表的大小，決定應(yīng)用小表進(jìn)行全表查詢(xún)，而大表執(zhí)行聯(lián)合查詢(xún)，這種性能明顯高于先大表進(jìn)行全表掃描。索引的統(tǒng)計(jì)信息對(duì)分析也產(chǎn)生比較大的影響，如ORACLE通過(guò)統(tǒng)計(jì)可以分析產(chǎn)生多個(gè)索引的優(yōu)先級(jí)及索引的實(shí)用性來(lái)確定最優(yōu)的索引策略。ORACLE還可以統(tǒng)計(jì)列及數(shù)據(jù)對(duì)稱(chēng)信息以產(chǎn)生更精確的分析。如一個(gè)表有A字段的索引，其中A共有兩種值1和0，共10000條記錄，為0的記錄有10條，為1的記錄有9990條，這時(shí)假如沒(méi)有進(jìn)行列數(shù)據(jù)不對(duì)稱(chēng)的統(tǒng)計(jì)信息，那么ORACLE對(duì)A=0及A=1條件查詢(xún)都會(huì)進(jìn)行索引，但實(shí)際應(yīng)用對(duì)A=0的索引性能得到了很大的提高，而A=1的索引反而使性能下降。所以說(shuō)索引特征值分析信息對(duì)應(yīng)用索引產(chǎn)生重大影響，精確的信息使ORACLE不會(huì)使用不應(yīng)該用的索引。
實(shí)際分析
zl_cbqc和zl_yhjbqk都沒(méi)有建立統(tǒng)計(jì)信息，執(zhí)行如下兩個(gè)SQL ORACLE將產(chǎn)生不同的執(zhí)行計(jì)劃。
1 select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh
執(zhí)行計(jì)劃：
SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
TABLE access FULL DLYX ZL_YHJBQK
TABLE ACCESS BY INDEX ROWID DLYX ZL_CBQC
INDEX UNIQUE SCAN DLYX 抄表區(qū)冊(cè)主鍵
2 select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh
執(zhí)行計(jì)劃：
SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
TABLE ACCESS FULL DLYX ZL_CBQC
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK
INDEX RANGE SCAN DLYX 區(qū)冊(cè)索引
在對(duì)兩個(gè)表進(jìn)行了統(tǒng)計(jì)分析后
3 select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh
執(zhí)行計(jì)劃：
SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449
HASH JOIN 159 72853 9689449
TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596
TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211
4 select * from dlyx.zl_cbqc b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh
執(zhí)行計(jì)劃：
SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449
HASH JOIN 159 72853 9689449
TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596
TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211
從以上測(cè)試可以明顯看出ORACLE的分析結(jié)果
第1條SQL與第2條SQL在沒(méi)有統(tǒng)計(jì)分析的信息下進(jìn)行，ORACLE無(wú)法進(jìn)行判定，只能以規(guī)則方法進(jìn)行分析，根據(jù)表的出現(xiàn)先后順序有明顯關(guān)系。
第3條SQL與第4條SQL在有統(tǒng)計(jì)分析的信息下進(jìn)行，ORACLE分析與表的出現(xiàn)先后順序無(wú)關(guān)，因?yàn)樗呀?jīng)知道了表的數(shù)據(jù)量并且已經(jīng)確定返回的數(shù)據(jù)量基本上是兩個(gè)表所有的數(shù)據(jù)，所以對(duì)表兩個(gè)表進(jìn)行了HASH JOIN （同時(shí)取出兩個(gè)表的數(shù)據(jù)然后在內(nèi)存中進(jìn)行聯(lián)合產(chǎn)生返回結(jié)果）。
相關(guān)技術(shù)
用analyze語(yǔ)句產(chǎn)生分析數(shù)據(jù)
分析表：analyze table zl_yhjbqk estimate statistics sample 20 percent
分析索引：analyze index用戶(hù)資料表主鍵compute statistics
分析列：analyze table zl_yhjbqk compute statistics for columns hbs_bh
分析索引列：analyze table zl_yhjbqk compute statistics for all indexed columns
用sys.dbms_utility包分析數(shù)據(jù)
分析數(shù)據(jù)庫(kù)（包括所有的用戶(hù)對(duì)象和系統(tǒng)對(duì)象）：analyze_database
分析用戶(hù)所有的對(duì)象（包括用戶(hù)方案內(nèi)的表、索引、簇）：analyze_schema
用sys.dbms_stats包處理分析數(shù)據(jù)
分析數(shù)據(jù)庫(kù)（包括所有的用戶(hù)對(duì)象和系統(tǒng)對(duì)象）：gather_database_stats
分析用戶(hù)所有的對(duì)象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
刪除數(shù)據(jù)庫(kù)統(tǒng)計(jì)信息：delete_database_stats
刪除用戶(hù)方案統(tǒng)計(jì)信息：delete_schema_stats
刪除表統(tǒng)計(jì)信息：delete_table_stats
刪除索引統(tǒng)計(jì)信息：delete_index_stats
刪除列統(tǒng)計(jì)信息：delete_column_stats
設(shè)置表統(tǒng)計(jì)信息：set_table_stats
設(shè)置索引統(tǒng)計(jì)信息：set_index_stats
設(shè)置列統(tǒng)計(jì)信息：set_column_stats
ORACLE推薦用戶(hù)采用sys.dbms_stats包體進(jìn)行分析，因?yàn)樵贠RACLE9i及其以上的版本全面擴(kuò)充的此包體的功能。sys.dbms_utility包體進(jìn)行分析時(shí)會(huì)對(duì)所有的信息全部分析一遍，時(shí)間比較長(zhǎng)，而在9i中sys.dbms_stats可以利用表修改監(jiān)控技術(shù)來(lái)判定需統(tǒng)計(jì)分析的表進(jìn)行，節(jié)省了用戶(hù)的分析資源。
備注：
ORACLE在得到了表的統(tǒng)計(jì)分析數(shù)據(jù)后才會(huì)進(jìn)行成本分析，否則采用規(guī)則分析。
并不是所有的統(tǒng)計(jì)數(shù)據(jù)都會(huì)對(duì)ORACLE分析產(chǎn)生影響，有些統(tǒng)計(jì)數(shù)據(jù)ORACLE并不處理，只是提供給用戶(hù)一個(gè)參考信息，也可能在ORACLE以后的版本中利用這些信息進(jìn)行分析。
分析表的登錄用戶(hù)必須對(duì)表?yè)碛性L問(wèn)權(quán)限或具有DBA或Analyze any權(quán)限
本文是我剛學(xué)ORACLE數(shù)據(jù)庫(kù)時(shí)寫(xiě)，可能有些錯(cuò)誤的地方，希望網(wǎng)友能給與指正！
葉正盛
2003-08-15
