要命的MERGE JOIN CARTESIAN

一個應用，突然出現響應緩慢，檢視一下等待時間，發現有很多的資料讀取的等待，而且每次執行讀取的BLOCK數量明顯比之前多，看看SQL的執行計劃，發 現跟之前的執行計劃不一樣，多了一個MERGE JOIN CARTESIAN 的過程，把這些程式殺掉，重新整理下SHARED POOL，然後等SQL重新請求進來被解析，執行計劃已經正確了，下面來看看這個MERGE JOIN CARTESIAN 是何方神聖[@more@]
先來看看SQL：
SELECT /*+ USE_NL(store) INDEX(store EI_ATTRSTORE) INDEX(dn EP_DN) FIRST_ROWS */
STORE.ENTRYID,
STORE.ATTRNAME,
NVL(STORE.ATTRVAL, ' '),
NVL(STORE.ATTRSTYPE, ' ')
FROM CT_DN DN, DS_ATTRSTORE STORE
WHERE DN.ENTRYID IN
((SELECT /*+ INDEX(at1 VA_uid) FIRST_ROWS */
AT1.ENTRYID
FROM CT_UID AT1
WHERE (AT1.ATTRVALUE = :0 OR AT1.ATTRVALUE = :ORM)))
AND (DN.PARENTDN LIKE :BDN ESCAPE
'' OR (DN.RDN = :RDN AND DN.PARENTDN = :PDN))
AND DN.ENTRYID = STORE.ENTRYID
AND DN.ENTRYID >= :ENTRYTHRESHOLD
AND STORE.ATTRKIND IN ('u', 'o')
AND STORE.ATTRNAME NOT IN ('member', 'uniquemember')
這個是一個應用當中的SQL，上面這一大砣是什麼意思也不用關心，現在看看正確的執行計劃：
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 6588 | 4 (25)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | DS_ATTRSTORE | 27 | 1377 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 54 | 6588 | 4 (25)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 142 | 3 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 40 | 1 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | VA_UID | 2 | 40 | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| CT_DN | 1 | 51 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EP_DN | 1 | | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EI_ATTRSTORE | 27 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

這個執行計劃跟期望的一致，而且SQL中的大量HINT也說明了這點，首先根據CT_UID表的VA_uid索引進行查詢，然後根據CT_DN表的EP_DN索引進行檢索，兩個結果集進行一個NEST LOOP，再把得到的結果跟使用DS_ATTRSTORE的索引EI_ATTRSTORE檢索出來的結果再做一個NEST LOOP，最後得到結果集。而出現問題時候的執行計劃是這樣的：
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 3 (100)
1 TABLE ACCESS BY INDEX ROWID CT_DN 1 50 1 (0) 00:00:01
2 NESTED LOOPS 1 659 3 (0) 00:00:01
3 MERGE JOIN CARTESIAN 1 609 2 (0) 00:00:01
4 TABLE ACCESS BY INDEX ROWID DS_ATTRSTORE 2 164 1 (0) 00:00:01
5 INDEX RANGE SCAN EI_ATTRSTORE 2 1 (0) 00:00:01
6 BUFFER SORT 1 527 1 (0) 00:00:01
7 SORT UNIQUE 1 527 1 (0) 00:00:01
8 INLIST ITERATOR
9 INDEX RANGE SCAN VA_UID 1 527 1 (0) 00:00:01
10 INDEX RANGE SCAN EP_DN 1 1 (0) 00:00:01
第一步根據VA_UID的索引來檢索CT_UID表是沒問題的，而第二步卻直接掃描DS_ATTRSTORE表的EI_ATTRSTORE索引，然後這兩個結果集進行一個MERGE JOIN CARTESIAN，這玩意問題就來了，因為DS_ATTRSTORE表是和CT_DN表透過ENTRYID來進行連線的，而且DS_ATTRSTORE表和CT_UID表之間是沒有任何關聯關係的，雖然根據索引掃描CT_UID表特別快，因為有選擇條件的，但是根據索引對DS_ATTRSTORE的掃描就一塌糊塗了，看起來是一個RANGE的掃描，但因為沒有任何選擇條件，其實就是一個全掃描，然後再來根據ROWID去回表取資料，這個不慢才是奇怪了呢。

問題是，ORACLE為什麼搞這麼一個傻蛋執行計劃呢？

這個問題發生已經是好幾個月前的事情了，很多東西也沒地方查證了，但可以肯定的是，發生問題是在中午，而且沒有任何DDL操作，也沒有對庫的統計分析的操作，但是卻突然跳出這麼一個執行計劃，真的是莫名其妙了。

看了看ML，關於MERGE JOIN的BUG一大堆，不過這個跟 6251917很像，說即使你加了一堆提示來走NEST LOOP，但是仍然會因為這個BUG，導致這些HINT不起作用，最後走到一個MERGE JOIN的執行計劃中。但是ML上說這個BUG在10.2.0.4的版本中已經修復了，可恰恰發生這個問題的庫就是這個版本的。而且更嚴重的BUG有可能導致返回的結果集都不正確，看來這個咚咚使用起來還真的是要慎重！

避免的方法基本就是把MERGE JOIN給打死，可以在系統級別設定隱含引數_optimizer_mjc_enabled=false；也可以在登陸觸發器中設定SESSION級別的_optimizer_mjc_enabled=false；也可以在單個SQL級別使用HINT來禁止，/*+ OPT_PARAM('_optimizer_mjc_enabled','false') */，其實目的都一樣，就是不用這個咚咚，至於在哪個級別禁止，要看實際需要了。


順便提一下強悍的OPT_PARAM，這玩意可以讓你在SQL級別透過加HINT的方式來調整單個SQL執行的時候所依賴的系統引數的值，相當強悍。這個是 10.2版本新引進的引數，至於哪些引數可以在這個HINT中設定，我也不知道，不過估計SESSION級別的引數應該都可以的。