优化存储过程--批量数据处理
原过程摘录如下：
CREATE OR REPLACE PROCEDURE p_sb_yqwsb_dscl
as
BEGIN
...
update hx_sb.SB_YSBTJ a
set NSSBRQ = (select NSSBRQ
FROM hx_zgxt.SB_SBXX b
WHERE a.djxh = b.djxh
and a.zsxm_dm = b.zsxm_dm
and a.zspm_dm = b.zspm_dm
and a.skssqq = b.skssqq
and a.skssqz = b.skssqz and A.zsxm_dm<>'10101'
and rownum=1),XGR_DM='00000000000',XGRQ=sysdate
where exists (select 1
FROM hx_zgxt.SB_SBXX b
WHERE a.djxh = b.djxh
and a.zsxm_dm = b.zsxm_dm
and a.zspm_dm = b.zspm_dm
and a.skssqq = b.skssqq
and a.skssqz = b.skssqz and A.zsxm_dm<>'10101'
) and NSSBRQ is null;
...
end;
该存储过程处理150W条记录，在生产环境36小时跑不出来。检查等待事件发现出现大量GC CURRENT REQUEST等待，于是第一次优化尝试采用临时表，避免GC等待：
update hx_sb.tmp_SB_YSBTJ a
set NSSBRQ = (select NSSBRQ
FROM hx_zgxt.TMP_SB_SBXX b
WHERE a.djxh = b.djxh
and a.zsxm_dm = b.zsxm_dm
and a.zspm_dm = b.zspm_dm
and a.skssqq = b.skssqq
and a.skssqz = b.skssqz and A.zsxm_dm<>'10101'
and rownum=1),XGR_DM='00000000000',XGRQ=sysdate
where exists (select 1
FROM hx_zgxt.TMP_SB_SBXX b
WHERE a.djxh = b.djxh
and a.zsxm_dm = b.zsxm_dm
and a.zspm_dm = b.zspm_dm
and a.skssqq = b.skssqq
and a.skssqz = b.skssqz and A.zsxm_dm<>'10101'
) and NSSBRQ is null;
该SQL虽然避免了GC CURRENT REQUEST等待，但24小时仍然无法完成数据修改，出现大量 db file sequential read等待。
修改存储过程为批获取批绑定方式处理150W条记录4分钟完成，PGA使用3M内存：
create or replace PROCEDURE hx_zgxt.p_sb_yqwsb_dscl_new is
cursor cur_sb is
select NSSBRQ, djxh, zsxm_dm, zspm_dm, skssqq, skssqz
from SB_SBXX
where ZFBZ_1 = 'N'
AND (SBLX = '0' or SBLX is null)
and lrrq between to_date('sysdate - 20', 'yyyy-mm-dd') and
to_date('sysdate', 'yyyy-mm-dd');
type resultset is table of cur_sb%rowtype;
r resultset;
BEGIN
...
open cur_sb;
loop
fetch cur_sb bulk collect
into r limit 1000;
forall i in 1 .. r.count
update SB_YSBTJ a
set NSSBRQ = r(i).nssbrq, XGR_DM = '00000000000', XGRQ = sysdate
where a.djxh = r(i).djxh
and a.zsxm_dm = r(i).zsxm_dm
and a.zspm_dm = r(i).zspm_dm
and a.skssqq = r(i).skssqq
and a.skssqz = r(i).skssqz
and A.zsxm_dm <> '10101'
and NSSBRQ is null;
forall i in 1 .. r.count
update SB_YSBTJ a
set NSSBRQ = r(i).nssbrq, XGR_DM = '00000000000', XGRQ = sysdate
where a.djxh = r(i).djxh
and a.zsxm_dm = r(i).zsxm_dm
and a.skssqq = r(i).skssqq
and a.skssqz = r(i).skssqz
and A.zsxm_dm = '10101'
and NSSBRQ is null;
.....
exit when cur_sb%notfound;
end loop;
close cur_sb;
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23371754/viewspace-756863/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
将JOB固定到一个节点执行
请登录后发表评论
登录
全部评论
