ORACLE數據庫對象統計分析技術應用

2024-08-29 13:31:21

字體：大中小

來源：轉載

供稿：網友

商業源碼熱門下載www.html.org.cn

數據庫對象統計分析技術應用

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

上一篇：在DB2中如何實現Oracle的相關功能(三)

下一篇：杜絕安全隱患容易忽視的Oracle安全問題