CNDBA社区
我们知道，访问数据表可以通过普通的DQL语句，也可以在存储过程中使用游标。通过游标，可以逐行获取数据表的数据处理，确实相当灵活便利。在本篇文章，我们来探讨一下，通过游标访问一张数据表，与普通DQL方式访问，有什么差别，分析一下这其中的性能差异。
测试环境如下：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
测试使用的脚本：
DQL语句：
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid),t.name from node t where rownum<=40;
-----------------------------------------------------------------------------------------------------------------------------
PLSQL脚本：
declare
-- Local variables here
cursor x is select * from node where rownum<=40;
m x%rowtype;
begin
-- Test statements here
open x;
loop
fetch x into m;
exit when x%notfound;
null;
end loop;
end;
表node为一张只有200条记录的小表。测试过程是这样的：
1. 运行上面两个脚本时候打开10046，分别获取两个脚本运行时的trace文件;
2. 运行上面两个脚本的前后，采集相关资源使用指标，本次采集的是cache buffers chains的Latch。
然后对比输出的日志，分别对相关资源使用情况对比，来分析这两种方式的执行细节差异。为了使测试过程更加简单明了，本案例通过rownum<=40来限定只检索表的第一个数据块。
先来看看node表的第一个数据块在CBC中是受哪个cache buffers chains Latch管理的：
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from node t where rownum=1;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO(
------------------------------ ------------------------------
1868331 37
SQL> select hladdr from xmodel.X$BH where file#=37 and dbablk=1868331;
HLADDR
----------------
0000000701350E08
SQL> select hladdr,file#,dbablk from xmodel.X$BH where hladdr = hextoraw('0000000701350E08');
HLADDR FILE# DBABLK
---------------- ---------- ----------
0000000701350E08 37 1868331
1 rows selected
至此，我们已经获取到管理这个块的Latch 地址是0000000701350E08。而且该Latch也仅仅管理着一个数据块而已（这是因为测试库是一个空闲库的原因，正常运行的生产库一般都是一个Latch管理多个bucket）。所以该Latch的使用情况，只会受到这两个测试案例的影响，测试的准确性还是比较高的。
OK，Let's start. 先来采集一下第一个脚本：
SQL> select distinct sid from v$mystat;
SID
----------
194
SQL> select get_session_trace_name() from dual;
GET_SESSION_TRACE_NAME()
--------------------------------------------------------------------------------
/oravl01/oracle/diag/rdbms/gzdev00/GZDEV00/trace/GZDEV00_ora_40893.trc
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered
SQL> select name,gets from v$latch_children where addr = '0000000701350E08';
NAME GETS
---------------------------------------------------------------- ----------
cache buffers chains 3971
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid),t.name from node t where rownum<=40;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO( NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------
1868331 37 GX14800017340764
1868331 37 GX14800017340765
1868331 37 GX14800017340766
1868331 37 GX14800017340768
1868331 37 GX14800017340770
1868331 37 GX14800017340771
1868331 37 GX14800017340780
1868331 37 GX14800017340781
......
40 rows selected
SQL> select name,gets from v$latch_children where addr = '0000000701350E08';
NAME GETS
------------------------------- ----------
cache buffers chains 3974
可以看到，SQL的运行，总共get了 3974 - 3971 = 3次cache buffer chains Latch. 来看看日志文件：
PARSING IN CURSOR #140228851612784 len=118 dep=0 uid=89 oct=3 lid=89 tim=1479883400003183 hv=936671197 ad='72fcaf958' sqlid='fg15v9cvx8xyx'
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid),t.name from node t where rownum<=40
END OF STMT
PARSE #140228851612784:c=4999,e=7233,p=0,cr=20,cu=0,mis=1,r=0,dep=0,og=1,plh=1652634197,tim=1479883400003182
EXEC #140228851612784:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1652634197,tim=1479883400003321
WAIT #140228851612784: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=384 tim=1479883400003393
WAIT #140228851612784: nam='SQL*Net message from client' ela= 266737 driver id=1413697536 #bytes=1 p3=0 obj#=384 tim=1479883400270190
pin ktewh25: kteinicnt dba 0x95c822a:4 time 878825946
pin ktewh26: kteinpscan dba 0x95c822a:4 time 878825980
pin ktewh27: kteinmap dba 0x95c822a:4 time 878826000
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 878826039
WAIT #140228851612784: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=384 tim=1479883400270691
FETCH #140228851612784:c=1000,e=1288,p=0,cr=4,cu=0,mis=0,r=40,dep=0,og=1,plh=1652634197,tim=1479883400271567
STAT #140228851612784 id=1 cnt=40 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=4 pr=0 pw=0 time=171 us)'
STAT #140228851612784 id=2 cnt=40 pid=1 pos=1 obj=282487 op='TABLE ACCESS FULL NODE (cr=4 pr=0 pw=0 time=144 us cost=2 size=22800 card=200)'
从日志中看到（pin kdswh11: kdst_fetch dba 0x95c822b:1 time 878826039。0x95c822b 块地址就是上面所说的37号文件，1868331号块）服务进程仅仅对数据块pin住一次，然后就一次获取了块中所有满足条件的行记录（FETCH r=40）（pin ktewh25: kteinicnt dba 0x95c822a:4;pin ktewh26: kteinpscan dba 0x95c822a:4;pin ktewh27: kteinmap dba 0x95c822a:4 这几行表示对数据段node的段头块的pin,目的是想获取段的区分布与块分布，这个不是本次讨论的要点）.消耗的时间为 171 us.
好了，稍微总结一下本次DQL访问，所消耗的资源：
获取Latch 3次 + Pin住数据块一次 and 总的消耗时间 171 us
接下来，第二个脚本要上场了：
这个脚本我选择在PLSQL DEV的TEST窗口中运行，运行前同样也对Latch的情况采集了一下，顺便用oradebug方式跟踪TEST窗口的10046trace.
SQL> select name,gets from v$latch_children where addr = '0000000701350E08';
NAME GETS
---------------------------------------------------------------- ----------
cache buffers chains 3974
SQL> oradebug setospid 62061
Oracle pid: 31, Unix process pid: 62061, image: oracle@chlin001
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
TEST窗体运行脚本：
declare
-- Local variables here
cursor x is select * from node where rownum<=40;
m x%rowtype;
begin
-- Test statements here
open x;
loop
fetch x into m;
exit when x%notfound;
null;
end loop;
end;
执行完毕后再采集一次Latch:
SQL> select name,gets from v$latch_children where addr = '0000000701350E08';
NAME GETS
--------------------------------------------- ----------
cache buffers chains 4058
可以看到，PLSQL的运行，总共get了 4058 - 3974 = 84 次cache buffer chains Latch. 来看看日志文件：
PARSING IN CURSOR #139924015154576 len=35 dep=1 uid=89 oct=3 lid=89 tim=1479886002963152 hv=283386848 ad='723e1fe58' sqlid='2jqgnps8f88z0'
SELECT * FROM NODE WHERE ROWNUM<=40
END OF STMT
PARSE #139924015154576:c=4000,e=4395,p=0,cr=20,cu=0,mis=1,r=0,dep=1,og=1,plh=1652634197,tim=1479886002963150
EXEC #139924015154576:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1652634197,tim=1479886002963301
pin ktewh25: kteinicnt dba 0x95c822a:4 time 3481519099
pin ktewh26: kteinpscan dba 0x95c822a:4 time 3481519141
pin ktewh27: kteinmap dba 0x95c822a:4 time 3481519160
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519191
FETCH #139924015154576:c=0,e=161,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002963585
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519406
FETCH #139924015154576:c=0,e=60,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002963810
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519558
FETCH #139924015154576:c=0,e=69,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002963970
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519702
FETCH #139924015154576:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964092
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519799
FETCH #139924015154576:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964186
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481519914
FETCH #139924015154576:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964301
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520006
FETCH #139924015154576:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964393
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520098
FETCH #139924015154576:c=0,e=38,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964483
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520185
FETCH #139924015154576:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964582
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520294
FETCH #139924015154576:c=0,e=65,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964699
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520448
FETCH #139924015154576:c=0,e=62,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964851
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520564
FETCH #139924015154576:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002964952
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520658
FETCH #139924015154576:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965043
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520745
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965130
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520833
FETCH #139924015154576:c=999,e=66,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965245
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481520995
FETCH #139924015154576:c=0,e=70,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965402
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521133
FETCH #139924015154576:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965521
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521224
FETCH #139924015154576:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965610
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521320
FETCH #139924015154576:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965706
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521409
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965794
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521497
FETCH #139924015154576:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965883
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521587
FETCH #139924015154576:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002965974
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521676
FETCH #139924015154576:c=0,e=45,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966068
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521816
FETCH #139924015154576:c=1000,e=80,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966239
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481521976
FETCH #139924015154576:c=0,e=45,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966363
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522067
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966453
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522157
FETCH #139924015154576:c=0,e=38,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966542
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522246
FETCH #139924015154576:c=0,e=44,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966634
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522335
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966721
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522464
FETCH #139924015154576:c=0,e=66,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966872
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522606
FETCH #139924015154576:c=0,e=47,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002966994
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522700
FETCH #139924015154576:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967087
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522790
FETCH #139924015154576:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967178
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481522893
FETCH #139924015154576:c=0,e=52,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967289
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523007
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967393
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523096
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967482
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523188
FETCH #139924015154576:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967574
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523275
FETCH #139924015154576:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967661
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523369
FETCH #139924015154576:c=0,e=65,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967777
pin kdswh11: kdst_fetch dba 0x95c822b:1 time 3481523529
FETCH #139924015154576:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=1652634197,tim=1479886002967925
FETCH #139924015154576:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1652634197,tim=1479886002967986
STAT #139924015154576 id=1 cnt=40 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=43 pr=0 pw=0 time=1524 us)'
STAT #139924015154576 id=2 cnt=40 pid=1 pos=1 obj=282487 op='TABLE ACCESS FULL NODE (cr=43 pr=0 pw=0 time=1468 us cost=2 size=1346400 card=200)'
CLOSE #139924015154576:c=0,e=2,dep=1,type=3,tim=1479886002968134
从日志中看到,服务进程对数据块0x95c822b一共Pin住了40次，也就是说每次游标执行fetch x into m时,服务进程就Pin住一次数据块,然后获取数据文件里面的一行记录.一共40条记录，因此Pin & Fetch 40次。消耗的时间为 1521 us.
OK，总结一下本次PLSQL访问，所消耗的资源：
获取Latch 84次 + Pin住数据块40次 and 总的消耗时间 1521 us
对比这两个脚本的执行结果，从性能上看，高下立判。 前者仅仅需要获取3次 cache buffer chain latch, 1次pin住块。 后者需要获取84次Latch,40次pin住数据块。执行时间的对比也差别相当的大，前者仅仅需要171 us,后者需要1521 us. 这还是在测试环境得到的结果，换做是并发量比较大的生产环境，latch争用和 cache buffer handle争用比较严重的时候，每次获取latch与pin的时间必然是超过线性的时间增加的情况下，这两种方式得到的性能差异，比起本次案例的结果，应该大得多。
当然，情况也不总是像上面那么坏，游标每次获取下一行的时候，都去获取latch,然后遍历cbc,pin住数据块，获取数据行，额外做了这么多工作。oracle 11g版本后，新增了大表直接读功能(direct path read),如果oracle认为表达到大表的条件后，全表扫描方式从磁盘读数据块，会绕过buffer cache,将块缓存直接读进PGA。这种情况下通过游标去访问数据行的时候，不用再考虑并发访问机制，只需要只需要不断的fetch数据行即可(也是每次fetch一行，有多少记录符合就fetch多少次)，不需要获取latch,不需要考虑pin.
优化方案: 使用 FETCH BULK COLLECT 代替 FETCH. 就是使用批量获取代替单行逐一获取。网上也有很多文章介绍过这个方案，不过对于这个方案的解释，几乎全部人都认为是减少了PLSQL引擎与SQL引擎之间的切换，从而提升了性能。实际上还有另外一个原因，我认为是上面的结论。
对于该方案的实验案例，以后再列举分析，限于篇幅，这里不再说明。
版权声明：本文为博主原创文章，未经博主允许不得转载。
SQL优化 PLSQL引擎 SQL引擎
