Can multiblock-read span across extents 多数据块读能跨盘区吗

Can multiblock-read span across extents?
多数据块读能跨盘区吗?

通过测试展示了多数据块不能跨盘区, 但同时发现有时读取的块的数量不规则,不等于db_file_multiblock_read_count大小, 不知道为何



提问: 这个应该有影响因素吧？
如果我将db_file_multiblock_read_count设为64，一次读取64块，将近1m的数据，表的extent大小为65k，那么如果这16个extent都是一个表数据，全表扫描是否16个extent一次性读取；
而当这16个extent包含多个表数据并且互相交叉，是否要多次访问才能将某个表数据都取出：

回答: 多数据块读不能跨盘区, 即最多读取的数量等于: db_file_multiblock_read_count和extent两者取一个最大值(更正:应该是小)

提问: 我得学习学习了，需要确认一下你说的到底有没有问题，没有骗人吧？如果extent只有128k，那么db_file_multiblock_read_count设置再大也没有用？在哪记载？

回答: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm
10.3.4 db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'.


等我有时间再做个实验证实一下!


1. 补充说明

实际读取的大小还受操作系统的限制

DB_FILE_MULTIBLOCK_READ_COUNT
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

25 Using Parallel Execution - DB_FILE_MULTIBLOCK_READ_COUNT
This parameter determines how many database blocks are read with a single operating system READ call. The upper limit for this parameter is platform-dependent. If you set DB_FILE_MULTIBLOCK_READ_COUNT to an excessively high value, your operating system will lower the value to the highest allowable level when you start your database. In this case, each platform uses the highest value possible. Maximum values generally range from 64 KB to 1 MB.

WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
Oracle's ability to read multiple blocks is limited by the operating system's upper limit on the number of bytes which can be read in a single I/O call (MAX_IO_SIZE).
受到MAX_IO_SIZE参数的限制, 此参数跟操作系统相关


2. 多数据块读是否能跨盘区

参考资料1
Oracle wait interface By Richmond Shee, Kirtikumar Deshpande, K. Gopalakrishnan - Why Dose a Full Scan Operation Request Fewer Blocks than the MBRC
If you monitor a full table scan operation closely by repeatedly querying the V$SESSION_WAIT view in quick successions or by tracing the session with trace event 10046, you may see some db file scattered read events that request fewer blocks than the MBRC. This irregularity is due to any of the following reasons:
如果监视一个全表扫描(full table scan)时, 很快的连续的查询V$SESSION_WAIT视图, 或设置10046跟踪事件, 就会发现有些数据文件分散读(db file scattered read)事件请求的数据块数小于多数据块读的数量(MBRC). 这种不和常规的情况的发生是因为下面几种原因:
* The last set of blocks in an extents is less than the MBRC. If the MBRC is set to 8 and every extent has 10 blocks, Oracle will issue two multiblock read calls for each extent -- one read call for 8 blocks and the other read call for 2 blocks -- because the MBRC factor cannot span across extents.
* 一个盘区内的最后一组数据块数量小于MBRC. 如果MBRC设置为8, 每个盘区10个数据块, 对每个盘区Oracle会发起2个多数据块读 -- 一次读8个数据块, 另一次读2个数据块 -- 因为MBRC不能跨越盘区.
* One or more blocks in the multiblock read set is already in the buffer cache, so Oracle breaks the fetch into two or more reads, which may be comprised of a single or multiblock I/Os. For example, if the MBRC is 8 and blocks 3 and 7 are in the cache, Oracle will issue three read calls -- the first for blocks 1 and 2, the second for block 4 through 6, and the third for block 8. Since the third fetch is for a single database block, the wait event is db file sequential read. However, for the first two read calls, the wait event is db file scattered read because the number of blocks is greater than 1. Therefore, cached blocks can cause full table scans operations to perform more reads than required.
* 多数据块读中的一个或多个数据块已经在数据缓冲区内了, 因此Oracle将分2次或多次获取数据, 由一个单数据块或多数据块I/O组成. 比如, 如果MRBC是8, 数据块3和7在缓冲中, Oracle将发起3个读操作 -- 第1次读数据块1和2, 第2次读数据库4到6, 第3次读数据块8. 因为第3次读的是一个单数据块, 所以等待事件是数据文件连续读(db file sequential read). 而头2个读的等待事件则是数据文件分散读(db file scattered read), 这是因为读取的数据块数大于1. 因此, 被缓冲的数据块导致了全表扫描操作要读取更多次.

参考资料2
db_file_multiblock_read_count and different read rates
"We read maximally by EXTENT size (eg: a read never spans extents). perhaps your are being restricted by your extent size."
"we read in but not across extents, so yes, that small extent would cause the read size to go down."

----begin----
you can use the trace to find the MAX size, the optimal size is something entirely different :)

when you do a multi-block IO, some of the blocks may be in the buffer cache already. So, lets say
Oracle says "lets read 64 blocks starting from file 5, block 17"

it is saying "I need blocks 17 .. 80, but wait, block 17 is in the cache, lets read 63 blocks
starting at block 18". So it'll read 63 blocks

Later it says "I could read upto 64 blocks, but this extent only has 15 blocks left, lets read 15
of them"

Last it says "I could read upto 64 blocks, I want the 64 blocks from file 10 starting at block 1,
but blocks 2, 4, 6, 8, 10, ..... 64 are in the cache. I'll do 32 single block IO's to pick up the
rest"


So, you might (in order to find the max IO size on your system)

a) offline the tablespace (will flush cache of blocks for that tablespace)
b) online the tablespace
c) enable trace
d) full scan
e) exit sqlplus
f) scan trace file for max p3= value....

as long as your extents are bigger than your IO size, you'll have it.


the suggestion is to leave all parameters at a default setting unless you can really identify a
good reason for changing them
----end----

参考资料3
Managing Extents
If set incorrectly, when a full table or index scan is performed, an extra multiread block read will be required to read only the last remaining blocks of each extent. This happens because multiblock reads in Oracle never span extent boundaries, even if the extents happen to be contiguous. This behavior is also consistent within locally managed tablespaces.



3. 测试如下

创建测试表, 表空间是本地自动管理的

create tablespace ts_alloc_auto
 datafile '/home/oracle/app/oracle/oradata/test/ts_alloc_auto_01.dbf' size 100m
  autoextend on next 10m maxsize 500m
 extent management local autoallocate;
