h1.03 Single Block vs. Multiblock I/O
single block I/O 테스트
--테이블 생성
CREATE TABLE T
AS SELECT * FROM ALL_OBJECTS;
--primary key 추가
ALTER TABLE T ADD
CONSTRAINT T_PK PRIMARY KEY(OBJECT_ID);
--index scan으로 single block I/O 유도
SELECT /*+INDEX(T) */ COUNT(1)
FROM T WHERE OBJECT_ID > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 195 201 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.03 195 204 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=201 pr=195 pw=0 time=27068 us)
95943 INDEX RANGE SCAN T_PK (cr=201 pr=195 pw=0 time=35318 us cost=204 size=1289769 card=99213)(object id 366563)
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 195 0.00 0.00
SQL*Net message from client 2 8.16 8.16
-> 195번의 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 128
-> 한번에 128개를 읽을 거니까 멀티블록 I/O로 195개를 읽으려면 2번의 I/O만 있으면 될 것 같다.
--index fast full scan으로 Multiblock I/O 유도
SELECT /*+INDEX_FFS(T) */ COUNT(1)
FROM T WHERE OBJECT_ID > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 195 207 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 195 210 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=207 pr=195 pw=0 time=20183 us)
95943 INDEX FAST FULL SCAN T_PK (cr=207 pr=195 pw=0 time=28435 us cost=57 size=1289769 card=99213)(object id 366565)
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 16 0.00 0.00
SQL*Net message from client 2 6.28 6.28
-> 이상하다. multiblock_read_count가 128이라 195를 읽으려면 2번의 I/O만 있었으면 될 것 같은데 I/O가 왜 16번 발생한 걸까?
-> trace file을 확인해보자.
EXEC #11529215044980315600:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=73679483,tim=21770590858180
WAIT #11529215044980315600: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=366563 tim=21770590858199
WAIT #11529215044980315600: nam='db file sequential read' ela= 13 file#=34 block#=460287 blocks=1 obj#=366565 tim=21770590858714
WAIT #11529215044980315600: nam='db file scattered read' ela= 86 file#=34 block#=461568 blocks=8 obj#=366565 tim=21770590858971
WAIT #11529215044980315600: nam='db file scattered read' ela= 59 file#=34 block#=461576 blocks=8 obj#=366565 tim=21770590859844
WAIT #11529215044980315600: nam='db file scattered read' ela= 54 file#=34 block#=461584 blocks=8 obj#=366565 tim=21770590860690
WAIT #11529215044980315600: nam='db file scattered read' ela= 52 file#=34 block#=461592 blocks=8 obj#=366565 tim=21770590861496
WAIT #11529215044980315600: nam='db file scattered read' ela= 55 file#=34 block#=461600 blocks=8 obj#=366565 tim=21770590862310
WAIT #11529215044980315600: nam='db file scattered read' ela= 54 file#=34 block#=461608 blocks=8 obj#=366565 tim=21770590863123
WAIT #11529215044980315600: nam='db file scattered read' ela= 55 file#=34 block#=461616 blocks=8 obj#=366565 tim=21770590863939
WAIT #11529215044980315600: nam='db file scattered read' ela= 55 file#=34 block#=461624 blocks=8 obj#=366565 tim=21770590864756
WAIT #11529215044980315600: nam='db file scattered read' ela= 53 file#=34 block#=461632 blocks=8 obj#=366565 tim=21770590865563
WAIT #11529215044980315600: nam='db file scattered read' ela= 54 file#=34 block#=461640 blocks=8 obj#=366565 tim=21770590866378
WAIT #11529215044980315600: nam='db file scattered read' ela= 51 file#=34 block#=461648 blocks=8 obj#=366565 tim=21770590867176
WAIT #11529215044980315600: nam='db file scattered read' ela= 53 file#=34 block#=461656 blocks=8 obj#=366565 tim=21770590868100
WAIT #11529215044980315600: nam='db file scattered read' ela= 52 file#=34 block#=461664 blocks=8 obj#=366565 tim=21770590868912
WAIT #11529215044980315600: nam='db file scattered read' ela= 51 file#=34 block#=461672 blocks=8 obj#=366565 tim=21770590869719
WAIT #11529215044980315600: nam='db file scattered read' ela= 52 file#=34 block#=461680 blocks=8 obj#=366565 tim=21770590870515
WAIT #11529215044980315600: nam='db file scattered read' ela= 351 file#=34 block#=461696 blocks=74 obj#=366565 tim=21770590872034
FETCH #11529215044980315600:c=20000,e=20188,p=195,cr=207,cu=0,mis=0,r=1,dep=0,og=1,plh=73679483,tim=21770590878409
STAT #11529215044980315600 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=207 pr=195 pw=0 time=20183 us)'
STAT #11529215044980315600 id=2 cnt=95943 pid=1 pos=1 obj=366565 op='INDEX FAST FULL SCAN T_PK (cr=207 pr=195 pw=0 time=28435 us cost=57 size=1289769 card=99213)'
WAIT #11529215044980315600: nam='SQL*Net message from client' ela= 352 driver id=1413697536 #bytes=1 p3=0 obj#=366565 tim=21770590878842
FETCH #11529215044980315600:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=73679483,tim=21770590878864
WAIT #11529215044980315600: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=366565 tim=21770590878880
-> 주로 8블록씩 읽었다. 왜?
SQL> SELECT EXTENT_ID, BLOCK_ID, BYTES, BLOCKS
2 FROM DBA_EXTENTS
3 WHERE OWNER = 'SYS'
4 AND SEGMENT_NAME = 'T_PK'
5 AND TABLESPACE_NAME = 'SYSTEM';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 460280 65536 8
1 461568 65536 8
2 461576 65536 8
3 461584 65536 8
4 461592 65536 8
5 461600 65536 8
6 461608 65536 8
7 461616 65536 8
8 461624 65536 8
9 461632 65536 8
10 461640 65536 8
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
11 461648 65536 8
12 461656 65536 8
13 461664 65536 8
14 461672 65536 8
15 461680 65536 8
16 461696 1048576 128
-> 익스텐트 크기가 8블록이다.
-> '인접블록이란 멀티블록 I/O에서 한 익스텐트 내에 속한 블록, 따라서 멀티블록 I/O 사이즈는 익스텐트 크기를 초과하지 못 한다.' 라는 말이 증명되었다.
sequentialread : 1
scattered read : 8*15 = 120
scattered read : 74
1 + 120 + 74 = 195
총 195블록 read