arraysize对TFS和IFFS的影响
作者：kevin zheng | 分类： 大话技术 | 标签： Sql tuning, 大话技术 | 日期：2008-12-13
今天发现一个问题，对一个索引执行index fast full scan 的时候，如果在这之前进行了排序，逻辑读会少很多：
例子：
select id from A ;
xecution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=275240 Bytes=1100960)
1 0 INDEX (FAST FULL SCAN) OF ‘PK_T’ (NON-UNIQUE) (Cost=62 Card=275240 Bytes=1100960)
Statistics
———————————————————-
0 recursive calls
0 db block gets
3326 consistent gets
0 physical reads
0 redo size
1955968 bytes sent via SQL*Net to client
30397 bytes received via SQL*Net from client
2720 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
271834 rows processed
select id from t order by id;
271834 rows selected.
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=597 Card=275240 Bytes=1100960)
1 0 SORT (ORDER BY) (Cost=597 Card=275240 Bytes=1100960)
2 1 INDEX (FAST FULL SCAN) OF ‘PK_T’ (NON-UNIQUE) (Cost=62 Card=275240 Bytes=1100960)
Statistics
———————————————————-
0 recursive calls
0 db block gets
612 consistent gets
0 physical reads
0 redo size
1955968 bytes sent via SQL*Net to client
30397 bytes received via SQL*Net from client
2720 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
271834 rows processed
同样是index ffs,但是sort之后逻辑读和不sort逻辑读相差不是一个数量级。
一开始的猜想：可能是因为表上面有DML，一致读造成的。但重新建了一个表，建上索引之后，情况还是一样的。
进而猜想到可能是数据读取，返回客户端时候的造成的差异，做了个10046 level 12的trace
可以看到，两次查询的fetch次数是一样的，都是2720次，仔细一看，
select id from t order by id;这个查询中，大部分的fetch cr=0,而在
select id from t ;查询中，没有一个fetch cr=0
截取一段简化了的 trace：
（select id from t ）部分
FETCH #1:c=0,e=622,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281824136974
FETCH #1:c=0,e=623,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281824186030
FETCH #1:c=0,e=623,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281824188809
（select id from t ）部分
FETCH #1:c=0,e=628,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281862908131
FETCH #1:c=0,e=627,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281862910758
FETCH #1:c=0,e=626,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281862959433
FETCH #1:c=0,e=628,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=4,tim=1200281862962188
从这里我们可以看出，当不排序的时候，大部分的fetch不产生逻辑读。为什么呢？
我们知道，数据排序的时候，是需要读到PGA的work area里面进行排序的，然后返回给客户端。
因此，逻辑读产生是从data buffer cache读数据到PGA部分，如果我们不排序的话，那逻辑读产生是从data buffer cache读取，返回client
而在PGA里面，数据的存在形式和data buffer cache里存在的形式应该是有差异的。
所以我们认为，造成两条SQL的逻辑读差异的原因，应该是SQLPLUS断点的关系。我们可以看到，每次fetch的r=100。
kevin@CRMCN>show arraysize
arraysize 100
这个说明，oracle会每次取100行数据返回给我们，再回到data buffer cache里block的对应位置读取数据,这样势必会造成一个块多次读取的情况，
导致逻辑读的增加。
我们可以论证这一点；
表的行数：271834 rows selected.
每次fetch 100行，
271834/100=2718 相当于当arraysize=100的时候，我要把全部的数据返回到客户端需要2718次，每次一个逻辑读
612 consistent gets+2718-1=3329 和3326 consistent gets 差不多很接近了
select blocks from dba_segments where segment_name=’PK_T’;
BLOCKS
———-
640
索引有640块，index ffs的逻辑读612，差不多1块一个逻辑读。
我们猜想：如果把arraysize调得足够大的话，那这两条SQL的逻辑读应该是一致的，
kevin@CRMCN>set arraysize 100000
SP2-0267: arraysize option 100000 out of range (1 through 5000)
kevin@CRMCN>set arraysize 5000
kevin@CRMCN>set autot traceonly
kevin@CRMCN>select id from t;
271834 rows selected.
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=275240 Bytes=1100960)
1 0 INDEX (FAST FULL SCAN) OF ‘PK_T’ (NON-UNIQUE) (Cost=62 Card=275240 Bytes=1100960)
Statistics
———————————————————-
0 recursive calls
0 db block gets
667 consistent gets
0 physical reads
0 sorts (memory)
kevin@CRMCN>select id from t order by id;
271834 rows selected.
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=597 Card=275240 Bytes=1100960)
1 0 SORT (ORDER BY) (Cost=597 Card=275240 Bytes=1100960)
2 1 INDEX (FAST FULL SCAN) OF ‘PK_T’ (NON-UNIQUE) (Cost=62 Card=275240 Bytes=1100960)
Statistics
———————————————————-
0 recursive calls
0 db block gets
612 consistent gets
0 physical reads
1 sorts (memory)
667-612=55
271834/5000=55
看来logical reads确实和arraysize成正比
参考：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514
给返回行数特别多的SQL，先排序后再返回，确实可以避免ora-1555错误
玉面飞龙 @ December 14, 2008 |
写的很清晰
sky @ December 17, 2008 |