/

conn a/a
drop table t1;
create table t1 tablespace ts_alloc_auto
 as select * from all_objects;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;

查看盘区信息

select bytes/1024, count(*)
  from user_extents
 where segment_name = 'T1'
 group by bytes/1024
 order by 1
/

BYTES/1024   COUNT(*)
---------- ----------
        64         16
      1024         63
      6144          1
      8192          9

设置dfmbrc=16, 查看跟踪文件

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set db_file_multiblock_read_count=16;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214328880843122 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #1:c=40994,e=853947,p=527,cr=84,cu=0,mis=1,r=0,dep=0,og=1,tim=1214328880843114
BINDS #1:
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214328880843262
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328880843302
WAIT #1: nam='db file scattered read' ela= 66 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214328880843506
WAIT #1: nam='db file scattered read' ela= 78 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214328880843722
WAIT #1: nam='db file scattered read' ela= 71 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214328880843942
WAIT #1: nam='db file scattered read' ela= 94 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214328880844188
WAIT #1: nam='db file scattered read' ela= 8307 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214328880852641
WAIT #1: nam='db file scattered read' ela= 9519 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214328880862400
WAIT #1: nam='db file scattered read' ela= 8335 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214328880870940
WAIT #1: nam='db file scattered read' ela= 9318 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214328880880464
WAIT #1: nam='db file scattered read' ela= 8404 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214328880889128
WAIT #1: nam='db file scattered read' ela= 9423 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214328880898767
WAIT #1: nam='db file scattered read' ela= 9028 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214328880908032
WAIT #1: nam='db file scattered read' ela= 9220 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214328880917458
WAIT #1: nam='db file scattered read' ela= 7737 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214328880925600
WAIT #1: nam='db file scattered read' ela= 8927 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214328880934760
WAIT #1: nam='db file scattered read' ela= 11810 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214328880946829
WAIT #1: nam='db file scattered read' ela= 8946 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214328880955987
WAIT #1: nam='db file scattered read' ela= 18177 file#=12 block#=3979 blocks=16 obj#=62725 tim=1214328880974414
WAIT #1: nam='db file scattered read' ela= 2307 file#=12 block#=3995 blocks=2 obj#=62725 tim=1214328880977087
WAIT #1: nam='db file scattered read' ela= 2394 file#=12 block#=3998 blocks=16 obj#=62725 tim=1214328880979651
WAIT #1: nam='db file scattered read' ela= 17748 file#=12 block#=4014 blocks=16 obj#=62725 tim=1214328880997720
WAIT #1: nam='db file scattered read' ela= 18176 file#=12 block#=4030 blocks=16 obj#=62725 tim=1214328881016254
WAIT #1: nam='db file scattered read' ela= 18170 file#=12 block#=4046 blocks=16 obj#=62725 tim=1214328881034806
WAIT #1: nam='db file scattered read' ela= 20182 file#=12 block#=4062 blocks=16 obj#=62725 tim=1214328881055348
...
...
WAIT #1: nam='db file scattered read' ela= 18545 file#=12 block#=3831 blocks=16 obj#=62725 tim=1214328900218309
WAIT #1: nam='db file scattered read' ela= 2232 file#=12 block#=3847 blocks=2 obj#=62725 tim=1214328900220873
WAIT #1: nam='db file scattered read' ela= 18546 file#=12 block#=17421 blocks=16 obj#=62725 tim=1214328900239546
WAIT #1: nam='db file scattered read' ela= 18419 file#=12 block#=17437 blocks=16 obj#=62725 tim=1214328900258335
WAIT #1: nam='db file scattered read' ela= 18524 file#=12 block#=17453 blocks=16 obj#=62725 tim=1214328900277237
WAIT #1: nam='db file scattered read' ela= 24145 file#=12 block#=17469 blocks=16 obj#=62725 tim=1214328900301774
WAIT #1: nam='db file scattered read' ela= 34335 file#=12 block#=17485 blocks=16 obj#=62725 tim=1214328900336530
WAIT #1: nam='db file scattered read' ela= 18056 file#=12 block#=17501 blocks=15 obj#=62725 tim=1214328900354970
FETCH #1:c=1071837,e=19511954,p=16551,cr=17088,cu=0,mis=0,r=1,dep=0,og=1,tim=1214328900355290
WAIT #1: nam='SQL*Net message from client' ela= 221 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900355611
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214328900355665
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900355696
WAIT #1: nam='SQL*Net message from client' ela= 466 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900356187
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17088 pr=16551 pw=0 time=19511955 us)'
STAT #1 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17088 pr=16551 pw=0 time=9907650 us)'
=====================

前几个盘区大小是64k, 所以最多读8块
后面的盘区大小是1m, 最多读取16块, 跟db_file_multiblock_read_count相符

但是, 发现有时读取的块数不到16块, 是因为10g查询前的动态采样(Dynamic Sampling)

=====================
PARSING IN CURSOR #2 len=414 dep=1 uid=48 oct=3 lid=48 tim=1214328879996640 hv=508072983 ad='6d666888'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_T
UNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM
 "T1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "T1") SAMPLESUB
END OF STMT
PARSE #2:c=1000,e=862,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1214328879996633
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a974510b0  bln=22  avl=01  flg=09
  value=0
 Bind#1
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a97451068  bln=22  avl=01  flg=09
  value=0
