1、   故障描述

某用户某次反馈一个报表单次查询超过1分钟，需要针对这个报表逻辑进行针对性调优，详细排查过程请参考根因分析。

2 、排查处理

优化过程，和开发人员沟通页面调用具体 sql ，存储过程中通过 dblink 调用其他库的多个视图，将存储过程内容修改成 declare 块，添加 DBMS_OUTPUT.PUT_LINE 输出每一步执行时间，定位到下面的 sql 执行时间长。

远程数据库执行下列 SQL 语句，耗时 39.573 秒

select *

          from cb_01_01

         where JITAINAME in

               (SELECT REGEXP_SUBSTR(' 股份XXX 机', '[^,]+', 1, rownum)

                  FROM dual

                CONNECT BY rownum <=

                           LENGTH(' 股份XXX 机') -

                           LENGTH(regexp_replace(' 股份XXX 机', ',', '')) + 1)

           and COLXXXXXXTIMES  <= '2024-10-10 23:59:59'

           and COLXXXXXXTIMES  >= '2024-10-01 00:00:00';

原 SQL 执行计划如下：

通过对视图 cb_01_01 进行下转分析，可用将原 SQL 语句中的过滤谓词 JITAINAME 拆解成 JITA 和 NAME 两个谓词，经过测试语句的选择性提高， SQL 响应时间变快。

对原 SQL 语句进行谓词改造，形成新的 SQL 语句，具体如下：耗时 1.498 秒。

select *

          from cb_01_01

         where jitai in (select pk_defdoc from bd_defdoc where ( dr = 0 or dr is null ) and name in

               (SELECT REGEXP_SUBSTR(' 股份XXX 机', '[^,]+', 1, rownum)

                  FROM dual

                CONNECT BY rownum <=

                           LENGTH(' 股份XXX 机') -

                           LENGTH(regexp_replace(' 股份XXX 机', ',', '')) + 1))

           and COLXXXXXXTIMES  <= '2024-10-10 23:59:59'

           and COLXXXXXXTIMES  >= '2024-10-01 00:00:00';

新SQL执行计划如下：

将新 SQL 语句发送到原库执行，耗时又变为 26.370 秒，由于使用 dblink 跨库，因此计划使用 drving_site 通过添加 hint 固定执行路径解决查询问题，重新执行，耗时 2.539 秒。

select /*+drving_site(cb_01_01)*/*

          from cb_01_01@dblink_xxx

         where jitai in (select pk_defdoc from bd_defdoc where ( dr = 0 or dr is null ) and name in

               (SELECT REGEXP_SUBSTR(' 股份XXX 机', '[^,]+', 1, rownum)

                  FROM dual

                CONNECT BY rownum <=

                           LENGTH(' 股份XXX 机') -

                           LENGTH(regexp_replace(' 股份XXX 机', ',', '')) + 1))

           and COLXXXXXXTIMES  <= '2024-10-10 23:59:59'

           and COLXXXXXXTIMES  >= '2024-10-01 00:00:00';

但是即使将新SQL语句替换存储过程中的内容，执行时间还是要1分钟左右，问题原因为ddl、dml语句中drving_site不生效，随即尝试通过使用游标将数据insert到临时表，通过这一中转的方式进行迂回解决。

open c1 for select /*+drving_site(cb_01_01)*/*

          from cb_01_01@dblink_xxx

         where jitai in (select pk_defdoc from bd_defdoc where ( dr = 0 or dr is null ) and name in

               (SELECT REGEXP_SUBSTR(' 股份XXX 机', '[^,]+', 1, rownum)

                  FROM dual

                CONNECT BY rownum <=

                           LENGTH(' 股份XXX 机') -

                           LENGTH(regexp_replace(' 股份XXX 机', ',', '')) + 1))

           and COLXXXXXXTIMES  <= '2024-10-10 23:59:59'

           and COLXXXXXXTIMES  >= '2024-10-01 00:00:00';

loop

fetch c1 into v_temp;

……

end loop;

close c1;

这时存储过程执行时间从1分钟多到32秒左右，最终查询的表和上面的临时表一样直接使用上面的临时表。

最终，存储过程的执行时间从原来的1分钟多到6秒左右，业务核对存储过程修改前后报表结果进行对比，数据一致。