pl/sql中的forall簡單測試

之前寫過一篇bulk collect的博文，只是對於bulk collect做了簡單的例項。http://blog.itpub.net/23718752/viewspace-1289696/
其實不光是bulk collect,forall對於pl/sql的效能的提升也是相當大的。
可以參見下面的兩個圖，可以看到其實在pl/sql中，可能很多時候我們所寫的pl/sql程式碼會在sql引擎和plsql引擎建進行上下文的切換，這個過程還是很耗費時間的。

而forall卻是相反，是提供一次上下文切換，會在forall的過程中進行資料的包裝處理。一次傳送給sql執行器去處理，大大減少了上下文切換時間。

對於此，可以想象，如果cursor中的結果集很龐大，就很可能進行大量的上下文切換，導致執行速度驟降。
我們來做一個簡單的例項來說明一下。

我們建立一個表test_data，裡面大概有7萬多的資料量。
n1@TEST11G> create table test_data as select *from all_objects;
Table created.

n1@TEST11G> select count(*)from test_data;
  COUNT(*)
----------
     71659
1 row selected

n1@TEST11G> create unique index inx_test_data_pk on test_data(object_id);
Index created.
Elapsed: 00:00:00.48

然後就開始執行儲存過程
[ora11g@oel1 plsql]$ cat a.sql
create or replace procedure test_proc as  
  cursor test_cur is select *from test_data;
  i number;
begin
  i:=1;
  for cur in test_cur
   loop
   update test_data set object_name=cur.object_name
where object_id=cur.object_id;
   dbms_output.put_line('this is a test');
   i:=i+1;
   end loop;
end;
/

exec test_proc;

執行的過程中會看到程式佔用了大量的cpu資源。可見進行了大量的上下文切換。其實一個主要的資訊點就是可以看到輸出了大量的日誌內容，最後還因為快取的原因退出了。

......
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "N1.TEST_PROC", line 10
ORA-06512: at line 1

Elapsed: 00:00:13.73

同樣的要求，如果使用forall的形式，使用的程式碼如下。
[ora11g@oel1 plsql]$ cat b.sql
create or replace procedure test_proc as  
  cursor test_cur is select *from test_data;
  type rec_type is table of test_cur%rowtype index by binary_integer;
  recs rec_type;
begin
  open test_cur;
  fetch test_cur bulk collect into recs;
  close test_cur;
  forall i in 1..recs.COUNT
   update test_data set object_name=recs(i).object_name
where object_id=recs(i).object_id;
   dbms_output.put_line('this is a test');
end;
/

這種效果就好得多，可以看到日誌中只輸出了一次日誌資訊，意味著只進行了一次上下文切換，這種方法明顯要好很多。
n1@TEST11G> exec test_proc;
this is a test
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.67
對於大批量的資料處理還是很值得推薦的。後續會使用dbms_profiler來對此測試一下，可以看出在一些實現點中還是存在著很大的不同。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1695028/，如需轉載，請註明出處，否則將追究法律責任。