Array Fetch Size研究Word下载.docx

1、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 ProductionTNS for Linux: Version 10.2.0.2.0 - ProductionNLSRTL Version 10.2.0.2.0 - Productionoracleorainstorcl:$ uname -aLinux 2.4.21-50a6smp #1 SMP Wed Oct 3 18:57:25 PDT 20

2、07 i686 i686 i386 GNU/Linux$ cat /etc/redhat-releaseRed 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大小

3、为15，最大值允许设置的值是5000，可以通过SHOW ARRAYSIZE来查看当前使用的值。现在来建立一个测试表，然后设定不同Array Fetch Size，在观察不同情况下查询输出的统计数据有什么样的区别。首先建立测试数据：- 创建测试表 CREATE TABLE t AS SELECT * FROM all_objects;Table created.- 表建立完毕之后打开统计输出 SET AUTOT TRACE STATISTICS SET TIMING ON在表T上面我们没有建立任何的索引，现在我们查询表T中的一行数据，在查询一行数据时，查询的统计数据将不会受到Array Fetc

4、h Size设定值的影响，这种情况下的consistent gets可以认为是对表T做一次全表扫描所必须要读取的块的数量，统计输出结果如下：10111213141516 select * from t where object_id=100;Elapsed: 00:00:00.01Statistics-1 recursive calls0 db block gets147 consistent gets physical reads redo size1205 bytes sent via SQL*Net to client384 bytes received via SQL*Net from

5、 client2 SQL*Net roundtrips to/from client sorts （memory） sorts （disk） rows processed接着尝试不同Array Fetch Size值下的查询。Array Fetch Size为1时：17181920 SET ARRAYSIZE 1 SELECT * FROM T;11284 rows selected.00.615712112875562435564311284Array Fetch Size为10时： SET ARRAYSIZE 1000.301257555604127921130Array Fetch Si

6、ze为50时： SET ARRAYSIZE 5000.243694409232859227Array Fetch Size为100时： SET ARRAYSIZE 1002584265721616114Array Fetch Size为2500时： SET ARRAYSIZE 25001514128564286为了对比的方便，我们将上面的数据汇总在下面的表格里面：Array Fetch Sizeconsistent getsSQL*Net roundtripsElapsed Time5712564312571130503692271002581142500151表1：不同Array Fetch

7、 Size下统计数据对比从上面的表中我们很容易就能看出来Array Fetch Size对于consistent gets, SQL*Net roundtrips, Elapsed Time这三个参数的影响都还是比较大的。文章的后面几个部分就来一个个的分析这些参数都是怎么被影响的。top查询的工作过程由于Array Fetch Size的存在，客户端的动作可以分解成为下面的几个步骤：1. 向服务器请求Array Fetch Size行数据2. 等待服务器服务器响应（此时服务器准备相应的数据）3. 从网络接收服务器传送的数据4. 将接收的数据在本地保存5. 检查是否取完所有数据，如果没有则转向第

8、1步，否则完成查询通过服务器端的10046 trace可以大概看到整个过程，现在来重复一下Array Fetch Size为1和100时的查询，并把查询产生的trace结果保存下来，得到trace的相关语句如下：sqlplus orainst/hello EOFSET AUTOT TRACE STATISTICSSET TIMING ONalter session set tracefile_identifier=arraysize01;alter session set events 10046 trace name context forever, level 12SET ARRAYSIZ

9、E 1SELECT * FROM T;类似的代码将SET ARRAYSIZE 1换成SET ARRAYSIZE 100再重复一次，这样可以在udump目录中看到两个名字中包含arraysize的trace文件，下面是我的运行结果：/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 64K Mar35 orcl_ora_27341_arraysize100.trc打开其中的名字为orcl_ora_27337_array

10、size01.trc的文件，可以看到类似下面的代码：PARSING 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=1238338

11、811398383WAIT #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=1238338811398635SQL*Net message from client ela= 537 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811399242 ela= 1

12、driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338811399324. 这里有几万条类似的记录 .c=0,e=46,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,tim=1238338812564076 ela= 84 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564204 ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564252c=0,e=48,p=0,cr

13、=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1238338812564291 ela= 378 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1238338812564744* SESSION ID:（30.30827） 2010-03-08 14:35:44.067上面记录中显示的每次的FETCH操作就是服务器端准备数据的时间，等待消息为”SQL*Net message to client”的WAIT行就是向客户端传送数据并等待响应的过程，在Array Fetch Size设置为100的那个trace文件中，我们还能看到类

14、似于”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=

15、114，这个数据我们可以通过运行下列命令看到：# 这个命令会处理生成的全部包含array字串的trace文件$ for name in ls orcl*array*.*; do tkprof $name $name.txt; done运行完了以后打开orcl_ora_27337_arraysize01.trc.txt可以看到下面的信息：call count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.02 0 0ExecuteFetch 5643 0.21 0.25 5712 11284total 5645 0.28- 中间省略了一部分数据 -Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited- Waited - -SQL*Net message to client