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引擎之間的切換後，帶來的收益非常可觀。