Array Fetch Size研究.docx

1、Array Fetch Size研究Array Fetch Size研究测试环境 不同Array Fetch Size下的统计输出 查询的工作过程 Array Fetch Size对网络的影响 Array Fetch Size对consistent gets的影响 Array Fetch Size与竞争 Array Fetch Size与内存 总结 延伸阅读 Array Fetch Size这个参数决定了客户端一次从数据库获取数据的行数，写过访问Oracle数据库程序的人可能会发现Array Fetch Size在默认情况下设置的值都比较小，当他把Array Fetch Size设定值加大之后

2、，自己的程序明显的跑的更快了。既然越大Array Fetch Size对于程序运行越快，那为什么默认的Array Fetch Size值会那么小，多大的Array Fetch Size值才是个合适的呢？本文就试图探索下这方面的内容。top测试环境123456789ORAINSTorcl SELECT * FROM v$version;BANNER-Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - ProdPL/SQL Release 10.2.0.2.0 - ProductionCORE 10.2.0.2.0 Product

3、ionTNS for Linux: Version 10.2.0.2.0 - ProductionNLSRTL Version 10.2.0.2.0 - Production1234oracleorainstorcl:$ uname -aLinux 2.4.21-50a6smp #1 SMP Wed Oct 3 18:57:25 PDT 2007 i686 i686 i386 GNU/Linuxoracleorainstorcl:$ cat /etc/redhat-releaseRed Hat Enterprise Linux WS release 3 (Taroon Update 4)top

4、不同Array Fetch Size下的统计输出SQLPlus中可以自由的设定Array Fetch Size的大小，因此我们的实验都是基于SQLPlus来完成的，实际上不管是什么类型客户端还是自己基于OCI写程序，都是能单独设置Array Fetch Size的，SQLPlus设置方法为SET ARRAYSIZE number，默认情况下的Array Fetch Size大小为15，最大值允许设置的值是5000，可以通过SHOW ARRAYSIZE来查看当前使用的值。现在来建立一个测试表，然后设定不同Array Fetch Size，在观察不同情况下查询输出的统计数据有什么样的区别。首先建立

5、测试数据：1234567- 创建测试表ORAINSTorcl CREATE TABLE t AS SELECT * FROM all_objects;Table created.- 表建立完毕之后打开统计输出ORAINSTorcl SET AUTOT TRACE STATISTICSORAINSTorcl SET TIMING ON在表T上面我们没有建立任何的索引，现在我们查询表T中的一行数据，在查询一行数据时，查询的统计数据将不会受到Array Fetch Size设定值的影响，这种情况下的consistent gets可以认为是对表T做一次全表扫描所必须要读取的块的数量，统计输出结果如下：

6、12345678910111213141516ORAINSTorcl select * from t where object_id=100;Elapsed: 00:00:00.01Statistics-1 recursive calls0 db block gets147 consistent gets0 physical reads0 redo size1205 bytes sent via SQL*Net to client384 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sort

7、s (memory)0 sorts (disk)1 rows processed接着尝试不同Array Fetch Size值下的查询。Array Fetch Size为1时：1234567891011121314151617181920ORAINSTorcl SET ARRAYSIZE 1ORAINSTorcl SELECT * FROM T;11284 rows selected.Elapsed: 00:00:00.61Statistics-1 recursive calls0 db block gets5712 consistent gets0 physical reads0 redo

8、size1128755 bytes sent via SQL*Net to client62435 bytes received via SQL*Net from client5643 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)11284 rows processedArray Fetch Size为10时：1234567891011121314151617181920ORAINSTorcl SET ARRAYSIZE 10ORAINSTorcl SELECT * FROM T;11284 rows selec

9、ted.Elapsed: 00:00:00.30Statistics-0 recursive calls0 db block gets1257 consistent gets0 physical reads0 redo size555604 bytes sent via SQL*Net to client12792 bytes received via SQL*Net from client1130 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)11284 rows processedArray Fetch Siz

10、e为50时：1234567891011121314151617181920ORAINSTorcl SET ARRAYSIZE 50ORAINSTorcl SELECT * FROM T;11284 rows selected.Elapsed: 00:00:00.24Statistics-0 recursive calls0 db block gets369 consistent gets0 physical reads0 redo size440923 bytes sent via SQL*Net to client2859 bytes received via SQL*Net from cl

11、ient227 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)11284 rows processedArray Fetch Size为100时：1234567891011121314151617181920ORAINSTorcl SET ARRAYSIZE 100ORAINSTorcl SELECT * FROM T;11284 rows selected.Elapsed: 00:00:00.24Statistics-0 recursive calls0 db block gets258 consistent g

12、ets0 physical reads0 redo size426572 bytes sent via SQL*Net to client1616 bytes received via SQL*Net from client114 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)11284 rows processedArray Fetch Size为2500时：1234567891011121314151617181920ORAINSTorcl SET ARRAYSIZE 2500ORAINSTorcl SELEC

