1.  优化背景：客户反馈一个报表单次查询超过 1 分钟

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

 

-- 远程数据库执行

select  *

           from  cb_01_01

          where  JITAINAME in

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

                   FROM  dual

                 CONNECT   BY   rownum  <=

                            LENGTH ( ' 股份 39 号机 ' ) -

                            LENGTH (regexp_replace( ' 股份 39 号机 ' , ',' , '' )) + 1 )

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

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

 

 

--cb_01_01 的内容

 

 

 

 

-- 对语句进行修改

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( ' 股份 39 号机 ' , '[^,]+' , 1 , rownum )

                   FROM  dual

                 CONNECT   BY   rownum  <=

                            LENGTH ( ' 股份 39 号机 ' ) -

                            LENGTH (regexp_replace( ' 股份 39 号机 ' , ',' , '' )) + 1 ))

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

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

 

 

-- 修改后的语句到源库执行，时间还是 26 秒多

 

 

-- 使用 drving_site 解决查询问题，执行时间 2.5 秒左右

 

 

-- 用上面的语句替换存储过程中的内容，执行时间还是要 1 分钟左右，问题原因为 ddl 、 dml 语句中 drving_site 不生效，通过使用游标将数据 insert 到临时表

 

 

-- 这时存储过程执行时间从 1 分钟多到 32 秒左右，继续看下一段慢的 sql 。  

 

-- 最终查询的表和上面的临时表一样直接使用上面的临时表

 

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