一個參數究竟如何影響Oracle/OS
2024-08-29 13:49:50
供稿：網友
一點深入研究：這個參數究竟如何影響Oracle/OS文件系統：Veritas VXFS With QuickIObash-2.03$ qiomkfile -s 200m yong.dbfbash-2.03$ sqlplus internalSQL*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 ProductionWith the Partitioning optionJServer Release 8.1.7.2.0 - 64bit ProductionSQL> 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 internalConnected.SQL> alter tablespace yong offline;Tablespace altered.SQL> alter tablespace yong online;Tablespace altered.SQL> conn bidder/bidderConnected.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(*)---------- 196736Elapsed: 00:00:01.71SQL> exitDisconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit ProductionWith the Partitioning optionJServer 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 onSQL> select count(*) from t; COUNT(*)---------- 196736Elapsed: 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 TEND OF STMTPARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0BINDS #1:EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0WAIT #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=127WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=137 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=265 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=393 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=521 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=649 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=777 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=905 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1033 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1161 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1289 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1417 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1545 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1673 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1801 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1929 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2057 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2185 p3=128WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=2313 p3=38FETCH #1:c=0,e=0,p=2342,cr=2346,cu=15,mis=0,r=1,dep=0,og=4,tim=0WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=0WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0STAT #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=1DB_FILE_BLOCK_READ_COUNT=16的時候：WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=809 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=825 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=841 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=857 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=873 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=889 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=905 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=921 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=937 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=953 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=969 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=985 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1001 p3=16WAIT #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=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1049 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1065 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1081 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1097 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1113 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1129 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1145 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1161 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1177 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1193 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1209 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1225 p3=16WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=1241 p3=16
