oracle IO的影响因素有哪些？

数据库的IO能力，在很多时候会影响到查询的性能，尤其是FTS 的性能，所以对于oracle来说，这会影响到执行计划的选择。数据库中统计的 physical reads和 IO 不是一个概念，数据库中物理读统计的是 blocks ，而操作系统中是 io requests ,一次 request 可能是 128k/256k/1024k 等等数据，当然也可能是几十k 或者更少的，取决于数据库的要求。 io request 次数，简单点说是 table blocks / DB_FILE_MULTIBLOCK_READ_COUNT。

数据库的IO的次数和性能，实际上相关因素包括：

1： 不同 os + 硬件 ，每次IO 有一个极限值，比如hp unix 极限值 是 256k ,sun 的可能高达 1---8m ，若 DB_FILE_MULTIBLOCK_READ_COUNT* block_size > 这个极限值自然就被拆分为多于一个的 io request

2: 一次 io request 不能跨越 extent 边界，所以 extent 大小也影响 IO request

3:对于文件系统来说，连续的 block_id 并不意味着连续的 os block block，这会影响到读磁盘的效率。

4:对于 disk array 等设备类说，对于做了 raid 的设备来说，对于很多存储设备来说 ， IO 在从 os 抵达这里的时候又重新做了优化重整的 ，还存在cache的，这样代价就不好评估了

5： 对于 AIO 来说，os 的读写请求被重整优化过，更复杂一些

下面做一个db_file_multiblock_read_count和extents边界影响IO的测试

windows下测试

SQL> drop table t;

表已丢弃。

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

表已创建。

SQL> insert into t select * from t;

已创建6331行。

SQL> commit;

提交完成。

SQL> select file_id,extent_id,block_id,blocks from dba_extents where owner='TEST' and segment_name='
T';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
4 0 9 32
4 1 41 32
4 2 73 32
4 3 105 32
4 4 137 32

SQL> alter session set db_file_multiblock_read_count=16;

会话已更改。

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

会话已更改。

SQL>
SQL> select count(*) from t;

COUNT(*)
----------
12662

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

trace

PARSING IN CURSOR #1 len=22 dep=0 uid=23 oct=3 lid=23 tim=136424432868 hv=2199322426 ad='7b3e0898'
select count(*) from t
END OF STMT
PARSE #1:c=0,e=21795,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=136424432851
BINDS #1:
EXEC #1:c=0,e=4015,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=136424456603
WAIT #1: nam='SQL*Net message to client' ela= 11 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 33200 p1=4 p2=10 p3=16
WAIT #1: nam='db file scattered read' ela= 21724 p1=4 p2=26 p3=15
WAIT #1: nam='db file scattered read' ela= 3197 p1=4 p2=41 p3=16
WAIT #1: nam='db file scattered read' ela= 11001 p1=4 p2=57 p3=16
WAIT #1: nam='db file scattered read' ela= 2687 p1=4 p2=73 p3=15
FETCH #1:c=40057,e=130616,p=78,cr=160,cu=0,mis=0,r=1,dep=0,og=4,tim=136424598085



SQL> drop table t;

表已丢弃。

SQL> create table t tablespace users as select * from dba_objects;

表已创建。

SQL> insert into t select * from t;

已创建6331行。

SQL> /

已创建12662行。

SQL> commit;

提交完成。

SQL> select file_id,extent_id,block_id,blocks from dba_extents where owner='TEST' and segment_name='
T';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
3 0 33 8
3 1 73 8
3 2 81 8
3 3 89 8
3 4 169 8
3 5 177 8
3 6 185 8
3 7 193 8
3 8 201 8
3 9 209 8
3 10 217 8

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
3 11 225 8
3 12 233 8
3 13 241 8
3 14 249 8
3 15 257 8
3 16 12169 128
3 17 12297 128

已选择18行。

SQL> alter session set db_file_multiblock_read_count=20;

会话已更改。

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

会话已更改。

SQL>
SQL> select count(*) from t;

COUNT(*)
----------
25324

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

C:/>


