SQL执行计划变化分析

开发反馈存储过程做了代码优化后校验反而出现卡顿的现象，导致存储过程卡顿的sql如下：

select /*+ full(a) use_hash(a) */ count(*)
        from sip*** a
        where a.oc_date = v_oc_date
        AND rownum = 1
        and  not exists (select /*+ use_hash(b) */ 1 from sip***_all b
        where b.oc_date=a.oc_date and b.oc_date = v_oc_date and a.zqzh=b.zqzhzh);   

--注：b.oc_date=a.oc_date 为代码优化后的新增条件
复制

当sql为

select /*+ full(a) use_hash(a) */ count(*)
        from sip*** a
        where a.oc_date = v_oc_date
        AND rownum = 1
        and  not exists (select /*+ use_hash(b) */ 1 from sip***_all b
        where /*b.oc_date=a.oc_date and*/ b.oc_date = v_oc_date and a.zqzh=b.zqzhzh);  
复制

执行计划如下：

当sql为

select /*+ full(a) use_hash(a) */ count(*)
        from sip*** a
        where a.oc_date = v_oc_date
        AND rownum = 1
        and  not exists (select /*+ use_hash(b) */ 1 from sip***_all b
        where b.oc_date = v_oc_date and a.zqzh=b.zqzhzh);  
复制

执行计划如下：

sip***_all 表索引如下：

通过10053 trace分析，发现添加条件后导致oracle在试图展开子查询时同外部查询的关系验证失败，最终查看的结果应该是修改数据库 _optimizer_cost_based_transformation 这个隐含参数导致。

SELECT /*+OPT_PARAM('_optimizer_cost_based_transformation' 'on')*/ /*+ full(a) use_hash(a) */
    COUNT(*)
FROM
    sip*** a
WHERE
    a.oc_date = v_oc_date
    AND ROWNUM = 1
    AND NOT EXISTS (
        SELECT /*+ use_hash(b) */
            1
        FROM
            sip***_all b
        WHERE
            b.oc_date = a.oc_date
            AND b.oc_date = v_oc_date
            AND a.zqzh = b.zqzhzh
    );
复制

设置 _optimizer_cost_based_transformation 这个隐含参数是因为之前数据库alert日志中有 ORA-07445 的报错，查看mos发现是sql使用正则表达式触发bug。应用报ORA-03113错误，查看发现数据库alert日志报7445，该系统数据库版本是10.2.0.5，该报错由于触发bug导致：Bug 10274277 - dump on [kkqtcpopn_int] (Doc ID 10274277.8)
修改隐含参数后应用OK，这个bug在11g已修复，修改如下：

alter session set "_optimizer_cost_based_transformation" = off;
复制

通过这个案例可以看出，当数据库某条sql触发bug可以通过隐含参数解决的情况下，如果怕影响这个系统，可以尝试是否可以在SQL语句中修改，如果可以，在sql语句中修改，避免影响整个数据库。