一次ORA-4030問題診斷及解決（三）

　　在報表數據庫的後台alert文件中發現了這個錯誤簡單記錄一下問題的診斷和解決過程數據庫版本 for Solaris sparc

　　尋找產生問題的真正原因

　　在第一篇文章中定位了問題並且找到了解決方法;在第二篇文章中找到了導致源數據庫和目標數據庫執行計劃不同的原因

　　但是到目前為止還沒有找到這個問題產生的真正原因

　　首先理一下思路根據第一篇文章的描述產生ORA問題的原因是由於一個大數據量的插入語句選擇了一個十分糟糕的執行計劃而導致Oracle選擇了這個執行計劃的直接原因是由於列的統計信息出現了錯誤而在第二篇文章中可以確認由於源數據庫的版本為沒有使用列統計信息中的DENSITY列所以沒有引發這個問題而在目標數據庫版本為Oracle使用了統計信息列DENSITY的值所以Oracle認為訪問ORD_HIT_COMM表且通過ENABLE_FLAG列進行限制只會返回條記錄這就導致了Oracle產生了一個錯誤的離譜的執行計劃

　　現在的問題是什麼導致了源數據庫錯誤統計信息的產生

　　這就需要檢查源數據庫數據和統計的來源因為在源數據庫上直接收集統計信息是不會得到這種DENSITY的

　　經過檢查發現這個的源數據庫仍然不是數據的真正源頭而真正的來源數據庫版本是

　　發現了這個信息那麼問題的產生就不奇怪了

　　看一下上這張表的統計信息　　

　　       SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM
　　 FROM USER_TAB_COLUMNS
　　 WHERE TABLE_NAME = ORD_HIT_COMM
　　 AND COLUMN_NAME = ENABLE_FLAG;
　　COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM
　　
　　ENABLE_FLAG   E  FREQUENCY

　　可以看到在Oracleg使用了BUCKETS的設置而且USER_TAB_COLUMNS添加了一個字段HISTOGRAM用來表示列的統計信息的類型

　　FREQUENCY類型和以往的HEIGHT BALANCED類似的列統計不同使用FREQUENCY類型Oracle會選擇與NUM_DISTINCT相同數量的NUM_BUCKETS來進行直方圖統計而直方圖統計信息方式和基於高度的統計信息是不同的最關鍵的是這種統計方式的DENSITY的結果和HEIGHT BALANCED的計算方式大不相同

　　因此在g中由於Oracle了解當前列的統計信息方式為FREQUENCY類型因此可以根據直方圖的信息得到正確的執行計劃和返回記錄數

　　Explained
　　SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY);
　　PLAN_TABLE_OUTPUT
　　
　　
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
　　
　　| | SELECT STATEMENT | | K| M| ()|
　　| | TABLE ACCESS FULL| ORD_HIT_COMM | K| M| ()|
　　
　　 rows selected

　　但是如果將統計信息導入到數據庫中就會存在嚴重的問題由於i的數據庫中沒有表示統計信息類型的HISTOGRAM列因此即使是基於FREQUENCY類型的統計信息也會被當作基於HEIGHT BALANCED類型的統計信息

　　而且從導入的統計信息可以看到雖然直方圖的統計信息被導入但是USER_TAB_COLUMNS中的NUM_BUCKETS列的值為也就是說中優化器根本不會去考慮直方圖信息而是直接通過NUM_DISTINCT和DENSITY的值來確定執行計劃和返回記錄數

　　對於版本Oracle都使用NUM_DISTINCT的值也就避免了問題的產生而在中Oracle使用了DENSITY的值而這個值並不是版本的DBMS_STATS包生成的統計信息而是從g環境中導入的且這個值在g的FREQUENCY類型的統計信息中已經改變了計算方法使得計算結果比環境中要小得多從而導致了上錯誤執行計劃的產生

　　顯然整個問題完全是由於版本差異造成的這個問題說明在將g的表導入到環境中最好不要導入統計信息

　　在導出階段或在導入階段設置STATISTICS = NONE避免g的統計信息導入到環境中在導入過程結束後手工在環境上重新收集統計信息

　　一旦g的統計信息被導入到環境中就必須重新收集統計信息　

　　       SQL> SELECT * FROM V$VERSION;
　　BANNER
　　
　　Oraclei Enterprise Edition Release   bit Production PL/SQL Release   Production
　　CORE  Production
　　TNS for Linux: Version   Production
　　NLSRTL Version   Production
　　SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
　　 FROM USER_TAB_COLUMNS
　　 WHERE TABLE_NAME = ORD_HIT_COMM
　　 AND COLUMN_NAME = ENABLE_FLAG;
　　COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
　　
　　ENABLE_FLAG   E
　　SQL> EXPLAIN PLAN FOR
　　 SELECT * FROM ORD_HIT_COMM WHERE ENABLE_FLAG = ;
　　已解釋
　　SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY);
　　PLAN_TABLE_OUTPUT
　　
　　
　　| Id | Operation | Name | Rows | Bytes | Cost |
　　
　　|  | SELECT STATEMENT | |  |  |  |
　　|*  | TABLE ACCESS FULL | ORD_HIT_COMM |  |  |  |
　　
　　Predicate Information (identified by operation id):
　　
　　  filter(ORD_HIT_COMMENABLE_FLAG=)
　　Note: cpu costing is off

　　已選擇行

　　這時g的統計信息已經導入到環境中如果忘記重新收集統計信息就會導致這個錯誤的產生　

　　 SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER ORD_HIT_COMM)

　　PL/SQL 過程已成功完成　　

　　已選擇行

　　這個問題也從另一個角度說明進行跨版本遷移測試工作的重要性

From:http://tw.wingwit.com/Article/program/Oracle/201311/18563.html