PARSING IN CURSOR #1 len=22 dep=0 uid=23 oct=3 lid=23 tim=136872504466 hv=2199322426 ad='7b3e0898'
select count(*) from t
END OF STMT
PARSE #1:c=0,e=1159,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=136872504449
BINDS #1:
EXEC #1:c=0,e=4618,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=136872527767
WAIT #1: nam='SQL*Net message to client' ela= 10 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 27197 p1=3 p2=36 p3=5
WAIT #1: nam='db file scattered read' ela= 23246 p1=3 p2=73 p3=8
WAIT #1: nam='db file scattered read' ela= 1346 p1=3 p2=82 p3=7
WAIT #1: nam='db file scattered read' ela= 3372 p1=3 p2=89 p3=8
WAIT #1: nam='db file scattered read' ela= 17965 p1=3 p2=170 p3=7
WAIT #1: nam='db file scattered read' ela= 2460 p1=3 p2=177 p3=8
WAIT #1: nam='db file scattered read' ela= 1403 p1=3 p2=186 p3=7
WAIT #1: nam='db file scattered read' ela= 1759 p1=3 p2=193 p3=8
WAIT #1: nam='db file scattered read' ela= 1444 p1=3 p2=202 p3=7
WAIT #1: nam='db file scattered read' ela= 1454 p1=3 p2=209 p3=8
WAIT #1: nam='db file scattered read' ela= 1053 p1=3 p2=218 p3=5


由上面p1 file_id p2 block_id p3 blocks 可以看出IO次数和相关blocks

事实上一次IO除了和这个参数有关，还和extent有关，一次IO是不能越过extent的

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> insert into t select * from t;

已创建25324行。

SQL> commit;

提交完成。

SQL> select file_id,extent_id,block_id,blocks from dba_extents where owner='TEST' and segment_name
T';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
3 0 33 8
3 1 73 8
3 2 81 8
3 3 89 8
3 4 169 8
3 5 177 8
3 6 185 8
3 7 193 8
3 8 201 8
3 9 209 8
3 10 217 8

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
3 11 225 8
3 12 233 8
3 13 241 8
3 14 249 8
3 15 257 8
3 16 12169 128
3 17 12297 128
3 18 12425 128
3 19 12553 128

已选择20行。

SQL> alter session set db_file_multiblock_read_count=20;

会话已更改。

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

会话已更改。

SQL> select count(*) from t;

COUNT(*)
----------
50648

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

C:/>

=====================
PARSING IN CURSOR #1 len=22 dep=0 uid=23 oct=3 lid=23 tim=137237788846 hv=2199322426 ad='7b3e0898'
select count(*) from t
END OF STMT
PARSE #1:c=0,e=230,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=137237788829
BINDS #1:
EXEC #1:c=0,e=3961,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=137237812525
WAIT #1: nam='SQL*Net message to client' ela= 10 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 29105 p1=3 p2=36 p3=5
WAIT #1: nam='db file scattered read' ela= 23441 p1=3 p2=73 p3=8
WAIT #1: nam='db file scattered read' ela= 1372 p1=3 p2=82 p3=7
WAIT #1: nam='db file scattered read' ela= 1549 p1=3 p2=89 p3=8
WAIT #1: nam='db file scattered read' ela= 19690 p1=3 p2=170 p3=7
WAIT #1: nam='db file scattered read' ela= 2588 p1=3 p2=177 p3=8
WAIT #1: nam='db file scattered read' ela= 1428 p1=3 p2=186 p3=7
WAIT #1: nam='db file scattered read' ela= 1479 p1=3 p2=193 p3=8
WAIT #1: nam='db file scattered read' ela= 1357 p1=3 p2=202 p3=7
WAIT #1: nam='db file scattered read' ela= 1487 p1=3 p2=209 p3=8
WAIT #1: nam='db file scattered read' ela= 1021 p1=3 p2=218 p3=5
WAIT #1: nam='db file sequential read' ela= 362 p1=3 p2=224 p3=1
WAIT #1: nam='db file scattered read' ela= 1658 p1=3 p2=225 p3=8
WAIT #1: nam='db file scattered read' ela= 2126 p1=3 p2=234 p3=7
WAIT #1: nam='db file scattered read' ela= 1508 p1=3 p2=241 p3=8
WAIT #1: nam='db file scattered read' ela= 1450 p1=3 p2=250 p3=7
WAIT #1: nam='db file scattered read' ela= 1490 p1=3 p2=257 p3=8
WAIT #1: nam='db file scattered read' ela= 42288 p1=3 p2=12171 p3=20
WAIT #1: nam='db file scattered read' ela= 3893 p1=3 p2=12191 p3=20
WAIT #1: nam='db file scattered read' ela= 7199 p1=3 p2=12211 p3=20
WAIT #1: nam='db file scattered read' ela= 1477 p1=3 p2=12231 p3=8
FETCH #1:c=130188,e=279571,p=185,cr=722,cu=1,mis=0,r=1,dep=0,og=4,tim=137238099800
WAIT #1: nam='SQL*Net message from client' ela= 620 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=137238110581