• Single Block:
• 한번의 I/O Call 에 하나의 데이터 블록만 읽어 메모리에 적재
• Multiblock I/O
• I/O Call 이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재
• db_file_multiblock_read_count 파라미터에 의해 결정
• db_block_size * count 로 한번에 읽어 들이는 값을 계산 

• 인덱스 
• Index Range Scan/ Index Full Scan : 블록간 논리적 순서와 물리적 저장 순서와 다르므로 한블록씩 읽어야함
• Index Fast Full Scan : 인덱스의 논리적 순서를 무시하고 물리적 순서에 따라 읽음 (Multiblock I/O 사용)

• 대기이벤트
• db file sequential read : Single Block I/O 방식으로 I/O를 요청할때 발생
• db file scattered read : Multiblock I/O 방식으로 I/O를 요청할때 발생

SINGLE BLOCK I/O

SQL> create table t as select * from all_objects;

Table created.

SQL> alter table t add constraint t_pk primary key (object_id);

Table altered.

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

Session altered.

SQL> select /*+ index(t) */ count(*) from t where object_id>0;

 COUNT(*)
----------
    12212

SQL>

trace 파일 확인
********************************************************************************

select /*+ index(t) */ count(*)
from
t where object_id>0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.04         20         26          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06         20         29          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=26 pr=20 pw=0 time=40610 us)
 12212   INDEX RANGE SCAN T_PK (cr=26 pr=20 pw=0 time=61140 us)(object id 17847)


Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                       2        0.00          0.00
 db file sequential read                        20        0.02          0.02
 SQL*Net message from client                     2        0.00          0.00



********************************************************************************


논리적으로 29개의 블록을 읽을동안 20번의 db file sequential read 발생

Multiblock I/O

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> show parameter db_file_multiblock_read_count

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


SQL> alter session set db_file_multiblock_read_count=16; --- 16으로 변경

Session altered.

SQL> show parameter db_file_multi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
SQL>


SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> alter table t add constraint t_pk primary key (object_id);

Table altered.

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

Session altered.

SQL>
SQL> select /*+ index_ffs(t) */ count(*) from t where object_id>0;

 COUNT(*)
----------
    12249

select /*+ index_ffs(t) */ count(*)

from

 t where object_id>0

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.10          0          3          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.01       0.08         20         30          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.02       0.19         20         33          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=30 pr=20 pw=0 time=84725 us)

  12249   INDEX FAST FULL SCAN T_PK (cr=30 pr=20 pw=0 time=54898 us)(object id 17927)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                         1        0.00          0.00

  db file scattered read                          3        0.04          0.05

  SQL*Net message from client                     2        0.03          0.04

********************************************************************************

20개의 블록을 대상으로 16 개씩 읽으므로 (20/16) 2 번정도의  I/O CALL 발생예상
하지만 실제는 3번이 발생하였으며 트레이스파일을 보면 

db file scattered read 대기 이벤트에 8블럭씩 읽은것을 확인할수 있다.
이는 익스텐트가 8블록씩 구성되어있어서 이며 multiblock i/o는 익스텐트의 크기를 넘기지 못함을 확인할수 잇다.

=====================

===================== 트레이스 파일

PARSING IN CURSOR #36 len=60 dep=0 uid=0 oct=3 lid=0 tim=1248882692644929 hv=2183680629 ad='285a08a4'

select /*+ index_ffs(t) */ count(*) from t where object_id>0

END OF STMT

PARSE #36:c=92006,e=245153,p=5,cr=95,cu=0,mis=1,r=0,dep=0,og=1,tim=1248882692644902

BINDS #36:

EXEC #36:c=0,e=187,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1248882692645359

WAIT #36: nam='SQL*Net message to client' ela= 13 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1248882692645504

WAIT #36: nam='db file sequential read' ela= 110 file#=1 block#=61760 blocks=1 obj#=17927 tim=1248882692665797

WAIT #36: nam='db file scattered read' ela= 44848 file#=1 block#=61761 blocks=8 obj#=17927 tim=1248882692711265

WAIT #36: nam='db file scattered read' ela= 905 file#=1 block#=61769 blocks=8 obj#=17927 tim=1248882692715785

WAIT #36: nam='db file scattered read' ela= 10399 file#=1 block#=61777 blocks=3 obj#=17927 tim=1248882692729342

FETCH #36:c=12001,e=84708,p=20,cr=30,cu=0,mis=0,r=1,dep=0,og=1,tim=1248882692730319

WAIT #36: nam='SQL*Net message from client' ela= 31445 driver id=1650815232 #bytes=1 p3=0 obj#=17927 tim=1248882692762069

FETCH #36:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1248882692762370

WAIT #36: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=17927 tim=1248882692762491

WAIT #36: nam='SQL*Net message from client' ela= 16162 driver id=1650815232 #bytes=1 p3=0 obj#=17927 tim=1248882692778735

STAT #36 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=30 pr=20 pw=0 time=84725 us)'

STAT #36 id=2 cnt=12249 pid=1 pos=1 obj=17927 op='INDEX FAST FULL SCAN T_PK (cr=30 pr=20 pw=0 time=54898 us)'

WAIT #0: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=17927 tim=1248882692779306

*** 2010-07-11 22:45:04.553

WAIT #0: nam='SQL*Net message from client' ela= 26511079 driver id=1650815232 #bytes=1 p3=0 obj#=17927 tim=1248882719290462

=====================

 1  select extent_id,block_id,bytes,blocks from dba_extents where owner=USER and segment_name='T_PK'
 2* order by extent_id
SQL> /

EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
        0      61753      65536          8
        1      61761      65536          8
        2      61769      65536          8
        3      61777      65536          8

• Single block I/O 
• 본 방식으로 읽은 블록은 LRU상 MRU쪽으로 연결되어 한번적제되면 비교적 오래 머뭄
• Multiblock I/O 
• 본방식으로 읽은 블록은 LRU리스트에서 LRU쪽에 연결되어 버퍼 캐시 메모리에서 오래지 않아 밀려남