pl/sql中三种游标循环效率对比

pl/sql中三种游标循环效率对比

 

这里主要对比以下三种格式的游标循环:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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种处理方式的本质。

 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

--创建测试大表

[sql] 

00:09:54 SCOTT@orcl>create tablebig_data as select 'Cc'||mod(level,8) a,'Dd'||  

mod(level,13) bfrom 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 frombig_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      exitwhen 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 proceduresuccessfully 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 frombig_data;  

00:19:56   4  

00:19:56   5  type t_ais tableof 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 collectinto v_a;  

00:19:56  11    close c_a;  

00:19:56  12  end;  

00:19:57  13  /  

    

PL/SQL proceduresuccessfully 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 proceduresuccessfully 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文件如下:(单条处理)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

PARSING INCURSOR #7 len=181 dep=0 uid=84 oct=47 lid=84 tim=1357453194221500 hv=4093379502 ad='3ab9f6ec'sqlid='3nz96vvtzs0xf'

declare

  cursorc_a is

    selecta from big_data;

  v_a big_data.a%type;

begin

  openc_a;

  loop

    fetchc_a into v_a;

    exitwhen c_a%notfound;

  endloop;

  closec_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 INCURSOR #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 INCURSOR #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文件如下:(批量处理)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

PARSING INCURSOR #5 len=182 dep=0 uid=84 oct=47 lid=84 tim=1357454222243170 hv=3525186369 ad='3aa08740'sqlid='fr3sb9r91w4u1'

declare

  cursorc_a is

    selecta from big_data;

type t_a istable of c_a%rowtype;

  v_a t_a;

begin

  openc_a;

  --?úá?′|àí

    fetchc_a bulk collect intov_a;

  closec_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 INCURSOR #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文件如下:(多条处理)

 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

763 PARSING IN CURSOR #6 len=105 dep=0 uid=84 oct=47 lid=84 tim=1357454481979282 hv=97100697 ad='3faaba00'sqlid='46bkjvc2wm8wt'

  764declare

  765   v_a big_data.a%type;

  766begin

  767  for x in(select a from big_data) loop

  768    v_a:=x.a;

  769end loop;

  770end;

  771END 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 PARSINGIN CURSOR#4 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357454481979809 hv=3992159408 ad='3aae4de0'sqlid='3w21sgzqz715h'

  775SELECT A FROMBIG_DATA

  776END 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

  778EXEC #4:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454481980067

  779FETCH #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.在不用使用到集合变量情况下(不使用BULK COLLECT时),速度也很快

 

 

http://www.2cto.com/database/201307/224636.html