参数db_file_multiblock_read_count效果测试

db_file_multiblock_read_count影响oracle一次物理读所读取的数据块的个数。它的效果通过set autot on是看不出来的。需要从等待时间中观察：

创建测试数据

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

Table created.

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS', tabname=>'T', estimate_percent=>100);

首先，看看db_file_multiblock_read_count = 16的情况

SQL> show parameter read

NAME                         TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count          integer     16

清空buffer cache

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

设置10046

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Session altered.

查询sql
SQL>  select * from t where wner = 'asdasdasdasdasdasdasdasdasd';

no rows selected

关闭10046

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Session altered.

查看trace文件

注意blosks接连等于16的地方，下一行的block#正好等于上一行block#加上16，就是说它本来想读很多，但是碍于16这个限制，只能分成多次读了。

PARSING IN CURSOR #1 len=59 dep=0 uid=0 ct=3 lid=0 tim=1321009561857941 hv=2353013503 ad='3a34dfb4'
select * from t where wner = 'asdasdasdasdasdasdasdasdasd'
END OF STMT
PARSE #1:c=3998,e=4551,p=4,cr=4,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009561857936
BINDS #1:

EXEC #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009561858002
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1321009561858043
WAIT #1: nam='db file sequential read' ela= 10 file#=1 block#=59593 blocks=1 obj#=51354 tim=1321009561858102
WAIT #1: nam='db file scattered read' ela= 56 file#=1 block#=59594 blocks=7 obj#=51354 tim=1321009561858267
WAIT #1: nam='db file scattered read' ela= 41 file#=1 block#=59609 blocks=8 obj#=51354 tim=1321009561858417
WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=59617 blocks=8 obj#=51354 tim=1321009561858687
WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#=59625 blocks=8 obj#=51354 tim=1321009561858802
WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#=59633 blocks=8 obj#=51354 tim=1321009561858907
WAIT #1: nam='db file scattered read' ela= 35 file#=1 block#=59641 blocks=8 obj#=51354 tim=1321009561859009
WAIT #1: nam='db file scattered read' ela= 39 file#=1 block#=59649 blocks=8 obj#=51354 tim=1321009561859121
WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=60169 blocks=8 obj#=51354 tim=1321009561859231
WAIT #1: nam='db file scattered read' ela= 35 file#=1 block#=60177 blocks=8 obj#=51354 tim=1321009561859326
WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#=60185 blocks=8 obj#=51354 tim=1321009561859422
WAIT #1: nam='db file scattered read' ela= 35 file#=1 block#=60193 blocks=8 obj#=51354 tim=1321009561859516
WAIT #1: nam='db file scattered read' ela= 38 file#=1 block#=60209 blocks=8 obj#=51354 tim=1321009561859592
WAIT #1: nam='db file scattered read' ela= 37 file#=1 block#=60217 blocks=8 obj#=51354 tim=1321009561859691
WAIT #1: nam='db file scattered read' ela= 35 file#=1 block#=60225 blocks=8 obj#=51354 tim=1321009561859781
WAIT #1: nam='db file scattered read' ela= 37 file#=1 block#=60233 blocks=8 obj#=51354 tim=1321009561859875
WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=60241 blocks=8 obj#=51354 tim=1321009561859974
WAIT #1: nam='db file scattered read' ela= 70 file#=1 block#=60297 blocks=16 obj#=51354 tim=1321009561860130
WAIT #1: nam='db file scattered read' ela= 75 file#=1 block#=60313 blocks=16 obj#=51354 tim=1321009561860281
WAIT #1: nam='db file scattered read' ela= 74 file#=1 block#=60329 blocks=16 obj#=51354 tim=1321009561860427
WAIT #1: nam='db file scattered read' ela= 74 file#=1 block#=60345 blocks=16 obj#=51354 tim=1321009561860606
WAIT #1: nam='db file scattered read' ela= 74 file#=1 block#=60361 blocks=16 obj#=51354 tim=1321009561860773
WAIT #1: nam='db file scattered read' ela= 76 file#=1 block#=60377 blocks=16 obj#=51354 tim=1321009561860935
WAIT #1: nam='db file scattered read' ela= 75 file#=1 block#=60393 blocks=16 obj#=51354 tim=1321009561861083
WAIT #1: nam='db file scattered read' ela= 74 file#=1 block#=60409 blocks=16 obj#=51354 tim=1321009561861230
WAIT #1: nam='db file scattered read' ela= 73 file#=1 block#=60425 blocks=16 obj#=51354 tim=1321009561861375
WAIT #1: nam='db file scattered read' ela= 91 file#=1 block#=60441 blocks=16 obj#=51354 tim=1321009561861538
WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#=60457 blocks=16 obj#=51354 tim=1321009561861679
WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#=60473 blocks=16 obj#=51354 tim=1321009561861819
WAIT #1: nam='db file scattered read' ela= 62 file#=1 block#=60489 blocks=16 obj#=51354 tim=1321009561861966
WAIT #1: nam='db file scattered read' ela= 66 file#=1 block#=60505 blocks=16 obj#=51354 tim=1321009561862115
WAIT #1: nam='db file scattered read' ela= 66 file#=1 block#=60521 blocks=16 obj#=51354 tim=1321009561862252
WAIT #1: nam='db file scattered read' ela= 65 file#=1 block#=60537 blocks=16 obj#=51354 tim=1321009561862386
WAIT #1: nam='db file scattered read' ela= 66 file#=1 block#=60553 blocks=16 obj#=51354 tim=1321009561862547
WAIT #1: nam='db file scattered read' ela= 69 file#=1 block#=60569 blocks=16 obj#=51354 tim=1321009561862682
WAIT #1: nam='db file scattered read' ela= 64 file#=1 block#=60585 blocks=16 obj#=51354 tim=1321009561862815
WAIT #1: nam='db file scattered read' ela= 61 file#=1 block#=60601 blocks=16 obj#=51354 tim=1321009561862943
WAIT #1: nam='db file scattered read' ela= 66 file#=1 block#=60617 blocks=16 obj#=51354 tim=1321009561863082
WAIT #1: nam='db file scattered read' ela= 66 file#=1 block#=60633 blocks=16 obj#=51354 tim=1321009561863216
WAIT #1: nam='db file scattered read' ela= 65 file#=1 block#=60649 blocks=16 obj#=51354 tim=1321009561863351
WAIT #1: nam='db file scattered read' ela= 68 file#=1 block#=60665 blocks=16 obj#=51354 tim=1321009561863510
WAIT #1: nam='db file scattered read' ela= 71 file#=1 block#=60681 blocks=16 obj#=51354 tim=1321009561863662
WAIT #1: nam='db file scattered read' ela= 75 file#=1 block#=60697 blocks=16 obj#=51354 tim=1321009561863806
WAIT #1: nam='db file scattered read' ela= 88 file#=1 block#=60713 blocks=16 obj#=51354 tim=1321009561863958
WAIT #1: nam='db file scattered read' ela= 56 file#=1 block#=60729 blocks=16 obj#=51354 tim=1321009561864031
WAIT #1: nam='db file scattered read' ela= 76 file#=1 block#=60745 blocks=16 obj#=51354 tim=1321009561864182
WAIT #1: nam='db file scattered read' ela= 78 file#=1 block#=60761 blocks=16 obj#=51354 tim=1321009561864326
WAIT #1: nam='db file scattered read' ela= 74 file#=1 block#=60777 blocks=16 obj#=51354 tim=1321009561864472
WAIT #1: nam='db file scattered read' ela= 75 file#=1 block#=60793 blocks=16 obj#=51354 tim=1321009561864613
WAIT #1: nam='db file scattered read' ela= 72 file#=1 block#=60809 blocks=16 obj#=51354 tim=1321009561864754
WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#=60825 blocks=16 obj#=51354 tim=1321009561865061
WAIT #1: nam='db file scattered read' ela= 52 file#=1 block#=60841 blocks=12 obj#=51354 tim=1321009561865180
FETCH #1:c=7000,e=7150,p=684,cr=688,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009561865220
WAIT #1: nam='SQL*Net message from client' ela= 704 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009561866016
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=51354 p='TABLE ACCESS FULL T (cr=688 pr=684 pw=0 time=7150 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009561866151
WAIT #0: nam='SQL*Net message from client' ela= 795670 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009562661842

