pl/sql中三種遊標迴圈效率對比

這裡主要對比以下三種格式的遊標迴圈:

1.單條處理

open 遊標;
LOOP  
FETCH 遊標 INTO 變數;
EXIT WHEN  條件;
END LOOP;
CLOSE 遊標;

2.批量處理

open 遊標;
FETCH 遊標 BULK COLLECT INTO 集合變數;
CLOSE 遊標;

3.隱式遊標

for x in (sql語句) loop
...--邏輯處理
end loop;

以上為工作中常見的幾種遊標處理方式,一般來說批量處理的速度要最好,隱式遊標的次之,單條處理的最差,但是在我的實際工作中發現大部分使用的還是第一種遊標處理。

歸其原因竟是對集合變數及批量處理的效率等問題不瞭解所致。

這裡簡單的測試一下以上三種遊標的效率,並分析trace檔案來檢視這3種處理方式的本質。

--建立測試大表
[sql] 
00:09:54 SCOTT@orcl> create table big_data as select 'Cc'||mod(level,8) a,'Dd'||  
mod(level,13) b from dual connect by level<1000000;

Table created.  

Elapsed: 00:00:05.87  
00:11:17 SCOTT@orcl> select count(*) from big_data;  

  COUNT(*)  
----------  
    999999  

1 row selected.  

Elapsed: 00:00:00.07  

--分別執行以上三種方式的遊標處理的plsql塊
[sql] 
00:11:21 SCOTT@orcl> declare 
00:17:54   2    cursor c_a is 
00:17:54   3      select a from big_data;  
00:17:54   4  
00:17:54   5    v_a big_data.a%type;  
00:17:54   6  begin 
00:17:54   7    open c_a;  
00:17:54   8    loop  
00:17:54   9      fetch c_a into v_a;  
00:17:54  10      exit when c_a%notfound;  
00:17:54  11    end loop;  
00:17:54  12    close c_a;  
00:17:54  13  end;  
00:17:56  14  /  

PL/SQL procedure successfully completed.  

Elapsed: 00:00:07.42 


00:18:05 SCOTT@orcl> declare 
00:19:56   2    cursor c_a is 
00:19:56   3      select a from big_data;  
00:19:56   4  
00:19:56   5  type t_a is table of c_a%rowtype;  
00:19:56   6    v_a t_a;  
00:19:56   7  begin 
00:19:56   8    open c_a;  
00:19:56   9    --批量處理  
00:19:56  10      fetch c_a bulk collect into v_a;  
00:19:56  11    close c_a;  
00:19:56  12  end;  
00:19:57  13  /  

PL/SQL procedure successfully completed.  

Elapsed: 00:00:00.64 

00:22:55 SCOTT@orcl> declare 
00:23:18   2    v_a big_data.a%type;  
00:23:18   3    begin 
00:23:18   4    --批量處理  
00:23:18   5    for x in (select a from big_data) loop  
00:23:18   6      v_a:=x.a;  
00:23:18   7    end loop;  
00:23:18   8  end;  
00:23:18   9  /  

PL/SQL procedure successfully completed.  

Elapsed: 00:00:00.79

注意對比消耗時間,1為7.42s, 2為0.64s, 3為0.79s

在執行pl/sql塊之前,需要執行語句: alter session set sql_trace=true;

以便之後檢視trace檔案.

第一個遊標方式的trace檔案如下:(單條處理)

PARSING IN CURSOR #7 len=181 dep=0 uid=84 oct=47 lid=84 tim=1357453194221500 hv=4093379502 ad='3ab9f6ec' sqlid='3nz96vvtzs0xf'
declare
  cursor c_a is
    select a from big_data;
  v_a big_data.a%type;
begin
  open c_a;
  loop
    fetch c_a into v_a;
    exit when c_a%notfound;
  end loop;
  close c_a;
end;
END OF STMT
PARSE #7:c=7998,e=8406,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357453194221495
=====================
PARSING IN CURSOR #4 len=444 dep=2 uid=84 oct=3 lid=84 tim=1357453194225811 hv=1611503607 ad='3ab64c10' sqlid='c7tu1h9h0v5zr'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("BIG_DATA") FULL("BIG_DATA") NO_PARALLEL_INDEX("BIG_DATA") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "BIG_DATA" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "BIG_DATA") SAMPLESUB
END OF STMT
PARSE #4:c=2000,e=1958,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1357453194225807
*** 2013-01-06 14:19:54.284
EXEC #4:c=3998,e=58289,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=3098652591,tim=1357453194284371
FETCH #4:c=18997,e=19074,p=0,cr=55,cu=0,mis=0,r=1,dep=2,og=1,plh=3098652591,tim=1357453194303593
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=55 pr=0 pw=0 time=0 us)'
STAT #4 id=2 cnt=27300 pid=1 pos=1 obj=75053 op='TABLE ACCESS SAMPLE BIG_DATA (cr=55 pr=0 pw=0 time=130371 us cost=19 size=61752 card=5146)'
CLOSE #4:c=0,e=86,dep=2,type=0,tim=1357453194318217
=====================
PARSING IN CURSOR #6 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357453194318768 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'
SELECT A FROM BIG_DATA
END OF STMT
PARSE #6:c=28995,e=96556,p=0,cr=56,cu=0,mis=1,r=0,dep=1,og=1,plh=3104650627,tim=1357453194318766
EXEC #6:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357453194318875
FETCH #6:c=0,e=405,p=20,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319360
FETCH #6:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319425
FETCH #6:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319463
FETCH #6:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319496
FETCH #6:c=0,e=7,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319531
FETCH #6:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319564
...
1000108 FETCH #6:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357453214142218
1000109 STAT #6 id=1 cnt=999999 pid=0 pos=1 obj=75053 op='TABLE ACCESS FULL BIG_DATA (cr=1000002 pr=1832 pw=0 time=2281997 us cost=512 size=18637659 card=810333)'
1000110 CLOSE #6:c=0,e=1,dep=1,type=3,tim=1357453214142317
1000111 EXEC #7:c=19290067,e=19920346,p=1832,cr=1000058,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1357453214142338
1000112 =====================