...
EXEC #2:c=2000,e=2463,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1214328879999189
WAIT #2: nam='db file sequential read' ela= 16590 file#=12 block#=3997 blocks=1 obj#=62725 tim=1214328880015978
WAIT #2: nam='db file sequential read' ela= 10124 file#=12 block#=4933 blocks=1 obj#=62725 tim=1214328880026325
WAIT #2: nam='db file sequential read' ela= 11306 file#=12 block#=5071 blocks=1 obj#=62725 tim=1214328880037913
WAIT #2: nam='db file sequential read' ela= 4090 file#=12 block#=5204 blocks=1 obj#=62725 tim=1214328880042182
WAIT #2: nam='db file sequential read' ela= 3980 file#=12 block#=5548 blocks=1 obj#=62725 tim=1214328880046334
WAIT #2: nam='db file sequential read' ela= 7437 file#=12 block#=6317 blocks=1 obj#=62725 tim=1214328880053893
WAIT #2: nam='db file scattered read' ela= 31558 file#=12 block#=6586 blocks=16 obj#=62725 tim=1214328880085761
WAIT #2: nam='db file scattered read' ela= 25753 file#=12 block#=6614 blocks=16 obj#=62725 tim=1214328880111864
WAIT #2: nam='db file sequential read' ela= 3809 file#=12 block#=6653 blocks=1 obj#=62725 tim=1214328880115880
WAIT #2: nam='db file sequential read' ela= 5620 file#=12 block#=7095 blocks=1 obj#=62725 tim=1214328880121604
WAIT #2: nam='db file sequential read' ela= 4788 file#=12 block#=7407 blocks=1 obj#=62725 tim=1214328880126497
...
WAIT #2: nam='db file scattered read' ela= 18731 file#=12 block#=3319 blocks=16 obj#=62725 tim=1214328880802261
WAIT #2: nam='db file scattered read' ela= 18777 file#=12 block#=3584 blocks=16 obj#=62725 tim=1214328880821291
WAIT #2: nam='db file scattered read' ela= 19198 file#=12 block#=3624 blocks=16 obj#=62725 tim=1214328880840813
WAIT #2: nam='db file sequential read' ela= 1243 file#=12 block#=3734 blocks=1 obj#=62725 tim=1214328880842270
FETCH #2:c=35994,e=843145,p=526,cr=83,cu=0,mis=0,r=1,dep=1,og=1,tim=1214328880842375
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=83 pr=526 pw=0 time=843158 us)'
STAT #2 id=2 cnt=4899 pid=1 pos=1 obj=62725 op='TABLE ACCESS SAMPLE T1 (cr=83 pr=526 pw=0 time=26636 us)'
=====================

已经预先读取了一些数据块到缓存, 所以造成后来多数据块读的数量不规则了

关闭动态采样, 重新查询

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=16;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214329116866106 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #2:c=3000,e=2168,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1214329116866095
BINDS #2:
EXEC #2:c=0,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214329116866274
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214329116866313
WAIT #2: nam='db file sequential read' ela= 3424 file#=12 block#=3851 blocks=1 obj#=62725 tim=1214329116871313
WAIT #2: nam='db file scattered read' ela= 2536 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214329116874127
WAIT #2: nam='db file scattered read' ela= 6547 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214329116880948
WAIT #2: nam='db file scattered read' ela= 5220 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214329116886434
WAIT #2: nam='db file scattered read' ela= 2597 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214329116889331
WAIT #2: nam='db file scattered read' ela= 6248 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214329116895837
WAIT #2: nam='db file scattered read' ela= 5833 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214329116901972
WAIT #2: nam='db file scattered read' ela= 5119 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214329116907434
WAIT #2: nam='db file scattered read' ela= 6072 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214329116913780
WAIT #2: nam='db file scattered read' ela= 5649 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214329116919883
WAIT #2: nam='db file scattered read' ela= 5165 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214329116925362
WAIT #2: nam='db file scattered read' ela= 5220 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214329116930849
WAIT #2: nam='db file scattered read' ela= 6463 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214329116937547
WAIT #2: nam='db file scattered read' ela= 6555 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214329116944397
WAIT #2: nam='db file scattered read' ela= 5195 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214329116949877
WAIT #2: nam='db file scattered read' ela= 5224 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214329116955364
WAIT #2: nam='db file scattered read' ela= 5253 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214329116960861
WAIT #2: nam='db file scattered read' ela= 11883 file#=12 block#=3979 blocks=16 obj#=62725 tim=1214329116973055
WAIT #2: nam='db file scattered read' ela= 10444 file#=12 block#=3995 blocks=16 obj#=62725 tim=1214329116984032
WAIT #2: nam='db file scattered read' ela= 10553 file#=12 block#=4011 blocks=16 obj#=62725 tim=1214329116995017
WAIT #2: nam='db file scattered read' ela= 10757 file#=12 block#=4027 blocks=16 obj#=62725 tim=1214329117006276
WAIT #2: nam='db file scattered read' ela= 10090 file#=12 block#=4043 blocks=16 obj#=62725 tim=1214329117016866
WAIT #2: nam='db file scattered read' ela= 10533 file#=12 block#=4059 blocks=16 obj#=62725 tim=1214329117027851
WAIT #2: nam='db file scattered read' ela= 10565 file#=12 block#=4075 blocks=16 obj#=62725 tim=1214329117038829
WAIT #2: nam='db file scattered read' ela= 8118 file#=12 block#=4091 blocks=14 obj#=62725 tim=1214329117047387
WAIT #2: nam='db file scattered read' ela= 89876 file#=12 block#=4363 blocks=16 obj#=62725 tim=1214329117137664
...
...
WAIT #2: nam='db file scattered read' ela= 10611 file#=12 block#=3804 blocks=16 obj#=62725 tim=1214329128747190
WAIT #2: nam='db file scattered read' ela= 10603 file#=12 block#=3820 blocks=16 obj#=62725 tim=1214329128758184
WAIT #2: nam='db file scattered read' ela= 9949 file#=12 block#=3836 blocks=13 obj#=62725 tim=1214329128768526
WAIT #2: nam='db file scattered read' ela= 441 file#=12 block#=17421 blocks=16 obj#=62725 tim=1214329128769262
WAIT #2: nam='db file scattered read' ela= 112 file#=12 block#=17437 blocks=16 obj#=62725 tim=1214329128769687
WAIT #2: nam='db file scattered read' ela= 128 file#=12 block#=17453 blocks=16 obj#=62725 tim=1214329128770161
WAIT #2: nam='db file scattered read' ela= 467 file#=12 block#=17469 blocks=16 obj#=62725 tim=1214329128770919
WAIT #2: nam='db file scattered read' ela= 125 file#=12 block#=17485 blocks=16 obj#=62725 tim=1214329128771381
WAIT #2: nam='db file scattered read' ela= 22736 file#=12 block#=17501 blocks=15 obj#=62725 tim=1214329128794404
FETCH #2:c=690895,e=11928411,p=17078,cr=17093,cu=0,mis=0,r=1,dep=0,og=1,tim=1214329128794759
WAIT #2: nam='SQL*Net message from client' ela= 265 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795142
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214329128795206
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795250
WAIT #2: nam='SQL*Net message from client' ela= 515 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795790
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17093 pr=17078 pw=0 time=11928417 us)'
STAT #2 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17093 pr=17078 pw=0 time=11153729 us)'
=====================

