Oracle性能優化-SQL優化(案例四)

Oracle 性能優化 -SQL 優化 ( 案例四 )

環境：

DB:Oracle 11.2.0.1.0

問題：

ERP 薪資發放節點計算時間耗時 較長，需要15 分鐘左右；

問題原因：

有兩個SQL 執行特別慢

第一個查詢WA_CACU_DATA 的 SQL ，在 plsql 中執行特別快，返回 0 條，懷疑某些堆表被當成臨時表使用，導致執行計劃有問題，手動刪除和鎖定這些表的統計信息后查詢 SQL 速度有明顯提高；

第二個更新WA_CACU_DATA 的 SQL ，第一次執行快，第二次執行慢，執行計劃不穩定，禁用基數反饋 (_optimizer_use_feedback) 后速度正常；

解決過程：

問題重現時，查看主要慢在兩個SQL ，一個 select wa_cacu_data ... ，另一個 update  ...;

一 耗時長的查詢SQL 如下

執行計劃如下：

解決方案：

在plsql 中執行特別快，返回 0 條，懷疑某些堆表被當成臨時表使用，導致執行計劃有問題，手動刪除和鎖定這些表的統計信息后查詢 SQL 速度有明顯提高；

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ tbm_period ’ );

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ org_adminorg ’ );

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ org_hrorg ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ tbm_period ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ org_adminorg ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ org_hrorg ’ );

二：耗時長的update 語句

抓取完整sql 單獨執行時，發現第一次執行很快，第二次執行特別慢，并且第一次和第二次生成的執行計劃不一樣，第二次執行計劃帶有“ cardinality feedback used for this statement ”，懷疑和 oracle 11g 基數反饋特性有關，導致執行計劃不穩定， SQL 執行效率低。

解決方案：

session 級別禁用基數反饋后，多次手動執行 SQL ，速度穩定變快了。

alter session set "_optimizer_use_feedback"=false;

臨時解決辦法可以考慮系統級別禁用基數反饋，或研發更改代碼，在sql 級別增加 hint 禁用基數反饋。

alter system set "_optimizer_use_feedback"=false;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！