Oracle 11g 11.2.0.2 的 Cardinality Feedback 影響的執行計畫不穩定，第一次快，第二次慢。

問題描述 :
日前遇到一句SQL發現執行時第一次快、第二次很慢，跟之前遇過的不一樣，因為大多數都是第一次慢、第二次快，非常弔詭，做了 dbms_stats 問題依然存在。。


環境 :
RHEL 5.5 x86-64
Oracle Database Standard Edition 11.2.0.2。

參考資料 :
http://externaltable.blogspot.tw/2012/08/how-to-turn-off-adaptive-cursor-sharing.html
https://blogs.oracle.com/optimizer/entry/cardinality_feedback

補充
發現可能為 Bug 14147762 問題

處理過程

當下取得客戶執行的SQL測試，與 10046 level 128 的 trace 與SQL Explain plan ，慢得快的各一次trace來比對，發現兩次的Explain plan不一樣。

因為是客戶發出來的問題，為了不被告到法院，所以語句簡化如下範例。
select M1.col1,M1.col50 ......................
from SCHEMA01.TABLE01 M1
on .....
and .....
left outer join SCHEMA01.TABLE01 D1
on .....
and .....
join SCHEMA01.TABLE02 a
on .....
and .....
left join SCHEMA01.TABLE03 m
on .....
and .....
order by 1,2,3,4,5,6,7
/

取得SQL Statement後就是分析三個 table，每個 table 都在兩萬筆 rows 左右，每筆 row 都有40到 80不等的 column，column 大多都是 varchar2(30)，Index 幾乎都是 Primary Key!!

以經驗來看以上的分析，一般 HP、IBM + Storage 的主機不至於應付不來，回應時間也應該在一秒左右，但是測試運作後發現，第一次執行 1秒，第二次執行 58秒，58就有點誇張，又不是上億筆ROW這種大table。

由於客戶表示他寫了十幾年的程式，不認為調整SQL或調整Index就可以解決問題，所以在不修正客戶SQL語句前提下，我這邊發現有很多該做索引column沒做，即使是很簡單的on M1.COL50 = D1.COL50 and D1.COL04='Y'，都會造成Full table scan。

第一次的執行計畫是這樣的


第二次的執行計畫



第一次的執行計畫，一個 sort order by 分裂了三個 filter，三個 filter 各有各的  nested loops 與 hash join 去 access table。

第二次的執行計畫，是 sort order by < filter < nested loops < hash join + table access by index rowid ，雖然分裂了兩個子計畫，但hash join 那個子計畫卻更深入更耗成本，也可以看到 sort order by 要處裡的資料暴增好幾十倍。

查了一下 Google 發現是 Cardinality Feedback 的影響導致兩個執行計畫的不同，以下的參數確實可以讓 SQL Statement 多次執行速度穩定依然很穩定，但這樣的干涉 optimizer 放在最後的解決手段，畢竟別的語句不會這樣。

alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;

一開有個想法就是，若一開始我就將 full table scan解決掉，是否還會回饋 Cardinality 給optimizer 進而影響執行計畫。

最後是把缺少 index 建立後就沒問題了，不僅執行成本降低，且Explain plan也穩定了，所以應證一開始的想法，先透過建立Index來讓 optimizer 走好一點的路來避免 Cardinality Feedback的問題。

網路上也有在探討  Cardinality Feedback，代表知能的優化開始出現，未來的DBA要做得越來越少，我想這是好事也不是一件好事 。

雖然降低後的執行成本(最後一部 select statement 的cost 在 1103K)很高也有改善的空間，但因為無法修改SQL語句，所以放棄繼續修正 SQL Statement。

記得要做  exec dbms_stats.gather_schema_stats('SCHEMA01');

所以這樣的觀點來看，雖人Oracle 11g 的新特性 Cardinality Feedback 也算是一個優點，因為發生了就會去檢討 SQL Statement 與 Index，不但可以解決目前的問題，未來也可以避免 table 成長後執行速度快速下降的問題。