硬件：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的数据.
阅读(1133) | 评论(0) | 转发(0) |
