PL/SQL中的forall簡單測試

PL/SQL中的forall簡單測試

之前寫過一篇bulk collect的文章，只是對於bulk collect做了簡單的執行個體。
其實不光是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來對此測試一下，可以看出在一些實現點中還是存在著很大的不同。

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享