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
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文件如下:(单条处理)
|
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
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文件如下:(批量处理)
|
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
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文件如下:(多条处理)
|
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'
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.在不用使用到集合变量情况下(不使用BULK COLLECT时),速度也很快
1246
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