13、T * FROM T;11284 rows selected.Elapsed: 00:00:00.24Statistics-0 recursive calls0 db block gets151 consistent gets0 physical reads0 redo size412856 bytes sent via SQL*Net to client428 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)11284 rows pro

14、cessed为了对比的方便，我们将上面的数据汇总在下面的表格里面：Array Fetch Sizeconsistent getsSQL*Net roundtripsElapsed Time15712564300:00:00.61101257113000:00:00.305036922700:00:00.2410025811400:00:00.242500151600:00:00.24表1：不同Array Fetch Size下统计数据对比从上面的表中我们很容易就能看出来Array Fetch Size对于consistent gets, SQL*Net roundtrips, Elapsed

15、Time这三个参数的影响都还是比较大的。文章的后面几个部分就来一个个的分析这些参数都是怎么被影响的。top查询的工作过程由于Array Fetch Size的存在，客户端的动作可以分解成为下面的几个步骤：1. 向服务器请求Array Fetch Size行数据2. 等待服务器服务器响应（此时服务器准备相应的数据）3. 从网络接收服务器传送的数据4. 将接收的数据在本地保存5. 检查是否取完所有数据，如果没有则转向第1步，否则完成查询通过服务器端的10046 trace可以大概看到整个过程，现在来重复一下Array Fetch Size为1和100时的查询，并把查询产生的trace结果保存下来，

16、得到trace的相关语句如下：12345678sqlplus orainst/hello EOFSET AUTOT TRACE STATISTICSSET TIMING ONalter session set tracefile_identifier=arraysize01;alter session set events 10046 trace name context forever, level 12;SET ARRAYSIZE 1SELECT * FROM T;EOF类似的代码将SET ARRAYSIZE 1换成SET ARRAYSIZE 100再重复一次，这样可以在udump目录中看

17、到两个名字中包含arraysize的trace文件，下面是我的运行结果：12345oracleorainstorcl:/dumps-01/databases/orcl/udump$ll -htotal 1.8M-rw-r-r- 1 oracle dba 1.6M Mar 8 14:35 orcl_ora_27337_arraysize01.trc-rw-r-r- 1 oracle dba 64K Mar 8 14:35 orcl_ora_27341_arraysize100.trc打开其中的名字为orcl_ora_27337_arraysize01.trc的文件，可以看到类似下面的代码：123

18、4567891011121314151617PARSING IN CURSOR #6 len=15 dep=0 uid=36 oct=3 lid=36 tim=1238338811398144 hv=1406298530 ad=3b4835fcSELECT * FROM TEND OF STMTPARSE #6:c=0,e=25457,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1238338811398132BINDS #6:EXEC #6:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=12383388113

19、98383WAIT #6: nam=SQL*Net message to client ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811398436FETCH #6:c=0,e=141,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1238338811398635WAIT #6: nam=SQL*Net message from client ela= 537 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=123833881139924

20、2WAIT #6: nam=SQL*Net message to client ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811399324. 这里有几万条类似的记录 .FETCH #6:c=0,e=46,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,tim=1238338812564076WAIT #6: nam=SQL*Net message from client ela= 84 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=123833

21、8812564204WAIT #6: nam=SQL*Net message to client ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564252FETCH #6:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1238338812564291WAIT #6: nam=SQL*Net message from client ela= 378 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=12383388125

22、64744* SESSION ID:(30.30827) 2010-03-08 14:35:44.067上面记录中显示的每次的FETCH操作就是服务器端准备数据的时间，等待消息为”SQL*Net message to client”的WAIT行就是向客户端传送数据并等待响应的过程，在Array Fetch Size设置为100的那个trace文件中，我们还能看到类似于”SQL*Net more data to client”的等待时间，这个也是服务器还在向客户端传送数据的一个事件。在这里，每一次的FETCH操作都会对应一次“向服务器请求Array Fetch Size行数据”的操作（注意：当A

23、rray Fetch Size为1时SQLPlus会按照值为2的情况去取数据），只有最后一次的FETCH不一样，最后一次FECTH是为了确定已经没有数据可取的，因此在 表T一共有11284行记录，所以对应的FETCH操作就共有11284/2+1=5643次，而Array Fetch Size为100是FETCH的数量一共是roundup(11284/100)+1=114，这个数据我们可以通过运行下列命令看到：12# 这个命令会处理生成的全部包含array字串的trace文件$ for name in ls orcl*array*.*; do tkprof $name $name.txt; do

24、ne运行完了以后打开orcl_ora_27337_arraysize01.trc.txt可以看到下面的信息：123456789101112131415161718SELECT * FROM Tcall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.02 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 5643 0.21 0.25 0 5712 0 11284- - - - - - - -total 5645 0.21 0.28 0 5712 0 11284- 中间省略了一部分数据 -Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited- Waited - -SQL*Net message to client