读取还是不规则. 比如,读7个16块,1个14块. 为什么? 有时还有13块?

设置dfmbrc为更大的值, dfmbrc=4096, 查看跟踪文件
其实设置不到4096这么大, 最大只有128, 也就是最多一次读128*8k=1m

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
SQL> alter session set db_file_multiblock_read_count=4096;

Session altered.

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
SQL>

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=4096;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214351910543864 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #2:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214351910543858
BINDS #2:
EXEC #2:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214351910544029
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214351910544071
WAIT #2: nam='db file sequential read' ela= 3416 file#=12 block#=3851 blocks=1 obj#=62725 tim=1214351910548894
WAIT #2: nam='db file scattered read' ela= 2408 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214351910551595
WAIT #2: nam='db file scattered read' ela= 5218 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214351910557073
WAIT #2: nam='db file scattered read' ela= 5215 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214351910562563
WAIT #2: nam='db file scattered read' ela= 3839 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214351910566707
WAIT #2: nam='db file scattered read' ela= 6344 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214351910573312
WAIT #2: nam='db file scattered read' ela= 5167 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214351910578795
WAIT #2: nam='db file scattered read' ela= 5271 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214351910584326
WAIT #2: nam='db file scattered read' ela= 6626 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214351910591250
WAIT #2: nam='db file scattered read' ela= 5182 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214351910596721
WAIT #2: nam='db file scattered read' ela= 5227 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214351910602211
WAIT #2: nam='db file scattered read' ela= 5221 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214351910607696
WAIT #2: nam='db file scattered read' ela= 6848 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214351910614795
WAIT #2: nam='db file scattered read' ela= 5128 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214351910620259
WAIT #2: nam='db file scattered read' ela= 5253 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214351910625751
WAIT #2: nam='db file scattered read' ela= 5245 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214351910631248
WAIT #2: nam='db file scattered read' ela= 6596 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214351910638089
WAIT #2: nam='db file scattered read' ela= 87504 file#=12 block#=3979 blocks=126 obj#=62725 tim=1214351910726610
WAIT #2: nam='db file scattered read' ela= 166722 file#=12 block#=4363 blocks=126 obj#=62725 tim=1214351910896271
WAIT #2: nam='db file scattered read' ela= 84797 file#=12 block#=4491 blocks=126 obj#=62725 tim=1214351910983974
WAIT #2: nam='db file scattered read' ela= 84803 file#=12 block#=4619 blocks=126 obj#=62725 tim=1214351911071708
WAIT #2: nam='db file scattered read' ela= 84780 file#=12 block#=4747 blocks=126 obj#=62725 tim=1214351911159631
WAIT #2: nam='db file scattered read' ela= 84584 file#=12 block#=4875 blocks=126 obj#=62725 tim=1214351911247331
WAIT #2: nam='db file scattered read' ela= 84883 file#=12 block#=5003 blocks=126 obj#=62725 tim=1214351911335008
WAIT #2: nam='db file scattered read' ela= 84960 file#=12 block#=5131 blocks=126 obj#=62725 tim=1214351911422732
WAIT #2: nam='db file scattered read' ela= 85093 file#=12 block#=5259 blocks=126 obj#=62725 tim=1214351911510735
WAIT #2: nam='db file scattered read' ela= 84723 file#=12 block#=5387 blocks=126 obj#=62725 tim=1214351911598233
WAIT #2: nam='db file scattered read' ela= 85103 file#=12 block#=5515 blocks=126 obj#=62725 tim=1214351911686015
WAIT #2: nam='db file scattered read' ela= 85149 file#=12 block#=5643 blocks=126 obj#=62725 tim=1214351911773861
WAIT #2: nam='db file scattered read' ela= 87201 file#=12 block#=5771 blocks=126 obj#=62725 tim=1214351911863783
WAIT #2: nam='db file scattered read' ela= 84943 file#=12 block#=5899 blocks=126 obj#=62725 tim=1214351911951679
WAIT #2: nam='db file scattered read' ela= 84879 file#=12 block#=6027 blocks=126 obj#=62725 tim=1214351912039312
WAIT #2: nam='db file scattered read' ela= 84608 file#=12 block#=6155 blocks=126 obj#=62725 tim=1214351912127094
WAIT #2: nam='db file scattered read' ela= 85062 file#=12 block#=6283 blocks=126 obj#=62725 tim=1214351912214894
WAIT #2: nam='db file scattered read' ela= 84816 file#=12 block#=6411 blocks=126 obj#=62725 tim=1214351912302607
WAIT #2: nam='db file scattered read' ela= 85147 file#=12 block#=6539 blocks=126 obj#=62725 tim=1214351912390407
WAIT #2: nam='db file scattered read' ela= 85150 file#=12 block#=6667 blocks=126 obj#=62725 tim=1214351912478262
WAIT #2: nam='db file scattered read' ela= 85169 file#=12 block#=6795 blocks=126 obj#=62725 tim=1214351912566093
WAIT #2: nam='db file scattered read' ela= 85125 file#=12 block#=6923 blocks=126 obj#=62725 tim=1214351912654005
WAIT #2: nam='db file scattered read' ela= 84563 file#=12 block#=7051 blocks=126 obj#=62725 tim=1214351912741721
WAIT #2: nam='db file scattered read' ela= 84846 file#=12 block#=7179 blocks=126 obj#=62725 tim=1214351912829444
WAIT #2: nam='db file scattered read' ela= 84862 file#=12 block#=7307 blocks=126 obj#=62725 tim=1214351912917050
WAIT #2: nam='db file scattered read' ela= 84933 file#=12 block#=7435 blocks=126 obj#=62725 tim=1214351913004811
WAIT #2: nam='db file scattered read' ela= 84942 file#=12 block#=7563 blocks=126 obj#=62725 tim=1214351913092502
WAIT #2: nam='db file scattered read' ela= 85132 file#=12 block#=7691 blocks=126 obj#=62725 tim=1214351913180347
WAIT #2: nam='db file scattered read' ela= 85049 file#=12 block#=7819 blocks=126 obj#=62725 tim=1214351913268080
WAIT #2: nam='db file scattered read' ela= 84877 file#=12 block#=7947 blocks=126 obj#=62725 tim=1214351913355804
WAIT #2: nam='db file scattered read' ela= 84951 file#=12 block#=8075 blocks=126 obj#=62725 tim=1214351913443520
WAIT #2: nam='db file scattered read' ela= 85081 file#=12 block#=8203 blocks=126 obj#=62725 tim=1214351913531319
WAIT #2: nam='db file scattered read' ela= 85028 file#=12 block#=8331 blocks=126 obj#=62725 tim=1214351913619047
WAIT #2: nam='db file scattered read' ela= 84962 file#=12 block#=8459 blocks=126 obj#=62725 tim=1214351913706849
WAIT #2: nam='db file scattered read' ela= 84855 file#=12 block#=8587 blocks=126 obj#=62725 tim=1214351913794578
WAIT #2: nam='db file scattered read' ela= 85190 file#=12 block#=8715 blocks=126 obj#=62725 tim=1214351913882474
WAIT #2: nam='db file scattered read' ela= 84566 file#=12 block#=8843 blocks=126 obj#=62725 tim=1214351913970201
WAIT #2: nam='db file scattered read' ela= 85003 file#=12 block#=8971 blocks=126 obj#=62725 tim=1214351914057894
WAIT #2: nam='db file scattered read' ela= 85174 file#=12 block#=9099 blocks=126 obj#=62725 tim=1214351914145869
WAIT #2: nam='db file scattered read' ela= 84784 file#=12 block#=9227 blocks=126 obj#=62725 tim=1214351914233373
WAIT #2: nam='db file scattered read' ela= 85096 file#=12 block#=9355 blocks=126 obj#=62725 tim=1214351914321154
WAIT #2: nam='db file scattered read' ela= 85037 file#=12 block#=9483 blocks=126 obj#=62725 tim=1214351914408912
WAIT #2: nam='db file scattered read' ela= 85018 file#=12 block#=9611 blocks=126 obj#=62725 tim=1214351914496710
WAIT #2: nam='db file scattered read' ela= 84800 file#=12 block#=9739 blocks=126 obj#=62725 tim=1214351914584616
WAIT #2: nam='db file scattered read' ela= 84961 file#=12 block#=9867 blocks=126 obj#=62725 tim=1214351914672285
WAIT #2: nam='db file scattered read' ela= 84974 file#=12 block#=9995 blocks=126 obj#=62725 tim=1214351914759998
WAIT #2: nam='db file scattered read' ela= 87049 file#=12 block#=10123 blocks=126 obj#=62725 tim=1214351914849805
WAIT #2: nam='db file scattered read' ela= 84919 file#=12 block#=10251 blocks=126 obj#=62725 tim=1214351914937507
WAIT #2: nam='db file scattered read' ela= 85094 file#=12 block#=10379 blocks=126 obj#=62725 tim=1214351915025324
WAIT #2: nam='db file scattered read' ela= 93487 file#=12 block#=10507 blocks=126 obj#=62725 tim=1214351915121494
WAIT #2: nam='db file scattered read' ela= 76708 file#=12 block#=10635 blocks=126 obj#=62725 tim=1214351915200896
WAIT #2: nam='db file scattered read' ela= 84945 file#=12 block#=10763 blocks=126 obj#=62725 tim=1214351915288673
WAIT #2: nam='db file scattered read' ela= 85123 file#=12 block#=10891 blocks=126 obj#=62725 tim=1214351915376507
WAIT #2: nam='db file scattered read' ela= 85206 file#=12 block#=11019 blocks=126 obj#=62725 tim=1214351915464493
WAIT #2: nam='db file scattered read' ela= 85099 file#=12 block#=11147 blocks=126 obj#=62725 tim=1214351915552304
WAIT #2: nam='db file scattered read' ela= 89897 file#=12 block#=11275 blocks=126 obj#=62725 tim=1214351915645016
WAIT #2: nam='db file scattered read' ela= 85050 file#=12 block#=11403 blocks=126 obj#=62725 tim=1214351915732738
WAIT #2: nam='db file scattered read' ela= 85161 file#=12 block#=11531 blocks=126 obj#=62725 tim=1214351915820585
WAIT #2: nam='db file scattered read' ela= 85294 file#=12 block#=11659 blocks=126 obj#=62725 tim=1214351915908549
WAIT #2: nam='db file scattered read' ela= 84876 file#=12 block#=11787 blocks=126 obj#=62725 tim=1214351915996265
WAIT #2: nam='db file scattered read' ela= 84860 file#=12 block#=11915 blocks=126 obj#=62725 tim=1214351916083966
WAIT #2: nam='db file scattered read' ela= 85020 file#=12 block#=12043 blocks=126 obj#=62725 tim=1214351916171686
WAIT #2: nam='db file scattered read' ela= 84694 file#=12 block#=12170 blocks=127 obj#=62725 tim=1214351916259509
WAIT #2: nam='db file scattered read' ela= 87660 file#=12 block#=12301 blocks=128 obj#=62725 tim=1214351916349970
WAIT #2: nam='db file scattered read' ela= 85014 file#=12 block#=12429 blocks=128 obj#=62725 tim=1214351916437808
WAIT #2: nam='db file scattered read' ela= 84923 file#=12 block#=12557 blocks=128 obj#=62725 tim=1214351916525495
WAIT #2: nam='db file scattered read' ela= 84929 file#=12 block#=12685 blocks=128 obj#=62725 tim=1214351916613175
WAIT #2: nam='db file scattered read' ela= 84899 file#=12 block#=12813 blocks=128 obj#=62725 tim=1214351916701065
WAIT #2: nam='db file scattered read' ela= 84868 file#=12 block#=12941 blocks=128 obj#=62725 tim=1214351916788797
WAIT #2: nam='db file scattered read' ela= 85047 file#=12 block#=13069 blocks=128 obj#=62725 tim=1214351916876575
WAIT #2: nam='db file scattered read' ela= 82152 file#=12 block#=13197 blocks=124 obj#=62725 tim=1214351916961500
WAIT #2: nam='db file scattered read' ela= 87154 file#=12 block#=13325 blocks=128 obj#=62725 tim=1214351917052060
WAIT #2: nam='db file scattered read' ela= 84967 file#=12 block#=13453 blocks=128 obj#=62725 tim=1214351917139905
WAIT #2: nam='db file scattered read' ela= 84905 file#=12 block#=13581 blocks=128 obj#=62725 tim=1214351917227592
WAIT #2: nam='db file scattered read' ela= 85613 file#=12 block#=13709 blocks=128 obj#=62725 tim=1214351917315952
WAIT #2: nam='db file scattered read' ela= 85091 file#=12 block#=13837 blocks=128 obj#=62725 tim=1214351917403834
WAIT #2: nam='db file scattered read' ela= 86421 file#=12 block#=13965 blocks=128 obj#=62725 tim=1214351917493241
WAIT #2: nam='db file scattered read' ela= 85098 file#=12 block#=14093 blocks=128 obj#=62725 tim=1214351917581064
WAIT #2: nam='db file scattered read' ela= 82232 file#=12 block#=14221 blocks=124 obj#=62725 tim=1214351917666031
WAIT #2: nam='db file scattered read' ela= 87473 file#=12 block#=14349 blocks=128 obj#=62725 tim=1214351917756500
WAIT #2: nam='db file scattered read' ela= 87166 file#=12 block#=14477 blocks=128 obj#=62725 tim=1214351917846538
WAIT #2: nam='db file scattered read' ela= 84952 file#=12 block#=14605 blocks=128 obj#=62725 tim=1214351917934223
WAIT #2: nam='db file scattered read' ela= 85422 file#=12 block#=14733 blocks=128 obj#=62725 tim=1214351918022378
WAIT #2: nam='db file scattered read' ela= 84502 file#=12 block#=14861 blocks=128 obj#=62725 tim=1214351918110063
WAIT #2: nam='db file scattered read' ela= 84870 file#=12 block#=14989 blocks=128 obj#=62725 tim=1214351918197783
WAIT #2: nam='db file scattered read' ela= 84973 file#=12 block#=15117 blocks=128 obj#=62725 tim=1214351918285618
WAIT #2: nam='db file scattered read' ela= 82231 file#=12 block#=15245 blocks=124 obj#=62725 tim=1214351918370666
WAIT #2: nam='db file scattered read' ela= 88454 file#=12 block#=15373 blocks=128 obj#=62725 tim=1214351918461802
WAIT #2: nam='db file scattered read' ela= 84889 file#=12 block#=15501 blocks=128 obj#=62725 tim=1214351918549513
WAIT #2: nam='db file scattered read' ela= 85044 file#=12 block#=15629 blocks=128 obj#=62725 tim=1214351918637377
WAIT #2: nam='db file scattered read' ela= 85250 file#=12 block#=15757 blocks=128 obj#=62725 tim=1214351918725354
WAIT #2: nam='db file scattered read' ela= 84693 file#=12 block#=15885 blocks=128 obj#=62725 tim=1214351918812915
WAIT #2: nam='db file scattered read' ela= 85098 file#=12 block#=16013 blocks=128 obj#=62725 tim=1214351918900749
WAIT #2: nam='db file scattered read' ela= 85060 file#=12 block#=16141 blocks=128 obj#=62725 tim=1214351918988614
WAIT #2: nam='db file scattered read' ela= 82203 file#=12 block#=16269 blocks=124 obj#=62725 tim=1214351919073551
WAIT #2: nam='db file scattered read' ela= 87833 file#=12 block#=16397 blocks=128 obj#=62725 tim=1214351919164104
WAIT #2: nam='db file scattered read' ela= 84953 file#=12 block#=16525 blocks=128 obj#=62725 tim=1214351919251812
WAIT #2: nam='db file scattered read' ela= 84994 file#=12 block#=16653 blocks=128 obj#=62725 tim=1214351919339701
WAIT #2: nam='db file scattered read' ela= 84984 file#=12 block#=16781 blocks=128 obj#=62725 tim=1214351919427846
WAIT #2: nam='db file scattered read' ela= 85027 file#=12 block#=16909 blocks=128 obj#=62725 tim=1214351919515687
WAIT #2: nam='db file scattered read' ela= 85656 file#=12 block#=17037 blocks=128 obj#=62725 tim=1214351919604120
WAIT #2: nam='db file scattered read' ela= 84927 file#=12 block#=17165 blocks=128 obj#=62725 tim=1214351919691946
WAIT #2: nam='db file scattered read' ela= 81989 file#=12 block#=17293 blocks=124 obj#=62725 tim=1214351919776786
WAIT #2: nam='db file scattered read' ela= 131724 file#=12 block#=13 blocks=128 obj#=62725 tim=1214351919911211
WAIT #2: nam='db file scattered read' ela= 85492 file#=12 block#=141 blocks=128 obj#=62725 tim=1214351919999413
WAIT #2: nam='db file scattered read' ela= 84817 file#=12 block#=269 blocks=128 obj#=62725 tim=1214351920087174
WAIT #2: nam='db file scattered read' ela= 84424 file#=12 block#=397 blocks=128 obj#=62725 tim=1214351920174816
WAIT #2: nam='db file scattered read' ela= 84965 file#=12 block#=525 blocks=128 obj#=62725 tim=1214351920262538
*** 2009-05-28 07:39:26.438
WAIT #2: nam='db file scattered read' ela= 85025 file#=12 block#=653 blocks=128 obj#=62725 tim=1214351920350373
WAIT #2: nam='db file scattered read' ela= 85233 file#=12 block#=781 blocks=128 obj#=62725 tim=1214351920438458
WAIT #2: nam='db file scattered read' ela= 82161 file#=12 block#=909 blocks=124 obj#=62725 tim=1214351920523410
WAIT #2: nam='db file scattered read' ela= 89403 file#=12 block#=1037 blocks=128 obj#=62725 tim=1214351920615466
WAIT #2: nam='db file scattered read' ela= 85092 file#=12 block#=1165 blocks=128 obj#=62725 tim=1214351920703258
WAIT #2: nam='db file scattered read' ela= 86077 file#=12 block#=1293 blocks=128 obj#=62725 tim=1214351920792731
WAIT #2: nam='db file scattered read' ela= 85274 file#=12 block#=1421 blocks=128 obj#=62725 tim=1214351920880847
WAIT #2: nam='db file scattered read' ela= 84926 file#=12 block#=1549 blocks=128 obj#=62725 tim=1214351920968580
WAIT #2: nam='db file scattered read' ela= 85055 file#=12 block#=1677 blocks=128 obj#=62725 tim=1214351921056417
WAIT #2: nam='db file scattered read' ela= 84898 file#=12 block#=1805 blocks=128 obj#=62725 tim=1214351921144107
WAIT #2: nam='db file scattered read' ela= 82178 file#=12 block#=1933 blocks=124 obj#=62725 tim=1214351921229184
WAIT #2: nam='db file scattered read' ela= 89427 file#=12 block#=2061 blocks=128 obj#=62725 tim=1214351921321285
WAIT #2: nam='db file scattered read' ela= 85163 file#=12 block#=2189 blocks=128 obj#=62725 tim=1214351921409182
WAIT #2: nam='db file scattered read' ela= 85052 file#=12 block#=2317 blocks=128 obj#=62725 tim=1214351921497029
WAIT #2: nam='db file scattered read' ela= 84847 file#=12 block#=2445 blocks=128 obj#=62725 tim=1214351921584852
WAIT #2: nam='db file scattered read' ela= 84866 file#=12 block#=2573 blocks=128 obj#=62725 tim=1214351921672513
WAIT #2: nam='db file scattered read' ela= 85162 file#=12 block#=2701 blocks=128 obj#=62725 tim=1214351921760449
WAIT #2: nam='db file scattered read' ela= 85226 file#=12 block#=2829 blocks=128 obj#=62725 tim=1214351921848473
WAIT #2: nam='db file scattered read' ela= 81282 file#=12 block#=2957 blocks=124 obj#=62725 tim=1214351921933222
WAIT #2: nam='db file scattered read' ela= 88830 file#=12 block#=3084 blocks=128 obj#=62725 tim=1214351922024750
WAIT #2: nam='db file scattered read' ela= 85072 file#=12 block#=3212 blocks=128 obj#=62725 tim=1214351922112530
WAIT #2: nam='db file scattered read' ela= 88051 file#=12 block#=3340 blocks=128 obj#=62725 tim=1214351922203314
WAIT #2: nam='db file scattered read' ela= 84971 file#=12 block#=3468 blocks=128 obj#=62725 tim=1214351922291095
WAIT #2: nam='db file scattered read' ela= 85209 file#=12 block#=3596 blocks=128 obj#=62725 tim=1214351922379076
WAIT #2: nam='db file scattered read' ela= 84185 file#=12 block#=3724 blocks=125 obj#=62725 tim=1214351922466023
WAIT #2: nam='db file scattered read' ela= 12697 file#=12 block#=17421 blocks=95 obj#=62725 tim=1214351922481138
FETCH #2:c=701893,e=11938796,p=17078,cr=17093,cu=0,mis=0,r=1,dep=0,og=1,tim=1214351922482897
WAIT #2: nam='SQL*Net message from client' ela= 273 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922483316
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214351922483375
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922483413
WAIT #2: nam='SQL*Net message from client' ela= 560 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922484003
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17093 pr=17078 pw=0 time=11938810 us)'
STAT #2 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17093 pr=17078 pw=0 time=23537745 us)'
=====================

