一个SQL语句引发的ORA-00600错误排查（二）

继昨天一个SQL语句导致的ORA-00600错误之后，我给出了背景和初步的分析结果，今天来给出我的结论，当然说明原因不是我的本意，还有反思。
首先语句类似这样的形式：
MERGE INTO (SELECT * FROM TEST_SERVER_LOG WHERE BUY_TIME>=TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS')
                               AND BUY_TIME<to_date(:2 ,'yyyy-mm-dd="" hh24:mi:ss')=""                               ="" and="" put_date="TO_DATE(:3" ,'yyyy-mm-dd'))="" t                    USING(SELECT
                           TO_CHAR(:4 ) AS SN,
                           TO_NUMBER(:5 ) AS GROUP_ID,
                          。。。
                           TO_NUMBER(:23 ) AS CLIENT_STYLE,
                           TO_CHAR(:24 ) AS GAME_TYPE
                         FROM DUAL) A
                   ON (T.SN=A.SN)
                   WHEN NOT MATCHED THEN
                   INSERT(T.SN,T.GROUP_ID,T.SERVER_IP,。。。)
                   VALUES(A.SN,A.GROUP_ID,A.SERVER_IP。。。)
运行后会报出ORA-00600，我在初步的分析之后排除了绑定变量的个数，表中数据量大的可能因素。
在经过一番周折之后，发现问题可能出在这个语句的结构上。
当然我换一个方式来说明，我可以随便创建一个表，然后模拟这个ORA-00600的错误。
创建测试表test_bug
SQL> create table test_bug as select * from dba_objects where rownum<1;
Table created.
然后使用如下的语句尝试生成执行计划。
SQL> explain plan for merge into (select * from test_bug where object_type='TABLE') t
       using (select :1 object_id,:2 object_name,:3 objet_type from dual) a
       on(t.object_id=a.object_id)
       where not matched then
       insert into test_bug(object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
explain plan for merge into (select * from test_bug where object_type='TABLE') t
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp:1], [1], [], [4], [], [],
[], [], [], [], [], []
当然了我无意中埋下了几个探针，如果你看到语句哪里有问题，后续分析就会明白了。
这个语句里的问题我是可以保持了(select * from test_bug where object_type='TABLE') t 这个子查询，抛出了ORA-00600的错误，那么我再简化一番如何，简化为（select*from test_bug）t这个子查询，同样还是会抛出一样的错误。
explain plan for merge into (select * from test_bug ) t
       using (select :1 object_id,:2 object_name,:3 objet_type from dual) a
       on(t.object_id=a.object_id)
       where not matched then
       insert into test_bug(object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
explain plan for merge into (select * from test_bug ) t
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp:1], [1], [], [4], [], [],
[], [], [], [], [], []
我们来回过头来翻翻旧账，刚刚的语句的问题在如下的几个地方，在解析的时候都没有抛出错误，可见问题是出在这些之前，那么推理只有test_bug相关的子查询了。


修复这几个语法之后，使用下面的方式就没有问题了。
explain plan for merge into test_bug t
       using (select :1 object_id,:2 object_name,:3 object_type from dual) a
       on(t.object_id=a.object_id)
       when not matched then
       insert (object_id,object_name,object_type) values(a.object_id,a.object_name,a.object_type);
明白了问题的原因，我们来反思一下，其实我们缩写的merge语句都是merge into table_xx using(xxxx) on (xxx)的形式
在table_xx的地方加入子查询，可能会让我们在联想到一些语句中使用子查询的DML方式，但是在merge语句中，这个地方就是大忌，所幸的是这个问题目前的测试没有发现对线上环境产生严重的影响，但是需要引以为戒。
而对于merge语句的更多反思，如下：
1.我所从事的一些调优工作中，对于merge的优化很难下手，因为这虽然是一个语句，但是有多重执行路径，执行计划没法确定，使用调优工具优化也给不出建议。
2.在10g的版本中，如果scheduler中使用PL/SQL块，包含有merge语句，使用dbms_metadata.get_ddl竟然无法查看到完整的DDL信息
3.如果你想查看到DDL的信息，如果通过解析dmp的方式，就会发现DMP对于这个语句的处理很是特殊，可能又会让你失望了。
这些问题会或多或少困扰到你，而我印象很深的案例，则是第1项中列举的，优化类的困扰。我举一个例子。
下面是我在一次系统监控中发现的一个性能问题，CPU使用异常，而经过分析发现瓶颈来源于数据库层面的一个SQL语句。

看到的语句类似这样的形式：

MERGE INTO UC_OPENPLATFORM_USER t
                   USING (SELECT count(*) CNT from UC_OPENPLATFORM_USER where
USER_ID=:1 and PLATFORM=:2) tw
                   ON (tw.CNT>0)
                   WHEN MATCHED THEN UPDATE SET t.NAME=:3, t.UPDATE_DATE=SYSDATE
where USER_ID=:4 and PLATFORM=:5
                   WHEN NOT MATCHED THEN INSERT(USER_ID, PLATFORM, NAME,
CREATE_DATE, UPDATE_DATE) VALUES(:6, :7, :8, SYSDATE, SYSDATE)
查看执行计划发现里面存在大量的全表扫描，资源消耗极高。
而这个语句的逻辑其实仔细看看还能明白，就是在插入一条记录前看看表中是否含有，如果没有就插入，否则更新，但是里面使用了count(*)的方式处理，过滤条件存在一些潜在的问题，而优化方式就是简化这种逻辑。改为如下的方式：

MERGE INTO UC_OPENPLATFORM_USER t

                   USING (SELECT :1 USER_ID,:2 PLATFORM from DUAL) tw

                   ON (tw.USER_ID=T.USER_ID and tw.PLATFORM=t.PLATFORM)

                   WHEN MATCHED THEN UPDATE SET t.NAME=:3, t.UPDATE_DATE=SYSDATE

where USER_ID=:4 and PLATFORM=:5

                   WHEN NOT MATCHED THEN INSERT(USER_ID, PLATFORM, NAME,

CREATE_DATE, UPDATE_DATE) VALUES(:6, :7, :8, SYSDATE, SYSDATE)       

而对ORA-00600的这个问题，其实也可以进一步反思，这个merge使用只有一个场景，其实可以考虑使用INSERT语句来实现。
很多的事情都有两面性，merge语句就是如此，而且是一种特殊的存在，我依然记得很久之前的一次技术争论中，有人说道：判断一个技术的优劣，也需要看待，到底是它带来的问题更多还是解决的问题更多？   
确实如此。 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-2122253/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-2122253/