其中SELECT /* OPT_DYN_SAMP */這個大sql為CBO的動態取樣SQL.這裡也耗費了一些CPU time(即c的值).

我們發現大概有100多萬的FETCH語句在trace中,也就是一條條的處理的,最終耗費的cpu time高達19290067,顯然這種遊標處理的效率是極其低下的.(尤其很多開發人員還喜歡對此類遊標加鎖後,單條處理,效率之低,不敢想象)

第二個遊標方式的trace檔案如下:(批量處理)

PARSING IN CURSOR #5 len=182 dep=0 uid=84 oct=47 lid=84 tim=1357454222243170 hv=3525186369 ad='3aa08740' sqlid='fr3sb9r91w4u1'
declare
  cursor c_a is
    select a from big_data;
type t_a is table of c_a%rowtype;
  v_a t_a;
begin
  open c_a;
  --?úá?′|àí
    fetch c_a bulk collect into v_a;
  close c_a;
end;
END OF STMT
PARSE #5:c=47993,e=48253,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357454222243163
=====================
PARSING IN CURSOR #7 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357454222243720 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'
SELECT A FROM BIG_DATA
END OF STMT
PARSE #7:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454222243719
EXEC #7:c=1000,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454222243839
*** 2013-01-06 14:37:02.816
FETCH #7:c=572913,e=572454,p=1832,cr=1835,cu=0,mis=0,r=999999,dep=1,og=1,plh=3104650627,tim=1357454222816387
STAT #7 id=1 cnt=999999 pid=0 pos=1 obj=75053 op='TABLE ACCESS FULL BIG_DATA (cr=1835 pr=1832 pw=0 time=633174 us cost=512 size=18637659 card=810333)'
CLOSE #7:c=0,e=2,dep=1,type=3,tim=1357454222816543
EXEC #5:c=586911,e=586709,p=1832,cr=1835,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1357454222830293

其中的亂碼為註釋的中文字元.

使用BULK COLLECT 批量處理的方式,顯然要快了許多.我們可以看到,它是先執行遊標語句SELECT A FROM BIG_DATA,然後一次FETCH出來.一次處理999999行.

第三個遊標方式的trace檔案如下:(多條處理)

763 PARSING IN CURSOR #6 len=105 dep=0 uid=84 oct=47 lid=84 tim=1357454481979282 hv=97100697 ad='3faaba00' sqlid='46bkjvc2wm8wt'
  764 declare
  765   v_a big_data.a%type;
  766 begin
  767   for x in (select a from big_data) loop
  768    v_a:=x.a;
  769 end loop;
  770 end;
  771 END OF STMT
  772 PARSE #6:c=9998,e=10050,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357454481979278
  773 =====================
  774 PARSING IN CURSOR #4 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357454481979809 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'
  775 SELECT A FROM BIG_DATA
  776 END OF STMT
  777 PARSE #4:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454481979806
  778 EXEC #4:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454481980067
  779 FETCH #4:c=1000,e=1012,p=20,cr=4,cu=0,mis=0,r=100,dep=1,og=1,plh=3104650627,tim=1357454481981179
  ...
10778 FETCH #4:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=99,dep=1,og=1,plh=3104650627,tim=1357454482759857
10779 CLOSE #4:c=0,e=2,dep=1,type=3,tim=1357454482759906
10780 EXEC #6:c=780882,e=780310,p=1832,cr=11798,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1357454482759962

可以看到這種處理方式的CPU time和第二種還是很接近的,只差了一個數量級,這種隱式迴圈的遊標語句,其實也是一種批量處理的過程,它每次讀取了多行資料到快取.

我們可以看到總的FETCH次數只有1萬多一點,比第一種的100多萬整整降低了100倍.

通過FETCH行中的r值我們可以看到,每次取的是近100行資料,可見這種隱式遊標迴圈也是一種批量處理的過程.

個人一般情況下喜歡第三種方式的遊標處理方式,原因有2點:

• 1,程式碼簡短,省卻了遊標變數的定義;
• 2.在不用使用到集合變數情況下(不使用BULK COLLECT時),速度也很快