一开始读7/8块, 读16次
后来读126/127块, 63次
最后读128块, 8次7*128+124, 1次5*128+125+95,
这跟盘区信息是相符的

BYTES/1024   COUNT(*)
---------- ----------
        64         16
      1024         63
      6144          1
      8192          9

不能解释为什么有时读取的块数不到dfmbrc, 而是5,7,124,127?
为什么读1m大小的盘区时, 只读126块?

重建表, 表空间是统一尺寸分配, 盘区大小为100m

conn / as sysdba
create tablespace ts_uniform_100m
 datafile '/home/oracle/app/oracle/oradata/test/ts_uniform_100m_01.dbf' size 200m
  autoextend on next 100m maxsize 500m
 extent management local uniform size 100m
/

conn a/a
drop table t2;
create table t2 tablespace ts_uniform_100m
 as select * from all_objects;
insert /*+ append */ into t2 select * from t2;
commit;
insert /*+ append */ into t2 select * from t2;
commit;
insert /*+ append */ into t2 select * from t2;
commit;

查看盘区信息

select bytes/1024, count(*)
  from user_extents
 where segment_name = 'T2'
 group by bytes/1024
 order by 1
/

BYTES/1024   COUNT(*)
---------- ----------
    102400          1

全部记录都处于1个100m的盘区内

