Oracle中關數據庫對象的統計分析

　　ORACLE在執行SQL時如果使用成本方式分析則所有的成本分析信息來源依靠於系統的統計分析表(DBA_TABLESDBA_INDEXESDBA_TAB_COLUMNS)數據如果說統計分析的數據是不准確的那可能會使ORACLE分析出來的路徑執行性能極差所以統計分析數據是影響ORACLE性能極重要的信息
　　
　　統計分析主要包括產生表及索引的統計信息
　　
　　表的統計信息主要包括表的行數每行的平均長度(字節)空閒塊統計時間等信息
　　
　　索引的統計信息主要包括行數層數葉塊數統計時間等信息
　　
　　另外ORACLE還可以統計列及數據不對稱信息i還可以統計系統信息(CPUI/O)
　　
　　ORACLE執行成本分析時首先取出所應用表及索引的統計數據進行分析其中數據行數是一個重要的參數因為ORACLE在分析表大小時行數為主要參數如果進行兩個表聯合時ORACLE會通過分析表的大小決定應用小表進行全表查詢而大表執行聯合查詢這種性能明顯高於先大表進行全表掃描索引的統計信息對分析也產生比較大的影響如ORACLE通過統計可以分析產生多個索引的優先級及索引的實用性來確定最優的索引策略ORACLE還可以統計列及數據對稱信息以產生更精確的分析如一個表有A字段的索引其中A共有兩種值和共條記錄為的記錄有條為的記錄有條這時如果沒有進行列數據不對稱的統計信息那麼ORACLE對A=及A=條件查詢都會進行索引但實際應用對A=的索引性能得到了很大的提高而A=的索引反而使性能下降所以說索引特征值分析信息對應用索引產生重大影響精確的信息使ORACLE不會使用不應該用的索引
　　
　　實際分析
　　
　　zl_cbqc和zl_yhjbqk都沒有建立統計信息執行如下兩個SQL ORACLE將產生不同的執行計劃
　　
　　 select * from dlyxzl_cbqc b dlyxzl_yhjbqk a where aqc_bh=bqc_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 抄表區冊主鍵
　　
　　 select * from dlyxzl_yhjbqk adlyxzl_cbqc b where aqc_bh=bqc_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 區冊索引
　　
　　在對兩個表進行了統計分析後
　　
　　 select * from dlyxzl_yhjbqk adlyxzl_cbqc b where aqc_bh=bqc_bh
　　
　　執行計劃:
　　
　　SELECT STATEMENT GOAL = CHOOSE
　　
　　HASH JOIN
　　
　　TABLE ACCESS FULL DLYX ZL_CBQC
　　
　　TABLE ACCESS FULL DLYX ZL_YHJBQK
　　
　　 select * from dlyxzl_cbqc bdlyxzl_yhjbqk a where aqc_bh=bqc_bh
　　
　　執行計劃:
　　
　　SELECT STATEMENT GOAL = CHOOSE
　　
　　HASH JOIN
　　
　　TABLE ACCESS FULL DLYX ZL_CBQC
　　
　　TABLE ACCESS FULL DLYX ZL_YHJBQK
　　
　　從以上測試可以明顯看出ORACLE的分析結果
　　
　　第條SQL與第條SQL在沒有統計分析的信息下進行ORACLE無法進行判斷只能以規則方法進行分析根據表的出現先後順序有明顯關系
　　
　　第條SQL與第條SQL在有統計分析的信息下進行ORACLE分析與表的出現先後順序無關因為它已經知道了表的數據量並且已經確定返回的數據量基本上是兩個表所有的數據所以對表兩個表進行了HASH JOIN (同時取出兩個表的數據然後在內存中進行聯合產生返回結果)
　　
　　相關技術
　　
　　用analyze語句產生分析數據
　　
　　分析表:analyze table zl_yhjbqk estimate statistics sample 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
　　
　　用sysdbms_utility包分析數據
　　
　　分析數據庫(包括所有的用戶對象和系統對象):analyze_database
　　
　　分析用戶所有的對象(包括用戶方案內的表索引簇):analyze_schema
　　
　　用sysdbms_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推薦用戶采用sysdbms_stats包體進行分析因為在ORACLEi及其以上的版本全面擴充的此包體的功能sysdbms_utility包體進行分析時會對所有的信息全部分析一遍時間比較長而在i中sysdbms_stats可以利用表修改監控技術來判斷需統計分析的表進行節省了用戶的分析資源
From:http://tw.wingwit.com/Article/program/Oracle/201311/18242.html