一点深入研究：这个参数究竟如何影响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
|