设置dfmbrc=最大, 查看跟踪文件

conn / as sysdba
alter tablespace ts_uniform_100m offline;
alter tablespace ts_uniform_100m online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=4096;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t2) */ count(*) from t2;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214353316531035 hv=2934957608 ad='789b3710'
select /*+ FULL(t2) */ count(*) from t2
END OF STMT
PARSE #2:c=2999,e=2167,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1214353316531022
BINDS #2:
EXEC #2:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214353316531214
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214353316531254
WAIT #2: nam='db file sequential read' ela= 3545 file#=11 block#=60 blocks=1 obj#=62731 tim=1214353316536247
WAIT #2: nam='db file scattered read' ela= 88963 file#=11 block#=61 blocks=128 obj#=62731 tim=1214353316626836
WAIT #2: nam='db file scattered read' ela= 91860 file#=11 block#=189 blocks=128 obj#=62731 tim=1214353316722135
WAIT #2: nam='db file scattered read' ela= 89679 file#=11 block#=317 blocks=128 obj#=62731 tim=1214353316814880
WAIT #2: nam='db file scattered read' ela= 91533 file#=11 block#=445 blocks=128 obj#=62731 tim=1214353316909059
WAIT #2: nam='db file scattered read' ela= 85837 file#=11 block#=573 blocks=128 obj#=62731 tim=1214353316997722
WAIT #2: nam='db file scattered read' ela= 84681 file#=11 block#=701 blocks=128 obj#=62731 tim=1214353317085472
WAIT #2: nam='db file scattered read' ela= 85087 file#=11 block#=829 blocks=128 obj#=62731 tim=1214353317173302
WAIT #2: nam='db file scattered read' ela= 87264 file#=11 block#=957 blocks=128 obj#=62731 tim=1214353317263270
WAIT #2: nam='db file scattered read' ela= 85293 file#=11 block#=1085 blocks=128 obj#=62731 tim=1214353317351353
WAIT #2: nam='db file scattered read' ela= 83874 file#=11 block#=1213 blocks=128 obj#=62731 tim=1214353317438788
WAIT #2: nam='db file scattered read' ela= 87989 file#=11 block#=1341 blocks=128 obj#=62731 tim=1214353317529645
WAIT #2: nam='db file scattered read' ela= 84940 file#=11 block#=1469 blocks=128 obj#=62731 tim=1214353317617455
WAIT #2: nam='db file scattered read' ela= 85153 file#=11 block#=1597 blocks=128 obj#=62731 tim=1214353317705479
WAIT #2: nam='db file scattered read' ela= 84574 file#=11 block#=1725 blocks=128 obj#=62731 tim=1214353317793068
WAIT #2: nam='db file scattered read' ela= 84848 file#=11 block#=1853 blocks=128 obj#=62731 tim=1214353317880767
WAIT #2: nam='db file scattered read' ela= 84992 file#=11 block#=1981 blocks=128 obj#=62731 tim=1214353317968596
WAIT #2: nam='db file scattered read' ela= 84968 file#=11 block#=2109 blocks=128 obj#=62731 tim=1214353318056353
WAIT #2: nam='db file scattered read' ela= 84690 file#=11 block#=2237 blocks=128 obj#=62731 tim=1214353318144047
WAIT #2: nam='db file scattered read' ela= 88591 file#=11 block#=2365 blocks=128 obj#=62731 tim=1214353318235508
WAIT #2: nam='db file scattered read' ela= 85007 file#=11 block#=2493 blocks=128 obj#=62731 tim=1214353318323290
WAIT #2: nam='db file scattered read' ela= 84633 file#=11 block#=2621 blocks=128 obj#=62731 tim=1214353318411145
WAIT #2: nam='db file scattered read' ela= 85179 file#=11 block#=2749 blocks=128 obj#=62731 tim=1214353318499112
WAIT #2: nam='db file scattered read' ela= 84844 file#=11 block#=2877 blocks=128 obj#=62731 tim=1214353318586863
WAIT #2: nam='db file scattered read' ela= 85204 file#=11 block#=3005 blocks=128 obj#=62731 tim=1214353318674810
WAIT #2: nam='db file scattered read' ela= 84841 file#=11 block#=3133 blocks=128 obj#=62731 tim=1214353318762359
WAIT #2: nam='db file scattered read' ela= 84941 file#=11 block#=3261 blocks=128 obj#=62731 tim=1214353318850057
WAIT #2: nam='db file scattered read' ela= 84890 file#=11 block#=3389 blocks=128 obj#=62731 tim=1214353318937820
WAIT #2: nam='db file scattered read' ela= 85100 file#=11 block#=3517 blocks=128 obj#=62731 tim=1214353319025678
WAIT #2: nam='db file scattered read' ela= 84955 file#=11 block#=3645 blocks=128 obj#=62731 tim=1214353319113389
WAIT #2: nam='db file scattered read' ela= 85105 file#=11 block#=3773 blocks=128 obj#=62731 tim=1214353319201352
WAIT #2: nam='db file scattered read' ela= 84542 file#=11 block#=3901 blocks=128 obj#=62731 tim=1214353319288753
WAIT #2: nam='db file scattered read' ela= 84724 file#=11 block#=4029 blocks=128 obj#=62731 tim=1214353319376214
WAIT #2: nam='db file scattered read' ela= 85027 file#=11 block#=4157 blocks=128 obj#=62731 tim=1214353319464026
WAIT #2: nam='db file scattered read' ela= 30083 file#=11 block#=4285 blocks=47 obj#=62731 tim=1214353319496682
FETCH #2:c=197970,e=2966232,p=4272,cr=4276,cu=0,mis=0,r=1,dep=0,og=1,tim=1214353319497521
WAIT #2: nam='SQL*Net message from client' ela= 313 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319497972
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214353319498033
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319498072
WAIT #2: nam='SQL*Net message from client' ela= 565 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319498664
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4276 pr=4272 pw=0 time=2966237 us)'
STAT #2 id=2 cnt=309616 pid=1 pos=1 obj=62731 op='TABLE ACCESS FULL T2 (cr=4276 pr=4272 pw=0 time=405861 us)'
=====================

