要命的MERGE JOIN CARTESIAN

一个应用，突然出现响应缓慢，查看一下等待时间，发现有很多的数据读取的等待，而且每次执行读取的BLOCK数量明显比之前多，看看SQL的执行计划，发现跟之前的执行计划不一样，多了一个MERGE JOIN CARTESIAN 的过程，把这些进程杀掉，刷新下SHARED POOL，然后等SQL重新请求进来被解析，执行计划已经正确了，下面来看看这个MERGE JOIN CARTESIAN 是何方神圣

先来看看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级别的参数应该都可以的。