测试的缘由是前几天看到了这个帖子：
http://www.itpub.net/viewthread. ... hlight=%2Bwarehouse
--===========================================
SQL> create table t tablespace test as select *from dba_objects;
表已创建。
SQL> insert into t select * from dba_objects;
已创建11354行。
SQL> insert into t select * from dba_objects;
已创建11354行。
SQL> commit;
提交完成。
SQL> select file_id , tablespace_name,file_name from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- --------------- --------------------------------------------------
2 TEST
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST.DBF
3 SYSAUX
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
1 SYSTEM
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
10 UNDOTBS3E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS3.DBF
SQL> select data_object_id from dba_objects where object_name='T' and owner='TES
T';
DATA_OBJECT_ID
--------------
12156
SQL> select count(*) from v$bh where objd=12156;
COUNT(*)
----------
304
SQL> connect / as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
--确保表T的block在内存中不存在，下面的操作有很多这样的步骤以及重起系统都是为了这个目的
SQL> select count(*) from v$bh where objd=12156;
COUNT(*)
----------
0
SQL> connect test/test
已连接。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
4
1
2
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select count(*) from t;
COUNT(*)
----------
34062
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
92
1
2
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 8
db_file_name_convert
string
db_files
integer 200
SQL> select 92 - 4 from dual;
92-4
----------
88
SQL> select file_id,extent_id,block_id,blocks from dba_extents where segment_name='T' and owner='TEST';
FILE_IDEXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
2
0265
8
2
1273
8
2
2281
8
2
3289
8
2
4297
8
2
5305
8
2
6313
8
2
7321
8
2
8329
8
2
9337
8
2 10345
8
FILE_IDEXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
2 11353
8
2 12361
8
2 13369
8
2 14377
8
2 15385
8
2 16393128
2 17521128
2 18649128
已选择19行。
--db_file_multiblock_read_count=8，那么物理读最少需要64个
SQL> select 16+128/8*3 from dual;
16+128/8*3
----------
64
--获得参数user_dump_dest的位置
SQL> show parameter user
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
license_max_users
integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest
stringE:\ORACLE\PRODUCT\10.2.0\ADMIN
--获得os上thread id，目的是为了找到上面所做的10046事件产生的trace file
\TEST\UDUMP
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------
3436
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>e:
E:\>cd E:\oracle\product\10.2.0\admin\test\udump
E:\oracle\product\10.2.0\admin\test\udump>dir *3436*
驱动器 E 中的卷没有标签。
卷的序列号是 F6E5-4B31
E:\oracle\product\10.2.0\admin\test\udump 的目录
2008-09-2408:23 170,923 tsid_ora_3436.trc
1 个文件170,923 字节
0 个目录2,770,808,832 可用字节
E:\oracle\product\10.2.0\admin\test\udump>exit
--创建一个目录为下面创建外部表使用
SQL> create or replace directory sqldir as 'E:\oracle\product\10.2.0\admin\test\
udump';
目录已创建。
SQL> show user
USER 为 "TEST"
--创建一个外部表来处理trace file，这样方便一些
SQL> create table trace_log(
2log_text varchar2(4000)
3)
4organization external
5(
6type oracle_loader
7default directory sqldir
8access parameters
9(
10records delimited by newline
11nobadfile
12nodiscardfile
13nologfile
14)
15location('tsid_ora_3436.trc')
16)
17reject limit unlimited
18/
表已创建。
SQL> select count(*) from trace_log;
COUNT(*)
----------
3226
--实际发生的物理读是84个，而上面通过v$filestat中字段phyrds计算出来的是88，存在一些偏差。
SQL> select count(*) from trace_log where log_text like '%nam=''db file %'
2and log_text like '%file#=2%';
COUNT(*)
----------
84
运行下面sql查询trace file，看看这84个物理读都发生在那些block上：
(为了操作方便一些，我在plsql developer上查询之后，把结果现实在下面了)
select * from trace_log where log_text like '%nam=''db file %'
and log_text like '%file#=2%'
--=========================================================
WAIT #3: nam='db file sequential read' ela= 16684 file#=2 block#=267 blocks=1 obj#=-1 tim=982399381
WAIT #3: nam='db file sequential read' ela= 273 file#=2 block#=267 blocks=1 obj#=12156 tim=982433422
WAIT #1: nam='db file scattered read' ela= 1124 file#=2 block#=290 blocks=7 obj#=12156 tim=982490414
WAIT #1: nam='db file scattered read' ela= 20914 file#=2 block#=305 blocks=8 obj#=12156 tim=982511667
WAIT #1: nam='db file sequential read' ela= 5579 file#=2 block#=331 blocks=1 obj#=12156 tim=982517738
WAIT #1: nam='db file scattered read' ela= 4957 file#=2 block#=339 blocks=6 obj#=12156 tim=982522927
WAIT #1: nam='db file sequential read' ela= 33125 file#=2 block#=393 blocks=1 obj#=12156 tim=982556325
WAIT #1: nam='db file sequential read' ela= 13470 file#=2 block#=266 blocks=1 obj#=12156 tim=982602382
WAIT #1: nam='db file sequential read' ela= 280 file#=2 block#=351 blocks=1 obj#=12156 tim=982660542
WAIT #1: nam='db file sequential read' ela= 6975 file#=2 block#=354 blocks=1 obj#=12156 tim=982667685
WAIT #1: nam='db file scattered read' ela= 4694 file#=2 block#=363 blocks=6 obj#=12156 tim=982672586
WAIT #1: nam='db file scattered read' ela= 3592 file#=2 block#=379 blocks=6 obj#=12156 tim=982676443
WAIT #1: nam='db file sequential read' ela= 239 file#=2 block#=386 blocks=1 obj#=12156 tim=982676915
WAIT #1: nam='db file scattered read' ela= 3604 file#=2 block#=395 blocks=8 obj#=12156 tim=982680706
WAIT #1: nam='db file scattered read' ela= 3534 file#=2 block#=405 blocks=8 obj#=12156 tim=982684562
WAIT #1: nam='db file sequential read' ela= 2293 file#=2 block#=423 blocks=1 obj#=12156 tim=982687219
WAIT #1: nam='db file scattered read' ela= 1984 file#=2 block#=425 blocks=8 obj#=12156 tim=982689389
WAIT #1: nam='db file scattered read' ela= 2261 file#=2 block#=436 blocks=8 obj#=12156 tim=982692043
WAIT #1: nam='db file scattered read' ela= 15988 file#=2 block#=444 blocks=8 obj#=12156 tim=982708397
WAIT #1: nam='db file scattered read' ela= 21134 file#=2 block#=452 blocks=5 obj#=12156 tim=982730046
WAIT #1: nam='db file scattered read' ela= 3052 file#=2 block#=463 blocks=8 obj#=12156 tim=982733331
WAIT #1: nam='db file scattered read' ela= 2103 file#=2 block#=473 blocks=8 obj#=12156 tim=982735768
WAIT #1: nam='db file scattered read' ela= 2003 file#=2 block#=483 blocks=8 obj#=12156 tim=982738152
WAIT #1: nam='db file scattered read' ela= 2014 file#=2 block#=493 blocks=8 obj#=12156 tim=982740565
WAIT #1: nam='db file scattered read' ela= 4926 file#=2 block#=509 blocks=8 obj#=12156 tim=982745877
WAIT #1: nam='db file scattered read' ela= 8683 file#=2 block#=541 blocks=8 obj#=12156 tim=982755043
WAIT #1: nam='db file scattered read' ela= 1547 file#=2 block#=549 blocks=8 obj#=12156 tim=982756975
WAIT #1: nam='db file scattered read' ela= 3247 file#=2 block#=564 blocks=8 obj#=12156 tim=982760591
WAIT #1: nam='db file scattered read' ela= 2802 file#=2 block#=578 blocks=7 obj#=12156 tim=982763831
WAIT #1: nam='db file scattered read' ela= 1603 file#=2 block#=586 blocks=8 obj#=12156 tim=982765889
WAIT #1: nam='db file scattered read' ela= 9744 file#=2 block#=620 blocks=8 obj#=12156 tim=982776015
WAIT #1: nam='db file scattered read' ela= 5761 file#=2 block#=642 blocks=7 obj#=12156 tim=982782160
WAIT #1: nam='db file scattered read' ela= 2004 file#=2 block#=651 blocks=8 obj#=12156 tim=982784461
WAIT #1: nam='db file scattered read' ela= 2090 file#=2 block#=661 blocks=8 obj#=12156 tim=982786875
WAIT #1: nam='db file scattered read' ela= 1521 file#=2 block#=669 blocks=8 obj#=12156 tim=982788805
WAIT #1: nam='db file scattered read' ela= 1151 file#=2 block#=677 blocks=8 obj#=12156 tim=982790814
WAIT #1: nam='db file scattered read' ela= 4248 file#=2 block#=691 blocks=8 obj#=12156 tim=982795556
WAIT #1: nam='db file scattered read' ela= 2742 file#=2 block#=704 blocks=8 obj#=12156 tim=982798693
WAIT #3: nam='db file scattered read' ela= 703 file#=2 block#=268 blocks=5 obj#=12156 tim=982813457
WAIT #3: nam='db file scattered read' ela= 863 file#=2 block#=273 blocks=8 obj#=12156 tim=982814656
WAIT #3: nam='db file scattered read' ela= 859 file#=2 block#=282 blocks=7 obj#=12156 tim=982816001
WAIT #3: nam='db file sequential read' ela= 239 file#=2 block#=289 blocks=1 obj#=12156 tim=982816546
WAIT #3: nam='db file scattered read' ela= 939 file#=2 block#=298 blocks=7 obj#=12156 tim=982817841
WAIT #3: nam='db file scattered read' ela= 11318 file#=2 block#=314 blocks=7 obj#=12156 tim=982829734
WAIT #3: nam='db file scattered read' ela= 902 file#=2 block#=321 blocks=8 obj#=12156 tim=982830988
WAIT #3: nam='db file sequential read' ela= 263 file#=2 block#=330 blocks=1 obj#=12156 tim=982831727
WAIT #3: nam='db file scattered read' ela= 673 file#=2 block#=332 blocks=5 obj#=12156 tim=982832572
WAIT #3: nam='db file scattered read' ela= 366 file#=2 block#=337 blocks=2 obj#=12156 tim=982833201
WAIT #3: nam='db file scattered read' ela= 662 file#=2 block#=346 blocks=5 obj#=12156 tim=982834226
WAIT #3: nam='db file sequential read' ela= 458 file#=2 block#=352 blocks=1 obj#=12156 tim=982834938
WAIT #3: nam='db file sequential read' ela= 292 file#=2 block#=353 blocks=1 obj#=12156 tim=982835346
WAIT #3: nam='db file scattered read' ela= 22570 file#=2 block#=355 blocks=6 obj#=12156 tim=982858092
WAIT #3: nam='db file sequential read' ela= 457 file#=2 block#=362 blocks=1 obj#=12156 tim=982858859
WAIT #3: nam='db file scattered read' ela= 4139 file#=2 block#=369 blocks=8 obj#=12156 tim=982863301
WAIT #3: nam='db file sequential read' ela= 259 file#=2 block#=378 blocks=1 obj#=12156 tim=982864018
WAIT #3: nam='db file sequential read' ela= 1160 file#=2 block#=385 blocks=1 obj#=12156 tim=982865468
WAIT #3: nam='db file scattered read' ela= 1604 file#=2 block#=387 blocks=6 obj#=12156 tim=982867246
WAIT #3: nam='db file scattered read' ela= 2293 file#=2 block#=403 blocks=2 obj#=12156 tim=982870089
WAIT #3: nam='db file scattered read' ela= 4851 file#=2 block#=413 blocks=8 obj#=12156 tim=982875371
WAIT #3: nam='db file scattered read' ela= 368 file#=2 block#=421 blocks=2 obj#=12156 tim=982876180
WAIT #3: nam='db file sequential read' ela= 254 file#=2 block#=424 blocks=1 obj#=12156 tim=982876614
WAIT #3: nam='db file scattered read' ela= 2230 file#=2 block#=433 blocks=3 obj#=12156 tim=982879237
WAIT #3: nam='db file scattered read' ela= 6942 file#=2 block#=457 blocks=6 obj#=12156 tim=982887031
WAIT #3: nam='db file scattered read' ela= 1795 file#=2 block#=471 blocks=2 obj#=12156 tim=982889379
WAIT #3: nam='db file scattered read' ela= 1986 file#=2 block#=481 blocks=2 obj#=12156 tim=982891794
WAIT #3: nam='db file scattered read' ela= 2002 file#=2 block#=491 blocks=2 obj#=12156 tim=982894206
WAIT #3: nam='db file scattered read' ela= 4832 file#=2 block#=501 blocks=8 obj#=12156 tim=982899483
WAIT #3: nam='db file scattered read' ela= 1967 file#=2 block#=517 blocks=4 obj#=12156 tim=982902444
WAIT #3: nam='db file scattered read' ela= 2071 file#=2 block#=523 blocks=8 obj#=12156 tim=982904789
WAIT #3: nam='db file scattered read' ela= 1455 file#=2 block#=531 blocks=8 obj#=12156 tim=982906719
WAIT #3: nam='db file scattered read' ela= 18628 file#=2 block#=539 blocks=2 obj#=12156 tim=982925798
WAIT #3: nam='db file scattered read' ela= 21149 file#=2 block#=557 blocks=7 obj#=12156 tim=982947633
WAIT #3: nam='db file scattered read' ela= 2756 file#=2 block#=572 blocks=6 obj#=12156 tim=982950988
WAIT #3: nam='db file sequential read' ela= 1358 file#=2 block#=585 blocks=1 obj#=12156 tim=982952826
WAIT #3: nam='db file scattered read' ela= 4907 file#=2 block#=594 blocks=8 obj#=12156 tim=982958145
WAIT #3: nam='db file scattered read' ela= 1435 file#=2 block#=602 blocks=8 obj#=12156 tim=982960070
WAIT #3: nam='db file scattered read' ela= 1430 file#=2 block#=610 blocks=8 obj#=12156 tim=982962004
WAIT #3: nam='db file scattered read' ela= 781 file#=2 block#=618 blocks=2 obj#=12156 tim=982963255
WAIT #3: nam='db file scattered read' ela= 21947 file#=2 block#=628 blocks=8 obj#=12156 tim=982985615
WAIT #3: nam='db file scattered read' ela= 837 file#=2 block#=636 blocks=6 obj#=12156 tim=982986972
WAIT #3: nam='db file scattered read' ela= 506 file#=2 block#=659 blocks=2 obj#=12156 tim=982988237
WAIT #3: nam='db file scattered read' ela= 777 file#=2 block#=685 blocks=6 obj#=12156 tim=982989847
WAIT #3: nam='db file scattered read' ela= 677 file#=2 block#=699 blocks=5 obj#=12156 tim=982991063
WAIT #3: nam='db file sequential read' ela= 10261 file#=2 block#=712 blocks=1 obj#=12156 tim=983001784
--===============================================================
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 8
db_file_name_convert
string
db_files
integer 200
--修改db_file_multiblock_read_count为16看看物理都的变化
SQL> alter system set db_file_multiblock_read_count=16;
系统已更改。
SQL> connect / as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect test/test
已连接。
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 16
db_file_name_convert
string
db_files
integer 200
SQL> select count(*) from v$bh where objd=12156;
COUNT(*)
----------
0
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
4
1
2
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select count(*) from t;
COUNT(*)
----------
34062
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
63
1
2
SQL> select 63 - 4 from dual;
63-4
----------
59
--通过v$filestat的字段phyrds计算的物理读和trace file中获得的相同都是59，而且
比db_file_multiblock_read_count=8时的物理读下降了近30个
SQL> select rownum,log_text from trace_log where log_text like '%nam=''db file %
'
2and log_text like '%file#=2%';
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
1 WAIT #2: nam='db file sequential read' ela= 16346 file#=2 block#=267
blocks=1 obj#=-1 tim=3877514970
2 WAIT #2: nam='db file sequential read' ela= 825 file#=2 block#=267 bl
ocks=1 obj#=12156 tim=3877541282
3 WAIT #4: nam='db file scattered read' ela= 899 file#=2 block#=290 blo
cks=7 obj#=12156 tim=3877583385
4 WAIT #4: nam='db file scattered read' ela= 21225 file#=2 block#=305 b
locks=8 obj#=12156 tim=3877604963
5 WAIT #4: nam='db file sequential read' ela= 17426 file#=2 block#=331
blocks=1 obj#=12156 tim=3877626907
6 WAIT #4: nam='db file scattered read' ela= 13277 file#=2 block#=339 b
locks=6 obj#=12156 tim=3877640529
7 WAIT #4: nam='db file sequential read' ela= 19323 file#=2 block#=351
blocks=1 obj#=12156 tim=3877660148
8 WAIT #4: nam='db file sequential read' ela= 21108 file#=2 block#=354
blocks=1 obj#=12156 tim=3877681423
9 WAIT #4: nam='db file scattered read' ela= 2282 file#=2 block#=363 bl
ocks=6 obj#=12156 tim=3877683900
10 WAIT #4: nam='db file scattered read' ela= 18794 file#=2 block#=379 b
locks=6 obj#=12156 tim=3877702970
11 WAIT #4: nam='db file sequential read' ela= 363 file#=2 block#=386 bl
ocks=1 obj#=12156 tim=3877703580
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
12 WAIT #4: nam='db file scattered read' ela= 40159 file#=2 block#=395 b
locks=16 obj#=12156 tim=3877743940
13 WAIT #4: nam='db file scattered read' ela= 6065 file#=2 block#=423 bl
ocks=16 obj#=12156 tim=3877750685
14 WAIT #4: nam='db file scattered read' ela= 5874 file#=2 block#=444 bl
ocks=16 obj#=12156 tim=3877757356
15 WAIT #4: nam='db file scattered read' ela= 3640 file#=2 block#=463 bl
ocks=16 obj#=12156 tim=3877761775
16 WAIT #4: nam='db file scattered read' ela= 4194 file#=2 block#=483 bl
ocks=16 obj#=12156 tim=3877766597
17 WAIT #4: nam='db file scattered read' ela= 6504 file#=2 block#=509 bl
ocks=12 obj#=12156 tim=3877773802
18 WAIT #4: nam='db file scattered read' ela= 9289 file#=2 block#=541 bl
ocks=16 obj#=12156 tim=3877783486
19 WAIT #4: nam='db file scattered read' ela= 38222 file#=2 block#=564 b
locks=16 obj#=12156 tim=3877822370
20 WAIT #4: nam='db file scattered read' ela= 5097 file#=2 block#=582 bl
ocks=16 obj#=12156 tim=3877828142
21 WAIT #4: nam='db file scattered read' ela= 8440 file#=2 block#=620 bl
ocks=16 obj#=12156 tim=3877837314
22 WAIT #4: nam='db file scattered read' ela= 4148 file#=2 block#=642 bl
ocks=7 obj#=12156 tim=3877842022
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
23 WAIT #4: nam='db file scattered read' ela= 4142 file#=2 block#=651 bl
ocks=16 obj#=12156 tim=3877846484
24 WAIT #4: nam='db file scattered read' ela= 4561 file#=2 block#=669 bl
ocks=16 obj#=12156 tim=3877851445
25 WAIT #4: nam='db file scattered read' ela= 5535 file#=2 block#=691 bl
ocks=16 obj#=12156 tim=3877857589
26 WAIT #4: nam='db file scattered read' ela= 835 file#=2 block#=707 blo
cks=6 obj#=12156 tim=3877858860
27 WAIT #2: nam='db file scattered read' ela= 697 file#=2 block#=268 blo
cks=5 obj#=12156 tim=3877861084
28 WAIT #2: nam='db file scattered read' ela= 985 file#=2 block#=273 blo
cks=8 obj#=12156 tim=3877862379
29 WAIT #2: nam='db file scattered read' ela= 873 file#=2 block#=282 blo
cks=7 obj#=12156 tim=3877863646
30 WAIT #2: nam='db file sequential read' ela= 336 file#=2 block#=289 bl
ocks=1 obj#=12156 tim=3877864266
31 WAIT #2: nam='db file scattered read' ela= 898 file#=2 block#=298 blo
cks=7 obj#=12156 tim=3877865539
32 WAIT #2: nam='db file scattered read' ela= 977 file#=2 block#=314 blo
cks=7 obj#=12156 tim=3877867125
33 WAIT #2: nam='db file scattered read' ela= 975 file#=2 block#=321 blo
cks=8 obj#=12156 tim=3877868447
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
34 WAIT #2: nam='db file sequential read' ela= 261 file#=2 block#=330 bl
ocks=1 obj#=12156 tim=3877869059
35 WAIT #2: nam='db file scattered read' ela= 664 file#=2 block#=332 blo
cks=5 obj#=12156 tim=3877869903
36 WAIT #2: nam='db file scattered read' ela= 374 file#=2 block#=337 blo
cks=2 obj#=12156 tim=3877870555
37 WAIT #2: nam='db file scattered read' ela= 720 file#=2 block#=346 blo
cks=5 obj#=12156 tim=3877871650
38 WAIT #2: nam='db file sequential read' ela= 602 file#=2 block#=352 bl
ocks=1 obj#=12156 tim=3877872532
39 WAIT #2: nam='db file sequential read' ela= 256 file#=2 block#=353 bl
ocks=1 obj#=12156 tim=3877872896
40 WAIT #2: nam='db file scattered read' ela= 776 file#=2 block#=355 blo
cks=6 obj#=12156 tim=3877873857
41 WAIT #2: nam='db file sequential read' ela= 271 file#=2 block#=362 bl
ocks=1 obj#=12156 tim=3877874450
42 WAIT #2: nam='db file scattered read' ela= 976 file#=2 block#=369 blo
cks=8 obj#=12156 tim=3877875759
43 WAIT #2: nam='db file sequential read' ela= 261 file#=2 block#=378 bl
ocks=1 obj#=12156 tim=3877876605
44 WAIT #2: nam='db file sequential read' ela= 260 file#=2 block#=385 bl
ocks=1 obj#=12156 tim=3877877159
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
45 WAIT #2: nam='db file scattered read' ela= 784 file#=2 block#=387 blo
cks=6 obj#=12156 tim=3877878127
46 WAIT #2: nam='db file scattered read' ela= 12557 file#=2 block#=411 b
locks=12 obj#=12156 tim=3877891457
47 WAIT #2: nam='db file scattered read' ela= 3951 file#=2 block#=439 bl
ocks=5 obj#=12156 tim=3877896384
48 WAIT #2: nam='db file scattered read' ela= 4793 file#=2 block#=460 bl
ocks=3 obj#=12156 tim=3877902390
49 WAIT #2: nam='db file scattered read' ela= 4108 file#=2 block#=479 bl
ocks=4 obj#=12156 tim=3877907222
50 WAIT #2: nam='db file scattered read' ela= 7063 file#=2 block#=499 bl
ocks=10 obj#=12156 tim=3877915067
51 WAIT #2: nam='db file scattered read' ela= 6430 file#=2 block#=523 bl
ocks=16 obj#=12156 tim=3877922359
52 WAIT #2: nam='db file scattered read' ela= 355 file#=2 block#=539 blo
cks=2 obj#=12156 tim=3877923326
53 WAIT #2: nam='db file scattered read' ela= 5709 file#=2 block#=557 bl
ocks=7 obj#=12156 tim=3877929698
54 WAIT #2: nam='db file scattered read' ela= 3439 file#=2 block#=580 bl
ocks=2 obj#=12156 tim=3877933968
55 WAIT #2: nam='db file scattered read' ela= 8681 file#=2 block#=598 bl
ocks=16 obj#=12156 tim=3877943362
ROWNUM LOG_TEXT
---------- ---------------------------------------------------------------------
-----------------------------------------
56 WAIT #2: nam='db file scattered read' ela= 770 file#=2 block#=614 blo
cks=6 obj#=12156 tim=3877945037
57 WAIT #2: nam='db file scattered read' ela= 5540 file#=2 block#=636 bl
ocks=6 obj#=12156 tim=3877951396
58 WAIT #2: nam='db file scattered read' ela= 5452 file#=2 block#=667 bl
ocks=2 obj#=12156 tim=3877957839
59 WAIT #2: nam='db file scattered read' ela= 11493 file#=2 block#=685 b
locks=6 obj#=12156 tim=3877970662
已选择59行。
--查询segment header block
SQL> select header_block from dba_segments where segment_name='T' and owner='TES
T';
HEADER_BLOCK
------------
267
--再来看看db_file_multiblock_read_count=32时的情况
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 16
db_file_name_convert
string
db_files
integer 200
SQL> alter system set db_file_multiblock_read_count=32;
系统已更改。
SQL> connect / as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect test/test
已连接。
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 32
db_file_name_convert
string
db_files
integer 200
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
4
1
2
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select count(*) from t;
COUNT(*)
----------
34062
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
49
1
2
SQL> select 49 - 4 from dual;
49-4
----------
45
SQL> select spid from v$process where addr=(select paddr from v$session where si
d=(select sid from v$mystat where rownum=1));
SPID
------------
1076
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>e:
E:\oracle\product\10.2.0\admin\test\udump>dir *1076*
驱动器 E 中的卷没有标签。
卷的序列号是 F6E5-4B31
E:\oracle\product\10.2.0\admin\test\udump 的目录
2008-09-2409:49 195,345 tsid_ora_1076.trc
1 个文件195,345 字节
0 个目录2,766,163,968 可用字节
E:\oracle\product\10.2.0\admin\test\udump>exit
SQL> alter table trace_log location('tsid_ora_1076.trc');
表已更改。
SQL> select count(*) from trace_log where log_text like '%nam=''db file %'
2and log_text like '%file#=2%';
COUNT(*)
----------
45
--通过v$filestat中获得和trace file计算出来的相同都是45，而且比db_file_multiblock_read_count=16时
又下降了59 - 45 = 14个
--如果根据dba_extents中的extent再结合db_file_multiblock_read_count来计算的话，
那么db_file_multiblock_read_count=16,物理读的理想值是40，db_file_multiblock_read_count=32
物理读的值是28，但是实际情况其实很难得到这个理想的值，因为oracle在读取block时不能跨越extent，
同时如果这个block在memory中存在的话，那么这个block就不需要重新读到memeory中，这在最后面也通过
试验简单的测试了一下。
SQL> select 16+128/16*3 from dual;
16+128/16*3
-----------
40
SQL> select 16+128/32*3 from dual;
16+128/32*3
-----------
28
--db_file_multiblock_read_count＝32时trace file获得的结果：
select * from trace_log where log_text like '%nam=''db file %'
and log_text like '%file#=2%'
--================================================================================
WAIT #4: nam='db file sequential read' ela= 16936 file#=2 block#=267 blocks=1 obj#=-1 tim=6132259380
WAIT #4: nam='db file sequential read' ela= 280 file#=2 block#=267 blocks=1 obj#=12156 tim=6132296943
WAIT #1: nam='db file scattered read' ela= 49081 file#=2 block#=290 blocks=7 obj#=12156 tim=6132477039
WAIT #1: nam='db file scattered read' ela= 27651 file#=2 block#=305 blocks=8 obj#=12156 tim=6132505128
WAIT #1: nam='db file sequential read' ela= 38140 file#=2 block#=331 blocks=1 obj#=12156 tim=6132544363
WAIT #1: nam='db file scattered read' ela= 114055 file#=2 block#=339 blocks=6 obj#=12156 tim=6132658698
WAIT #1: nam='db file sequential read' ela= 45397 file#=2 block#=351 blocks=1 obj#=12156 tim=6132704707
WAIT #1: nam='db file sequential read' ela= 33614 file#=2 block#=354 blocks=1 obj#=12156 tim=6132738780
WAIT #1: nam='db file scattered read' ela= 37917 file#=2 block#=363 blocks=6 obj#=12156 tim=6132777007
WAIT #1: nam='db file scattered read' ela= 36728 file#=2 block#=379 blocks=6 obj#=12156 tim=6132814198
WAIT #1: nam='db file sequential read' ela= 55680 file#=2 block#=386 blocks=1 obj#=12156 tim=6132870143
WAIT #1: nam='db file scattered read' ela= 43977 file#=2 block#=395 blocks=32 obj#=12156 tim=6132914549
WAIT #1: nam='db file scattered read' ela= 41739 file#=2 block#=429 blocks=32 obj#=12156 tim=6132957543
WAIT #1: nam='db file scattered read' ela= 51240 file#=2 block#=463 blocks=32 obj#=12156 tim=6133010165
WAIT #1: nam='db file scattered read' ela= 61744 file#=2 block#=496 blocks=25 obj#=12156 tim=6133073808
WAIT #1: nam='db file scattered read' ela= 57624 file#=2 block#=541 blocks=32 obj#=12156 tim=6133132370
WAIT #1: nam='db file scattered read' ela= 54080 file#=2 block#=578 blocks=32 obj#=12156 tim=6133187290
WAIT #1: nam='db file scattered read' ela= 64862 file#=2 block#=620 blocks=29 obj#=12156 tim=6133253629
WAIT #1: nam='db file scattered read' ela= 62955 file#=2 block#=651 blocks=32 obj#=12156 tim=6133317449
WAIT #1: nam='db file scattered read' ela= 35388 file#=2 block#=683 blocks=30 obj#=12156 tim=6133353692
WAIT #4: nam='db file scattered read' ela= 715 file#=2 block#=268 blocks=5 obj#=12156 tim=6133356436
WAIT #4: nam='db file scattered read' ela= 958 file#=2 block#=273 blocks=8 obj#=12156 tim=6133357782
WAIT #4: nam='db file scattered read' ela= 870 file#=2 block#=282 blocks=7 obj#=12156 tim=6133359743
WAIT #4: nam='db file sequential read' ela= 228 file#=2 block#=289 blocks=1 obj#=12156 tim=6133360408
WAIT #4: nam='db file scattered read' ela= 8898 file#=2 block#=298 blocks=7 obj#=12156 tim=6133369798
WAIT #4: nam='db file scattered read' ela= 4284 file#=2 block#=314 blocks=7 obj#=12156 tim=6133374987
WAIT #4: nam='db file scattered read' ela= 1475 file#=2 block#=321 blocks=8 obj#=12156 tim=6133376923
WAIT #4: nam='db file sequential read' ela= 238 file#=2 block#=330 blocks=1 obj#=12156 tim=6133377638
WAIT #4: nam='db file scattered read' ela= 970 file#=2 block#=332 blocks=5 obj#=12156 tim=6133378809
WAIT #4: nam='db file scattered read' ela= 337 file#=2 block#=337 blocks=2 obj#=12156 tim=6133379493
WAIT #4: nam='db file scattered read' ela= 2170 file#=2 block#=346 blocks=5 obj#=12156 tim=6133382189
WAIT #4: nam='db file sequential read' ela= 220 file#=2 block#=352 blocks=1 obj#=12156 tim=6133382794
WAIT #4: nam='db file sequential read' ela= 237 file#=2 block#=353 blocks=1 obj#=12156 tim=6133383159
WAIT #4: nam='db file scattered read' ela= 2508 file#=2 block#=355 blocks=6 obj#=12156 tim=6133385863
WAIT #4: nam='db file sequential read' ela= 244 file#=2 block#=362 blocks=1 obj#=12156 tim=6133386520
WAIT #4: nam='db file scattered read' ela= 2996 file#=2 block#=369 blocks=8 obj#=12156 tim=6133389949
WAIT #4: nam='db file sequential read' ela= 234 file#=2 block#=378 blocks=1 obj#=12156 tim=6133390653
WAIT #4: nam='db file sequential read' ela= 950 file#=2 block#=385 blocks=1 obj#=12156 tim=6133392003
WAIT #4: nam='db file scattered read' ela= 1582 file#=2 block#=387 blocks=6 obj#=12156 tim=6133393778
WAIT #4: nam='db file scattered read' ela= 8220 file#=2 block#=427 blocks=2 obj#=12156 tim=6133403866
WAIT #4: nam='db file scattered read' ela= 7795 file#=2 block#=461 blocks=2 obj#=12156 tim=6133413506
WAIT #4: nam='db file sequential read' ela= 5708 file#=2 block#=495 blocks=1 obj#=12156 tim=6133421434
WAIT #4: nam='db file scattered read' ela= 10839 file#=2 block#=523 blocks=18 obj#=12156 tim=6133433995
WAIT #4: nam='db file scattered read' ela= 7612 file#=2 block#=573 blocks=5 obj#=12156 tim=6133444194
WAIT #4: nam='db file scattered read' ela= 7093 file#=2 block#=610 blocks=10 obj#=12156 tim=6133455841
--================================================================================
SQL> connect /as sysdba
已连接。
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 32
db_file_name_convert
string
db_files
integer 200
SQL> alter system set db_file_multiblock_read_count=256;
系统已更改。
SQL> show parameter db_block_size
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_block_size
integer 8192
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect test/test
已连接。
SQL> show parameter db_file
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_countinteger 128
db_file_name_convert
string
db_files
integer 200
--尽管我们在上面把db_file_multiblock_read_count的值设置为256，但显示的结果
为db_file_multiblock_read_count＝128，是因为该参数收到了os的限制，正如doc所言：
This value is platform-dependent and is 1MB for most platforms.
--下面简单测试下db_file_multiblock_read_count=128时物理读的情况
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
4
1
2
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select count(*) from t;
COUNT(*)
----------
34062
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
38
1
2
--如果都能按照128个block来读取的话，仅仅需要19个物理读，但实际是34个
SQL> select 16+128/128*3 from dual;
16+128/128*3
------------
19
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>e:
E:\>exit
SQL> select spid from v$process where addr=(select paddr from v$session where si
d=(select sid from v$mystat where rownum=1));
SPID
------------
512
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>e:
E:\>cd E:\oracle\product\10.2.0\admin\test\udump
E:\oracle\product\10.2.0\admin\test\udump>dir *512*
驱动器 E 中的卷没有标签。
卷的序列号是 F6E5-4B31
E:\oracle\product\10.2.0\admin\test\udump 的目录
2008-09-2409:42 158,004 tsid_ora_2036.trc
2008-09-2410:00 194,194 tsid_ora_512.trc
2 个文件352,198 字节
0 个目录2,765,938,688 可用字节
E:\oracle\product\10.2.0\admin\test\udump>exit
SQL> alter table trace_log location('tsid_ora_512.trc');
表已更改。
SQL> select count(*) from trace_log where log_text like '%nam=''db file %'
2and log_text like '%file#=2%';
COUNT(*)
----------
34
sql>
select * from trace_log where log_text like '%nam=''db file %'
and log_text like '%file#=2%'
--=========================================================
WAIT #4: nam='db file sequential read' ela= 15454 file#=2 block#=267 blocks=1 obj#=-1 tim=6810740480
WAIT #4: nam='db file sequential read' ela= 294 file#=2 block#=267 blocks=1 obj#=12156 tim=6810795518
WAIT #1: nam='db file scattered read' ela= 910 file#=2 block#=290 blocks=7 obj#=12156 tim=6810853549
WAIT #1: nam='db file scattered read' ela= 21212 file#=2 block#=305 blocks=8 obj#=12156 tim=6810875120
WAIT #1: nam='db file sequential read' ela= 5419 file#=2 block#=331 blocks=1 obj#=12156 tim=6810881088
WAIT #1: nam='db file scattered read' ela= 4938 file#=2 block#=339 blocks=6 obj#=12156 tim=6810886230
WAIT #1: nam='db file sequential read' ela= 399 file#=2 block#=351 blocks=1 obj#=12156 tim=6810886900
WAIT #1: nam='db file sequential read' ela= 261 file#=2 block#=354 blocks=1 obj#=12156 tim=6810887300
WAIT #1: nam='db file scattered read' ela= 4361 file#=2 block#=363 blocks=6 obj#=12156 tim=6810891852
WAIT #1: nam='db file scattered read' ela= 3155 file#=2 block#=379 blocks=6 obj#=12156 tim=6810895317
WAIT #1: nam='db file sequential read' ela= 244 file#=2 block#=386 blocks=1 obj#=12156 tim=6810895769
WAIT #1: nam='db file scattered read' ela= 44901 file#=2 block#=395 blocks=126 obj#=12156 tim=6810941168
WAIT #1: nam='db file scattered read' ela= 36298 file#=2 block#=541 blocks=108 obj#=12156 tim=6810980260
WAIT #1: nam='db file scattered read' ela= 15180 file#=2 block#=651 blocks=62 obj#=12156 tim=6810997209
WAIT #4: nam='db file scattered read' ela= 735 file#=2 block#=268 blocks=5 obj#=12156 tim=6811000609
WAIT #4: nam='db file scattered read' ela= 988 file#=2 block#=273 blocks=8 obj#=12156 tim=6811002006
WAIT #4: nam='db file scattered read' ela= 863 file#=2 block#=282 blocks=7 obj#=12156 tim=6811003386
WAIT #4: nam='db file sequential read' ela= 253 file#=2 block#=289 blocks=1 obj#=12156 tim=6811004428
WAIT #4: nam='db file scattered read' ela= 12919 file#=2 block#=298 blocks=7 obj#=12156 tim=6811017854
WAIT #4: nam='db file scattered read' ela= 4091 file#=2 block#=314 blocks=7 obj#=12156 tim=6811022871
WAIT #4: nam='db file scattered read' ela= 1469 file#=2 block#=321 blocks=8 obj#=12156 tim=6811024812
WAIT #4: nam='db file sequential read' ela= 241 file#=2 block#=330 blocks=1 obj#=12156 tim=6811025611
WAIT #4: nam='db file scattered read' ela= 892 file#=2 block#=332 blocks=5 obj#=12156 tim=6811026697
WAIT #4: nam='db file scattered read' ela= 357 file#=2 block#=337 blocks=2 obj#=12156 tim=6811027718
WAIT #4: nam='db file scattered read' ela= 1854 file#=2 block#=346 blocks=5 obj#=12156 tim=6811030149
WAIT #4: nam='db file sequential read' ela= 255 file#=2 block#=352 blocks=1 obj#=12156 tim=6811030806
WAIT #4: nam='db file sequential read' ela= 242 file#=2 block#=353 blocks=1 obj#=12156 tim=6811031236
WAIT #4: nam='db file scattered read' ela= 1063 file#=2 block#=355 blocks=6 obj#=12156 tim=6811032499
WAIT #4: nam='db file sequential read' ela= 221 file#=2 block#=362 blocks=1 obj#=12156 tim=6811033164
WAIT #4: nam='db file scattered read' ela= 4245 file#=2 block#=369 blocks=8 obj#=12156 tim=6811037844
WAIT #4: nam='db file sequential read' ela= 241 file#=2 block#=378 blocks=1 obj#=12156 tim=6811038619
WAIT #4: nam='db file sequential read' ela= 806 file#=2 block#=385 blocks=1 obj#=12156 tim=6811039889
WAIT #4: nam='db file scattered read' ela= 1593 file#=2 block#=387 blocks=6 obj#=12156 tim=6811041675
WAIT #4: nam='db file scattered read' ela= 10957 file#=2 block#=523 blocks=18 obj#=12156 tim=6811060040
--===========================================================================================
--下面简单测试下物理读受到buffer打断的情况，也就是如果要读的block在
data buffer中已经存在的话，oracle是否会重复读取这个block，答案当然是否定的:
SQL> connect / as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect test/test
已连接。
--以object_id=2这条数据对应的rowid=AAAC98AACAAAAEMAAt为例，验证一下：
SQL> select dbms_rowid.rowid_block_number(rowid) block_no,rowid from t where obj
ect_id=2;
BLOCK_NO ROWID
---------- ------------------
268 AAAC98AACAAAAEMAAt
440 AAAC98AACAAAAG4AAt
557 AAAC98AACAAAAItAAK
SQL> select ts#,name from v$tablespace where name='TEST';
TS# NAME
---------- ------------------------------
5 TEST
SQL> CONNECT / AS SYSDBA
已连接。
SQL> SHUTDOWN IMMEDIATE
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP
ORACLE 例程已经启动。
Total System Global Area163577856 bytes
Fixed Size
1247876 bytes
Variable Size
92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers
2945024 bytes
数据库装载完毕。
数据库已经打开。
--上面重启db的目的是为了flush data buffer
SQL> connect test/test
已连接。
SQL> select file#,block#,status from v$bh where objd=12156 and ts#=5;
未选定行
SQL> select count(*) from t where rowid in ('AAAC98AACAAAAEMAAt');
COUNT(*)
----------
1
SQL> select file#,block#,status from v$bh where objd=12156 and ts#=5;
FILE# BLOCK# STATUS
---------- ---------- -------
2268 xcur
2267 cr
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
6
1
2
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select count(*) from t;
COUNT(*)
----------
34062
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL> select phyrds,phywrts,file# from v$filestat where file#=2;
PHYRDSPHYWRTSFILE#
---------- ---------- ----------
39
1
2
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>exit
SQL> select spid from v$process where addr=(select paddr from v$session where si
d=(select sid from v$mystat where rownum=1));
SPID
------------
1944
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\>e:
E:\>cd E:\oracle\product\10.2.0\admin\test\udump
E:\oracle\product\10.2.0\admin\test\udump>dir *1944*
驱动器 E 中的卷没有标签。
卷的序列号是 F6E5-4B31
E:\oracle\product\10.2.0\admin\test\udump 的目录
2008-09-2410:22
8,847 tsid_ora_1944.trc
1 个文件
8,847 字节
0 个目录2,765,803,520 可用字节
E:\oracle\product\10.2.0\admin\test\udump>exit
SQL> alter table trace_log location('tsid_ora_1944.trc');
表已更改。
SQL> select count(*) from trace_log where log_text like '%nam=''db file %'
2and log_text like '%file#=2%';
COUNT(*)
----------
33
--从下面的查询结果发现block#=268的block并没有被读取
SQL>
select * from trace_log where log_text like '%nam=''db file %'
and log_text like '%file#=2%'
--=====================================================
WAIT #4: nam='db file sequential read' ela= 15100 file#=2 block#=267 blocks=1 obj#=12156 tim=8122152356
WAIT #2: nam='db file scattered read' ela= 915 file#=2 block#=290 blocks=7 obj#=12156 tim=8122415955
WAIT #2: nam='db file scattered read' ela= 53101 file#=2 block#=305 blocks=8 obj#=12156 tim=8122475864
WAIT #2: nam='db file sequential read' ela= 260 file#=2 block#=331 blocks=1 obj#=12156 tim=8122482261
WAIT #2: nam='db file scattered read' ela= 888 file#=2 block#=339 blocks=6 obj#=12156 tim=8122490549
WAIT #2: nam='db file sequential read' ela= 261 file#=2 block#=351 blocks=1 obj#=12156 tim=8122496604
WAIT #2: nam='db file sequential read' ela= 274 file#=2 block#=354 blocks=1 obj#=12156 tim=8122503723
WAIT #2: nam='db file scattered read' ela= 782 file#=2 block#=363 blocks=6 obj#=12156 tim=8122511405
WAIT #2: nam='db file scattered read' ela= 1216 file#=2 block#=379 blocks=6 obj#=12156 tim=8122518958
WAIT #2: nam='db file sequential read' ela= 263 file#=2 block#=386 blocks=1 obj#=12156 tim=8122526866
WAIT #2: nam='db file scattered read' ela= 35193 file#=2 block#=395 blocks=126 obj#=12156 tim=8122568544
WAIT #2: nam='db file scattered read' ela= 36287 file#=2 block#=541 blocks=108 obj#=12156 tim=8122614351
WAIT #2: nam='db file scattered read' ela= 7577 file#=2 block#=651 blocks=62 obj#=12156 tim=8122630573
WAIT #4: nam='db file scattered read' ela= 756 file#=2 block#=269 blocks=4 obj#=12156 tim=8122715967
WAIT #4: nam='db file scattered read' ela= 998 file#=2 block#=273 blocks=8 obj#=12156 tim=8122724442
WAIT #4: nam='db file scattered read' ela= 919 file#=2 block#=282 blocks=7 obj#=12156 tim=8122732228
WAIT #4: nam='db file sequential read' ela= 275 file#=2 block#=289 blocks=1 obj#=12156 tim=8122739569
WAIT #4: nam='db file scattered read' ela= 27490 file#=2 block#=298 blocks=7 obj#=12156 tim=8122773861
WAIT #4: nam='db file scattered read' ela= 896 file#=2 block#=314 blocks=7 obj#=12156 tim=8122781572
WAIT #4: nam='db file scattered read' ela= 1014 file#=2 block#=321 blocks=8 obj#=12156 tim=8122794931
WAIT #4: nam='db file sequential read' ela= 289 file#=2 block#=330 blocks=1 obj#=12156 tim=8122802671
WAIT #4: nam='db file scattered read' ela= 699 file#=2 block#=332 blocks=5 obj#=12156 tim=8122810154
WAIT #4: nam='db file scattered read' ela= 398 file#=2 block#=337 blocks=2 obj#=12156 tim=8122817783
WAIT #4: nam='db file scattered read' ela= 689 file#=2 block#=346 blocks=5 obj#=12156 tim=8122825502
WAIT #4: nam='db file sequential read' ela= 454 file#=2 block#=352 blocks=1 obj#=12156 tim=8122832961
WAIT #4: nam='db file sequential read' ela= 253 file#=2 block#=353 blocks=1 obj#=12156 tim=8122840264
WAIT #4: nam='db file scattered read' ela= 793 file#=2 block#=355 blocks=6 obj#=12156 tim=8122848413
WAIT #4: nam='db file sequential read' ela= 272 file#=2 block#=362 blocks=1 obj#=12156 tim=8122855399
WAIT #4: nam='db file scattered read' ela= 994 file#=2 block#=369 blocks=8 obj#=12156 tim=8122862674
WAIT #4: nam='db file sequential read' ela= 272 file#=2 block#=378 blocks=1 obj#=12156 tim=8122870500
WAIT #4: nam='db file sequential read' ela= 244 file#=2 block#=385 blocks=1 obj#=12156 tim=8122877306
WAIT #4: nam='db file scattered read' ela= 958 file#=2 block#=387 blocks=6 obj#=12156 tim=8122886959
WAIT #4: nam='db file scattered read' ela= 9489 file#=2 block#=523 blocks=18 obj#=12156 tim=8122915940
--================================================================
物理读的情况是比较复杂的：
1、会受到参数db_file_multiblock_read_count的影响已经不用多说了，为了
减少物理读，需要设置db_file_multiblock_read_count为最大临界值。doc上提到
绝大多数系统是1m，那么db_file_multiblock_read_count=系统的(max I/O size)/db_block_size
系统的(max I/O size)如何准确的获得，也是我的疑问
2、db_cache_size如果很小的话，那么物理读通常都会显著的高，原因是一次读进来
的block无法或者是很少能找到连续的memory来容纳，这样只能通过多次来读取，而且读进来的block只能离散的分布在
memory中，这也正是scattered的含义。
|