这次没有出现124块的情况, 除了第一次(为什么?)和最后一次不是128块. 第1次只读了1块,好像每次查询都这样


4. 其它参数
_db_file_exec_read_count 和 _db_file_optimizer_read_count
_db_file_exec_read_count在多数据块读运行时使用
_db_file_optimizer_read_count则由优化器使用
修改db_file_multiblock_read_count参数后, 会自动修改这2个参数

col parameter for a40
col session_value for a20
col instance_value for a20
select substr(a.ksppinm,1,40) parameter,
       substr(b.ksppstvl,1,20) session_value,
       c.ksppstvl instance_value
  from x$ksppi a, x$ksppcv b, x$ksppsv c
 where a.indx = b.indx and a.indx = c.indx
   and a.ksppinm like '%db_file%';

PARAMETER                                SESSION_VALUE        INSTANCE_VALUE
---------------------------------------- -------------------- --------------------
_db_file_direct_io_count                 1048576              1048576
db_file_name_convert
db_files                                 200                  200
db_file_multiblock_read_count            128                  16
_db_file_exec_read_count                 128                  16
_db_file_optimizer_read_count            128                  16
_db_file_noncontig_mblock_read_count     11                   11
_db_file_format_io_buffers               4                    4

8 rows selected.

5. 其它参考资料
'DB_FILE_MULTIBLOCK_READ_COUNT' AND EXTENTS MANAGEMENT

Blocks read using index vs. full table scan
db_file_multiblock_read_count and different read rates
----begin----
Followup:
the table isn't full. we only read what we need -- and some of it can be in the buffer cache
(hence we won't read that necessarily).

It "looks" like you are using 3 of the 5 extents to hold data (this is probably, not 100%, caching
can affect this).

extent 1: blocks 867 .. 1042 (128+47)
extent 2: blocks 1302 .. 1562 (128+128+4)
extent 3: blocks 2082 .. 2249 (128+39)

the other two extents have nothing, two of the three that do have data are not full.
----end----

Sizing Extents for Performance

WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
How To Unset DB_FILE_MULTIBLOCK_READ_COUNT to Default Value?
Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows)
Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows)





-fin-