PL/SQL 批量Bind Forall 的性能表现

周三 AP 人员准备对一个千万级的Table A进行Update, 要求A表中，每一行的Column MO的值要和B表中的MO值想等，对应的逻辑是A.SN=B.SN, SN是Unique。

[@more@]

本来他们想一个SQL搞定（怎么可能…），后来觉得不可以，之后又来找我们商量要怎么做，因为毕竟是一个7X24的系统，CPU Loading比较重

最后确定的方案是用PL/SQL分批Update，同时用批量绑定的方式。

下面是当时作的测试对比。

测试平台是一套空载的10gR2 RAC，存储是ISCSI。

测试表COA_SN_D，是一个百万级的Table，作为A表；便于测试，提出其COA_SN和MO构造出测试表UPD_COA (B)，然后Update (B)的MO，得到新MO值。

本次测试选择更新26W 行数据。

以下是测试Procedure:

首先是传统Cursor做法：

declare

UPD TP.UPD_COA%rowtype;

CURSOR C1 is select COA_SN,MO from TP.UPD_COA;

T1 number(20);

T2 number(20);

begin

T1:=dbms_utility.get_cpu_time;

open c1;

fetch c1 into UPD;

WHILE c1%found Loop

update TP.COA_SN_D set TP.COA_SN_D.MO=UPD.MO

where TP.COA_SN_D.COA_SN=UPD.COA_SN;

fetch c1 into UPD;

end loop;

close c1;

T2:=dbms_utility.get_cpu_time;

DBMS_OUTPUT.put_line('Execution Time is '||to_char(T2-T1));

end;

时间是1499

批量Bind FORALL：

declare

type sn is table of TP.UPD_COA.COA_SN%TYPE;

sn1 sn;

T1 number(20);

T2 number(20);

begin

T1:=dbms_utility.get_cpu_time;

select COA_SN bulk collect into sn1 from TP.UPD_COA;

forall j in sn1.first..sn1.last

update TP.COA_SN_D set TP.COA_SN_D.MO=(select TP.UPD_COA.MO from TP.UPD_COA where TP.UPD_COA.COA_SN=sn1(j))

where TP.COA_SN_D.COA_SN=sn1(j);

T2:=dbms_utility.get_cpu_time;

DBMS_OUTPUT.put_line('CPU Time is '||to_char(T2-T1));

exception

when others then

DBMS_OUTPUT.put_line('UPDATE FAILED. Index='||SQL%BULK_EXCEPTIONS(1).ERROR_INDEX);

DBMS_OUTPUT.put_line(SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));

commit;

end;

时间是758.

减少了一半的CPU使用时间。在执行时间上也差了有一半。批量Bind在减少了PL/SQL引擎和SQL引擎之间的切换后，带来的收益非常可观。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10856805/viewspace-1018608/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10856805/viewspace-1018608/