Array Fetch Size这个参数决定了客户端一次从数据库获取数据的行数，写过访问Oracle数据库程序的人可能会发现Array Fetch Size在默认情况下设置的值都比较小，当他把Array Fetch Size设定值加大之后，自己的程序明显的跑的更快了。既然越大Array Fetch Size对于程序运行越快，那为什么默认的Array Fetch Size值会那么小，多大的Array Fetch Size值才是个合适的呢？本文就试图探索下这方面的内容。
| 1 | ORAINST@orcl> SELECT * FROM v$version; |
| 4 | ---------------------------------------------------------------- |
| 5 | Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod |
| 6 | PL/SQL Release 10.2.0.2.0 - Production |
| 7 | CORE 10.2.0.2.0 Production |
| 8 | TNS for Linux: Version 10.2.0.2.0 - Production |
| 9 | NLSRTL Version 10.2.0.2.0 - Production |
| 1 | oracle@orainst[orcl]:~$ uname -a |
| 2 | Linux orainst.desktop.mycompany.com 2.4.21-50a6smp #1 SMP Wed Oct 3 18:57:25 PDT 2007 i686 i686 i386 GNU/Linux |
| 3 | oracle@orainst[orcl]:~$ cat /etc/redhat-release |
| 4 | Red Hat Enterprise Linux WS release 3 (Taroon Update 4) |
top不同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，在观察不同情况下查询输出的统计数据有什么样的区别。
首先建立测试数据：
| 2 | ORAINST@orcl> CREATE TABLE t AS SELECT * FROM all_objects; |
| 6 | ORAINST@orcl> SET AUTOT TRACE STATISTICS |
| 7 | ORAINST@orcl> SET TIMING ON |
在表T上面我们没有建立任何的索引，现在我们查询表T中的一行数据，在查询一行数据时，查询的统计数据将不会受到Array Fetch Size设定值的影响，这种情况下的consistent gets可以认为是对表T做一次全表扫描所必须要读取的块的数量，统计输出结果如下：
| 1 | ORAINST@orcl> select * from t where object_id=100; |
| 5 | ---------------------------------------------------------- |
| 11 | 1205 bytes sent via SQL*Net to client |
| 12 | 384 bytes received via SQL*Net from client |
| 13 | 2 SQL*Net roundtrips to/from client |
接着尝试不同Array Fetch Size值下的查询。
Array Fetch Size为1时：
| 1 | ORAINST@orcl> SET ARRAYSIZE 1 |
| 2 | ORAINST@orcl> SELECT * FROM T; |
| 9 | ---------------------------------------------------------- |
| 15 | 1128755 bytes sent via SQL*Net to client |
| 16 | 62435 bytes received via SQL*Net from client |
| 17 | 5643 SQL*Net roundtrips to/from client |
Array Fetch Size为10时：
| 1 | ORAINST@orcl> SET ARRAYSIZE 10 |
| 2 | ORAINST@orcl> SELECT * FROM T; |
| 9 | ---------------------------------------------------------- |
| 15 | 555604 bytes sent via SQL*Net to client |
| 16 | 12792 bytes received via SQL*Net from client |
| 17 | 1130 SQL*Net roundtrips to/from client |
Array Fetch Size为50时：
| 1 | ORAINST@orcl> SET ARRAYSIZE 50 |
| 2 | ORAINST@orcl> SELECT * FROM T; |
| 9 | ---------------------------------------------------------- |
| 15 | 440923 bytes sent via SQL*Net to client |
| 16 | 2859 bytes received via SQL*Net from client |
| 17 | 227 SQL*Net roundtrips to/from client |
Array Fetch Size为100时：
| 1 | ORAINST@orcl> SET ARRAYSIZE 100 |
| 2 | ORAINST@orcl> SELECT * FROM T; |
| 9 | ---------------------------------------------------------- |
| 15 | 426572 bytes sent via SQL*Net to client |
| 16 | 1616 bytes received via SQL*Net from client |
| 17 | 114 SQL*Net roundtrips to/from client |
Array Fetch Size为2500时：
| 1 | ORAINST@orcl> SET ARRAYSIZE 2500 |
| 2 | ORAINST@orcl> SELECT * FROM T; |
| 9 | ---------------------------------------------------------- |
| 15 | 412856 bytes sent via SQL*Net to client |
| 16 | 428 bytes received via SQL*Net from client |
| 17 | 6 SQL*Net roundtrips to/from client |
为了对比的方便，我们将上面的数据汇总在下面的表格里面：
| Array Fetch Size | consistent gets | SQL*Net roundtrips | Elapsed Time |
| 1 | 5712 | 5643 | 00:00:00.61 |
| 10 | 1257 | 1130 | 00:00:00.30 |
| 50 | 369 | 227 | 00:00:00.24 |
| 100 | 258 | 114 | 00:00:00.24 |
| 2500 | 151 | 6 | 00:00:00.24 |
表1：不同Array Fetch Size下统计数据对比
从上面的表中我们很容易就能看出来Array Fetch Size对于consistent gets, SQL*Net roundtrips, Elapsed Time这三个参数的影响都还是比较大的。文章的后面几个部分就来一个个的分析这些参数都是怎么被影响的。
top查询的工作过程
由于Array Fetch Size的存在，客户端的动作可以分解成为下面的几个步骤：
- 向服务器请求Array Fetch Size行数据
- 等待服务器服务器响应（此时服务器准备相应的数据）
- 从网络接收服务器传送的数据
- 将接收的数据在本地保存
- 检查是否取完所有数据，如果没有则转向第1步，否则完成查询
通过服务器端的10046 trace可以大概看到整个过程，现在来重复一下Array Fetch Size为1和100时的查询，并把查询产生的trace结果保存下来，得到trace的相关语句如下：
| 1 | sqlplus "orainst/hello" < |
| 2 | SET AUTOT TRACE STATISTICS |
| 4 | alter session set tracefile_identifier='arraysize01'; |
| 5 | alter session set events '10046 trace name context forever, level 12'; |
类似的代码将SET ARRAYSIZE 1换成SET ARRAYSIZE 100再重复一次，这样可以在udump目录中看到两个名字中包含arraysize的trace文件，下面是我的运行结果：
| 1 | oracle@orainst[orcl]:/dumps-01/databases/orcl/udump |
| 4 | -rw-r--r-- 1 oracle dba 1.6M Mar 8 14:35 orcl_ora_27337_arraysize01.trc |
| 5 | -rw-r--r-- 1 oracle dba 64K Mar 8 14:35 orcl_ora_27341_arraysize100.trc |
打开其中的名字为orcl_ora_27337_arraysize01.trc的文件，可以看到类似下面的代码：
| 1 | PARSING IN CURSOR #6 len=15 dep=0 uid=36 ct=3 lid=36 tim=1238338811398144 hv=1406298530 ad='3b4835fc' |
| 4 | PARSE #6:c=0,e=25457,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1238338811398132 |
| 6 | EXEC #6:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1238338811398383 |
| 7 | WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811398436 |
| 8 | FETCH #6:c=0,e=141,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1238338811398635 |
| 9 | WAIT #6: nam='SQL*Net message from client' ela= 537 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811399242 |
| 10 | WAIT #6: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811399324 |
| 11 | ...... 这里有几万条类似的记录 ...... |
| 12 | FETCH #6:c=0,e=46,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,tim=1238338812564076 |
| 13 | WAIT #6: nam='SQL*Net message from client' ela= 84 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564204 |
| 14 | WAIT #6: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564252 |
| 15 | FETCH #6:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1238338812564291 |
| 16 | WAIT #6: nam='SQL*Net message from client' ela= 378 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564744 |
| 17 | *** 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行数据”的操作（注意：当Array Fetch Size为1时SQLPlus会按照值为2的情况去取数据），只有最后一次的FETCH不一样，最后一次FECTH是为了确定已经没有数据可取的，因此在表T一共有11284行记录，所以对应的FETCH操作就共有11284/2+1=5643次，而Array Fetch Size为100是FETCH的数量一共是roundup(11284/100)+1=114，这个数据我们可以通过运行下列命令看到：
| 1 | # 这个命令会处理生成的全部包含array字串的trace文件 |
| 2 | $ for name in `ls orcl*array*.*`; do tkprof $name $name.txt; done |
运行完了以后打开orcl_ora_27337_arraysize01.trc.txt可以看到下面的信息：
| 3 | call count cpu elapsed disk query current rows |
| 4 | ------- ------ -------- ---------- ---------- ---------- ---------- ---------- |
| 5 | Parse 1 0.00 0.02 0 0 0 0 |
| 6 | Execute 1 0.00 0.00 0 0 0 0 |
| 7 | Fetch 5643 0.21 0.25 0 5712 0 11284 |
| 8 | ------- ------ -------- ---------- ---------- ---------- ---------- ---------- |
| 9 | total 5645 0.21 0.28 0 5712 0 11284 |
| 13 | Elapsed times include waiting on following events: |
| 14 | Event waited on Times Max. Wait Total Waited |
| 15 | ---------------------------------------- Waited ---------- ------------ |
| 16 | SQL*Net message to client 5643 0.00 0.00 |
| 17 | SQL*Net message from client 5643 0.00 0.43 |
| 18 | ******************************************************************************** |
打开orcl_ora_27341_arraysize100.trc.txt可以看到下面的信息
| 3 | call count cpu elapsed disk query current rows |
| 4 | ------- ------ -------- ---------- ---------- ---------- ---------- ---------- |
| 5 | Parse 1 0.00 0.00 0 0 0 0 |
| 6 | Execute 1 0.00 0.00 0 0 0 0 |
| 7 | Fetch 114 0.04 0.03 0 258 0 11284 |
| 8 | ------- ------ -------- ---------- ---------- ---------- ---------- ---------- |
| 9 | total 116 0.04 0.03 0 258 0 11284 |
| 13 | Elapsed times include waiting on following events: |
| 14 | Event waited on Times Max. Wait Total Waited |
| 15 | ---------------------------------------- Waited ---------- ------------ |
| 16 | SQL*Net message to client 114 0.00 0.00 |
| 17 | SQL*Net message from client 114 0.00 0.20 |
| 18 | SQL*Net more data to client 151 0.00 0.00 |
| 19 | ******************************************************************************** |
从上面的统计记录可以看到Array Fetch Size设置越小时，客户端与服务器端交互的次数就越多，而网络交互的成本是很高的，这点同样可以通过上面数据中的等待事件的等待时间可以看出来，Array Fetch Size越大，用在”SQL*Net message to client”, “SQL*Net message from client”等这几个等待事件上面的时间就越短，当然这就是所看到文中第一部分表中所列出的”Elapsed Time”相差那么大的原因了。
topArray Fetch Size对网络的影响
这里先看看SQL*Net roundtrips这个统计值，这个统计值代表的意义就是在SQL*Net层面上，客户端和数据库服务器发生交互的次数。实际上也就是上面trace文件所计算出来的FETCH的次数。
从语句的运行时间上面来看，Array Fetch Size越大，客户端与服务器交互的次数就越少，SQL*Net roundtrips也就越小，但造成的问题就是服务器一次性向客户端传送的数据太大了。比如说我们定义了一个表中的每行数据大约是10k，如果将Array Fetch Size设置为1000的话，那么客户端和服务器一次交互就需要传送10k*1000等于大约就是10M的数据量，你可能会说这样不大啊，但是要考虑到网络中使用数据库的不只是一个机器，有可能是有很多运行同样程序的客户端在同时操作数据库，这样的话在网络上运行的数据就可能会达到一个很大的值。
一般来说我们的客户端和数据库服务器是不可能通过网线直接连接在一起的，它们之间经常会经过很多的路由器，路由器之间以及路由器和服务器之间通过双绞线或是光纤进行连接。当今这个时代已经是千兆以太网普及的时代了，双绞线或是光纤之类的传输介质不会成为网络中的瓶颈，真正的问题在于网络中的路由设备。熟悉TCP/IP协议的朋友应该知道，当在网络中传输一份大的数据的时候，这些数据通常是会被切分成一个个的小的数据包在网络中进行传输，路由器就负责顺序的将这些数据包从网络中的一点准确的发往另外一个点。另一个路由器在网络里面不仅仅处理两个节点之间的数据传输，而通常是需要处理很多节点之间的数据传输，但路由器的处理能力是有限的（这里的有限有两种情况，在内部以太网中，主要是因为数据量过大达到了路由器处理的极限，另外一种就是网络有速度的限制，只允许通过规定数据的数据包），路由器处理不过来的数据包就需要进行队列传输，这样的话肯定就会增加数据在网络中传输的时间，更可怕的是当路由器负载过重时还会造成丢包，为了保证数据的完整性，丢包的数据还需要重传，这无疑更是增加了数据在网络上消耗的时间，也就是会增加网络的延时。
因此我们设置Array Fetch Size值的时候需要结合实际的网络情况进行调整，不能一味的增加这个值，特别是在网络带宽比较小，或者是网络异常繁忙的情况下为了减少丢包和网络延时，Array Fetch Size应该尽可能的设置的小一点。
topArray Fetch Size对consistent gets的影响
Array Fetch Size会显著的影响consistent gets，这是一个很有意思的问题，不过真正问题的原因也很简单。
表T是从all_objects来的，通过对all_objects的各个字段进行计算科技估算出表T中每行的大小大约是93 bytes，而Oracle数据库中一般情况一个数据库大小是8k的，在这里一个数据块可以放下8*1024/96=84行数据。现在回顾一下服务器端处理查询的过程：当客户端请求10行数据的时候，服务器找到这10行记录所在的数据块，将10行数据读出来，然后暂停服务器端的读取，将这10行数据传送到客户端，确定传送完毕后再恢复读取进程去读取下10行数据，而因为一个数据块中可以保存多达84行的数据，如果每次只读取10行，那就有9次的与客户端交互的机会都会读取同一个数据块，很显然，我们读取的行数越小，那读取同一个数据块的机会就越多。
在实验的一开始，我们先跑了一个语句去对表T做一次全表扫描，读取一条记录，在那个语句的统计数据里面我们知道对表T进行一个全表扫描所产生的consistent gets是147。
当Array Fetch Size设置为1时，SQLPlus实际是按照Array Fetch Size为2读取数据的，统计结果得到查询产生的consistent gets是5712，与服务器交互的次数是5462+1次，我们可以计算出5712和5462+147是比较接近的。使用同样的方法我们也可以核算下Array Fetch Size为其他数值时consistent gets的大小和Array Fetch Size的变化关系。
但是，consistent gets会有重大的变化仅仅是针对表中行比较小的情况下而言的，如果数据表中的行非常大，比如说接近8k甚至超过8k时，我们即使把Array Fetch Size设置成一个极小的值，同一个数据块也极少被重复的读取的，这种情况可以通过创建一个大表做试验得出。
因此，Array Fetch Size的不同值只会在表行大小较小的情况下对consistent gets有影响，而且是行越小影响越大，即：行越小且Array Fetch Size越小时，consistent gets越大。
topArray Fetch Size与竞争
不同的Array Fetch Size设置对于数据库服务器还有一个重要的影响就是竞争了。当Array Fetch Size设置比较小的时候，客户端和服务器交互的次数就会很多，经常是在读取一小部分数据之后剩下的时间就是等待网络的交互，其中消耗在网络上面的时间会比较长，在这个等待网络的时间里面服务器还可以正常的进行其他方面的操作，即使数据库的并发量比较大也不会因为这个造成太明显的竞争问题。
但是当Array Fetch Size的设置值增加时，花费在服务器端准备数据的时间要比原来的长许多，同时消耗在网络等待上面的时间会短很多，也就是说同样是传输相同数量行的数据的全部传送时间里面，Array Fetch Size较大的总的传送时间短，但同时服务器也会看起来更忙，而Array Fetch Size小的总的传送时间长，但同时服务器看起来会更清闲些。所以当服务器的并发很大的时候，如果这时候Array Fetch Size也设置一个很大的值，这样无疑会加大服务器的负担，举个例子就是同一时间读取同一个表的机会增大了许多，这样就会造成资源竞争的问题。
因此在并发比较大的系统上，我们可以通过设置较小的Array Fetch Size来降低查询操作的速度，得到的好处就是减轻了我们服务器的负担。
（注：这部分内容停留在理论阶段，还没有设计出相应的实验进行验证。）
topArray Fetch Size与内存
在服务器端，因为数据是缓存在SGA中，而且服务器的内存通常也会比较大，因此Array Fetch Size对于服务器段内存不会有太大的影响。
但是在客户端就不一样的，当Array Fetch Size设置比较大而且数据行也比较大的时候，比如说Array Fetch Size为500，而数据行为20k每行，那这样一次就需要分配2M的内存供客户端获取数据，这在单个读取进程是可能不是问题，但是如果你的客户端是多个进程并行读的话，那可就要好好的考虑考虑了。
在设定Array Fetch Size大小的时候首先要考虑的两个问题是网络条件和服务器的并发。考虑网络条件是为了减少网络延时和丢包，最大可能的保证最高效的利用网络；考虑服务器的并发是要尽可能的减少服务器资源的竞争，加快服务器的响应速度。同时也要根据处理的表的行大小和客户端内存进行适当的调整。
通常Array Fetch Size的大小建议值是在50-500范围内，具体的必须要根据实际情况进行相应的调整。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8183550/viewspace-662226/，如需转载，请注明出处，否则将追究法律责任。
1181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
