ORACLE資料庫物件統計分析技術應用

oracle|對象|資料|資料庫|統計
資料庫物件統計分析技術應用

 

ORACLE在執行SQL時如果使用成本方式分析則所有的成本分析資訊來源依靠於系統的統計分析表（DBA_TABLES、DBA_INDEXES、DBA_TAB_COLUMNS）資料，如果說統計分析的資料是不準確的，那可能會使ORACLE分析出來的路徑執行效能極差，所以統計分析資料是影響ORACLE效能極重要的資訊。

 

統計分析主要包括產生表及索引的統計資訊

表的統計資訊主要包括表的行數，每行的平均長度（位元組），空閑塊，統計時間等資訊

索引的統計資訊主要包括行數、層數、葉塊數、統計時間等資訊。

另外ORACLE還可以統計列及資料不對稱資訊，9i還可以統計系統資訊（CPU，I/O）

 

ORACLE執行成本分析時首先取出所應用表及索引的統計資料進行分析，其中資料行數是一個重要的參數，因為ORACLE在分析表大小時行數為主要參數，如果進行兩個表聯合時，ORACLE會通過分析表的大小，決定應用小表進行全表查詢，而大表執行聯集查詢，這種效能明顯高於先大表進行全表掃描。索引的統計資訊對分析也產生比較大的影響，如ORACLE通過統計可以分析產生多個索引的優先順序及索引的實用性來確定最優的索引策略。ORACLE還可以統計列及資料對稱資訊以產生更精確的分析。如一個表有A欄位的索引，其中A共有兩種值1和0，共10000條記錄，為0的記錄有10條，為1的記錄有9990條，這時如果沒有進行列資料不對稱的統計資訊，那麼ORACLE對A=0及A=1條件查詢都會進行索引，但實際應用對A=0的索引效能得到了很大的提高，而A=1的索引反而使效能下降。所以說索引特徵值分析資訊對應用索引產生重大影響，精確的資訊使ORACLE不會使用不應該用的索引。

 

實際分析

zl_cbqc和zl_yhjbqk都沒有建立統計資訊，執行如下兩個SQL ORACLE將產生不同的執行計畫。

1   select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

執行計畫：

SELECT STATEMENT, GOAL = CHOOSE                             

 NESTED LOOPS                            

  TABLE ACCESS FULL    DLYX     ZL_YHJBQK              

  TABLE ACCESS BY INDEX ROWID      DLYX     ZL_CBQC                   

   INDEX UNIQUE SCAN  DLYX     抄表區冊主鍵                    

 

2  select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

執行計畫：

SELECT STATEMENT, GOAL = CHOOSE                             

 NESTED LOOPS                            

  TABLE ACCESS FULL    DLYX     ZL_CBQC                  

  TABLE ACCESS BY INDEX ROWID      DLYX     ZL_YHJBQK                

   INDEX RANGE SCAN    DLYX     區冊索引                    

 

 

在對兩個表進行了統計分析後

3         select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

執行計畫：

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

執行計畫：

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

 

從以上測試可以明顯看出ORACLE的分析結果

第1條SQL與第2條SQL在沒有統計分析的資訊下進行，ORACLE無法進行判斷，只能以規則方法進行分析，根據表的出現先後順序有明顯關係。

第3條SQL與第4條SQL在有統計分析的資訊下進行，ORACLE分析與表的出現先後順序無關，因為它已經知道了表的資料量並且已經確定返回的資料量基本上是兩個表所有的資料，所以對錶兩個表進行了HASH JOIN （同時取出兩個表的資料然後在記憶體中進行聯合產生返回結果）。

 

相關技術

用analyze語句產生分析資料

分析表：analyze table zl_yhjbqk estimate statistics sample 20 percent

分析索引：analyze index使用者資料表主鍵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包分析資料

       分析資料庫（包括所有的使用者物件和系統對象）：analyze_database

       分析使用者所有的對象（包括使用者方案內的表、索引、簇）：analyze_schema

用sys.dbms_stats包處理分析資料

       分析資料庫（包括所有的使用者物件和系統對象）：gather_database_stats

       分析使用者所有的對象（包括表、索引、簇）：gather_schema_stats

分析表：gather_table_stats

分析索引：gather_index_stats

刪除資料庫統計資訊：delete_database_stats

刪除使用者方案統計資訊：delete_schema_stats

刪除表統計資訊：delete_table_stats

刪除索引統計資訊：delete_index_stats

刪除列統計資訊：delete_column_stats

設定表統計資訊：set_table_stats

設定索引統計資訊：set_index_stats

設定列統計資訊：set_column_stats

 

ORACLE推薦使用者採用sys.dbms_stats包體進行分析，因為在ORACLE9i及其以上的版本全面擴充的此包體的功能。sys.dbms_utility包體進行分析時會對所有的資訊全部分析一遍，時間比較長，而在9i中sys.dbms_stats可以利用表修改監控技術來判斷需統計分析的表進行，節省了使用者的分析資源。

 

備忘：

       ORACLE在得到了表的統計分析資料後才會進行成本分析，否則採用規則分析。

       並不是所有的統計資料都會對ORACLE分析產生影響，有些統計資料ORACLE並不處理，只是提供給使用者一個參考資訊，也可能在ORACLE以後的版本中利用這些資訊進行分析。

       分析表的登入使用者必須對錶擁有存取權限或具有DBA或Analyze any許可權

本文是我剛學ORACLE資料庫時寫，可能有些錯誤的地方，希望網友能給與指正！

葉正盛
2003-08-15