pl/sql中三种游标循环效率对比(二)
=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 批量处理的方式,