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
秒左右，业务核对存储过程修改前后报表结果进行对比，数据一致
