AIX环境下的db_file_multiblock_read_count测试

硬件：IBM P5 570   盘阵：IBM ds4300  RAID5

OS:AIX5300-02    DB:ORACLE 9.2.0.7.0

 

SQL> show parameter read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8

SQL> create tablespace sjhtest datafile '/pdmdata/ocu_8.0/oradata/wind8/sjhtest.dbf' size 30M extent management local uniform size 2M;

Tablespace created.

 

SQL> create table test tablespace sjhtest as select * from dba_objects;

Table created.

 

SQL> /
create table test tablespace sjhtest as select * from dba_objects
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> insert into test select * from test;

8839 rows created.

SQL> /

17678 rows created.

SQL> /

35356 rows created.

SQL> /

70712 rows created.

SQL> /  

141424 rows created.

 

SQL> commit;

Commit complete.

 

SQL> alter session set db_file_multiblock_read_count=800;

Session altered.

SQL> show parameter read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     64

 

--10G已经支持128.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter system flush buffer_cache;
alter system flush buffer_cache
                   *
ERROR at line 1:
ORA-02000: missing SHARED_POOL keyword

 

--用10g的方法不行。

 

SQL> alter session set events 'immediate trace name flush_cache level 1'  2  ;                                                                                   

Session altered.

 

SQL> select count(*) from test;

  COUNT(*)
----------
    282848

 

--查看trace文件：

PARSING IN CURSOR #1 len=25 dep=0 uid=0 oct=3 lid=0 tim=25136717056319 hv=1872856941 ad='3588e9b0'
select count(*) from test
END OF STMT
PARSE #1:c=0,e=11286,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=25136717056317
BINDS #1:
EXEC #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=25136717056458
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 19 p1=9 p2=5 p3=1
WAIT #1: nam='db file scattered read' ela= 971 p1=9 p2=6 p3=64
WAIT #1: nam='db file scattered read' ela= 684 p1=9 p2=70 p3=63
WAIT #1: nam='db file scattered read' ela= 690 p1=9 p2=133 p3=64
WAIT #1: nam='db file scattered read' ela= 684 p1=9 p2=197 p3=64
WAIT #1: nam='db file scattered read' ela= 688 p1=9 p2=261 p3=64
WAIT #1: nam='db file scattered read' ela= 714 p1=9 p2=325 p3=64
WAIT #1: nam='db file scattered read' ela= 679 p1=9 p2=389 p3=64
WAIT #1: nam='db file scattered read' ela= 677 p1=9 p2=453 p3=64
WAIT #1: nam='db file scattered read' ela= 662 p1=9 p2=517 p3=64
WAIT #1: nam='db file scattered read' ela= 713 p1=9 p2=581 p3=64
WAIT #1: nam='db file scattered read' ela= 760 p1=9 p2=645 p3=64
WAIT #1: nam='db file scattered read' ela= 773 p1=9 p2=709 p3=64
WAIT #1: nam='db file scattered read' ela= 774 p1=9 p2=773 p3=64
WAIT #1: nam='db file scattered read' ela= 795 p1=9 p2=837 p3=64
WAIT #1: nam='db file scattered read' ela= 783 p1=9 p2=901 p3=64
WAIT #1: nam='db file scattered read' ela= 792 p1=9 p2=965 p3=64
WAIT #1: nam='db file scattered read' ela= 788 p1=9 p2=1029 p3=64
WAIT #1: nam='db file scattered read' ela= 809 p1=9 p2=1093 p3=64
WAIT #1: nam='db file scattered read' ela= 808 p1=9 p2=1157 p3=64
WAIT #1: nam='db file scattered read' ela= 832 p1=9 p2=1221 p3=64
WAIT #1: nam='db file scattered read' ela= 814 p1=9 p2=1285 p3=64
WAIT #1: nam='db file scattered read' ela= 826 p1=9 p2=1349 p3=64
WAIT #1: nam='db file scattered read' ela= 830 p1=9 p2=1413 p3=64
WAIT #1: nam='db file scattered read' ela= 815 p1=9 p2=1477 p3=64
WAIT #1: nam='db file scattered read' ela= 809 p1=9 p2=1541 p3=64
WAIT #1: nam='db file scattered read' ela= 837 p1=9 p2=1605 p3=64
WAIT #1: nam='db file scattered read' ela= 831 p1=9 p2=1669 p3=64
WAIT #1: nam='db file scattered read' ela= 33602 p1=9 p2=1733 p3=56
FETCH #1:c=0,e=153379,p=1784,cr=1787,cu=0,mis=0,r=1,dep=0,og=4,tim=25136717209884
WAIT #1: nam='SQL*Net message from client' ela= 389 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=25136717210464
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0

 

在以上测试平台中，Oracle最多每次IO能够读取64个Block，由于block_size为8k,也就是每次最多读取了512K的数据.