再看db_file_multiblock_read_count = 128的情况

修改参数

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> show parameter read

NAME                         TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count          integer     128

后面的步骤和之前一样。最后查看trace文件

出现读128块的等待

PARSING IN CURSOR #1 len=59 dep=0 uid=0 ct=3 lid=0 tim=1321009679475510 hv=2353013503 ad='3a34dfb4'
select * from t where wner = 'asdasdasdasdasdasdasdasdasd'
END OF STMT
PARSE #1:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009679475505
BINDS #1:
EXEC #1:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009679475583
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1321009679476041
WAIT #1: nam='db file sequential read' ela= 13 file#=1 block#=59593 blocks=1 obj#=51354 tim=1321009679476129
WAIT #1: nam='db file scattered read' ela= 54 file#=1 block#=59594 blocks=7 obj#=51354 tim=1321009679476266
WAIT #1: nam='db file scattered read' ela= 48 file#=1 block#=59609 blocks=8 obj#=51354 tim=1321009679476434
WAIT #1: nam='db file scattered read' ela= 42 file#=1 block#=59617 blocks=8 obj#=51354 tim=1321009679476615
WAIT #1: nam='db file scattered read' ela= 42 file#=1 block#=59625 blocks=8 obj#=51354 tim=1321009679476673
WAIT #1: nam='db file scattered read' ela= 38 file#=1 block#=59633 blocks=8 obj#=51354 tim=1321009679476792
WAIT #1: nam='db file scattered read' ela= 39 file#=1 block#=59641 blocks=8 obj#=51354 tim=1321009679476910
WAIT #1: nam='db file scattered read' ela= 42 file#=1 block#=59649 blocks=8 obj#=51354 tim=1321009679477017
WAIT #1: nam='db file scattered read' ela= 41 file#=1 block#=60169 blocks=8 obj#=51354 tim=1321009679477107
WAIT #1: nam='db file scattered read' ela= 37 file#=1 block#=60177 blocks=8 obj#=51354 tim=1321009679477218
WAIT #1: nam='db file scattered read' ela= 43 file#=1 block#=60185 blocks=8 obj#=51354 tim=1321009679477328
WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=60193 blocks=8 obj#=51354 tim=1321009679477449
WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=60209 blocks=8 obj#=51354 tim=1321009679477548
WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#=60217 blocks=8 obj#=51354 tim=1321009679477644
WAIT #1: nam='db file scattered read' ela= 35 file#=1 block#=60225 blocks=8 obj#=51354 tim=1321009679477735
WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#=60233 blocks=8 obj#=51354 tim=1321009679477826
WAIT #1: nam='db file scattered read' ela= 95 file#=1 block#=60241 blocks=8 obj#=51354 tim=1321009679478318
WAIT #1: nam='db file scattered read' ela= 1174 file#=1 block#=60297 blocks= 128 obj#=51354 tim=1321009679479889
WAIT #1: nam='db file scattered read' ela= 564 file#=1 block#=60425 blocks= 128 obj#=51354 tim=1321009679481102
WAIT #1: nam='db file scattered read' ela= 671 file#=1 block#=60553 blocks= 128 obj#=51354 tim=1321009679482194
WAIT #1: nam='db file scattered read' ela= 652 file#=1 block#=60681 blocks= 128 obj#=51354 tim=1321009679483426
WAIT #1: nam='db file scattered read' ela= 405 file#=1 block#=60809 blocks=44 obj#=51354 tim=1321009679484404
FETCH #1:c=8998,e=8632,p=684,cr=688,cu=0,mis=0,r=0,dep=0,og=1,tim=1321009679484712
WAIT #1: nam='SQL*Net message from client' ela= 716 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009679485571
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=51354 p='TABLE ACCESS FULL T (cr=688 pr=684 pw=0 time=8605 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009679485739
WAIT #0: nam='SQL*Net message from client' ela= 714984 driver id=1650815232 #bytes=1 p3=0 obj#=51354 tim=1321009680200739

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-749042/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-749042/