一点深入研究：这个参数究竟如何影响Oracle/OS

文件系统：Veritas VXFS With QuickIO

bash-2.03$ qiomkfile -s 200m yong.dbf

bash-2.03$ sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 18 14:52:47 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.7.2.0 - 64bit Production

SQL> create tablespace yong datafile '/oradata/standby/backup/yong.dbf' reuse

   2  extent management local uniform size 1m;

Tablespace created.

SQL> SQL>

SQL> alter table bidder.t move tablespace yong;

Table altered.

SQL> conn internal

Connected.

SQL> alter tablespace yong offline;

Tablespace altered.

SQL> alter tablespace yong online;

Tablespace altered.

SQL> conn bidder/bidder

Connected.

SQL> alter session set  db_file_multiblock_read_count=128;

Session altered.

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

Session altered.

SQL> select count(*) from t;

   COUNT(*)

----------

     196736

Elapsed: 00:00:01.71

SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.7.2.0 - 64bit Production

把它改成16，我们再次测试：

SQL>  alter tablespace yong offline;

Tablespace altered.

SQL> SQL> alter tablespace yong online;

Tablespace altered.

SQL>  alter session set  db_file_multiblock_read_count=16;

Session altered.

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

Session altered.

SQL>  set timing on

SQL>  select count(*) from t;

   COUNT(*)

----------

     196736

Elapsed: 00:00:02.12

我们查看生成的相应的Trace文件，我们可以看到，oracle的实际操作：

DB_FILE_MULIBLOCK_READ_COUNT=128:

PARSING IN CURSOR #1 len=36 dep=0 uid=18 oct=3 lid=18 tim=0 hv=2060058659 ad='d2d65230'

SELECT /*+full(t)*/ COUNT(*) FROM T

END OF STMT

PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0

BINDS #1:

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0

WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0

WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=9 p3=1

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=10 p3=127

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=137 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=265 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=393 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=521 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=649 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=777 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=905 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1033 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1161 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1289 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1417 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1545 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1673 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1801 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1929 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2057 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2185 p3=128

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2313 p3=38

FETCH #1:c=0,e=0,p=2342,cr=2346,cu=15,mis=0,r=1,dep=0,og=4,tim=0

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0

FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=0

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0

STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '

STAT #1 id=2 cnt=196736 pid=1 pos=1 obj=3161 op='TABLE ACCESS FULL T '

XCTEND rlbk=0, rd_only=1

DB_FILE_BLOCK_READ_COUNT=16的时候：

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=809 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=825 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=841 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=857 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=873 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=889 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=905 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=921 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=937 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=953 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=969 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=985 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1001 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1017 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1033 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1049 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1065 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1081 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1097 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1113 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1129 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1145 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1161 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1177 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1193 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1209 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1225 p3=16

WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1241 p3=16