오라클 성능 고도화 원리와 해법 I (2012년)
Single Block vs. Multiblock I/O
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.26 0.26 64 69 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.26 0.26 64 69 0 1
- 위 Call 통계를 보면, 버퍼 캐시에서 69개 블록을 읽으면서 그 중 64개는 디스크에서 읽었다.
- 버퍼 캐시 히트율은 7.24%(= 1 - (64 / 69) X 100)다.
- 디스크에서 읽은 블록 수가 64개라고 I/O Call까지 64번 발생했음을 의미하지는 않는다.
- 64번일 수도 있고, 그보다 작을 수도 있다.
읽고자 하는 블록을 버퍼 캐시에서 찾지 못했을 때, I/O Call을 통해 데이터파일로부터 버퍼 캐시에 적재하는 방식에는 크게 두 가지가 있다.
- Single Block I/O : 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 방법. 인덱스를 통한 Table Acess일 경우 인덱스와 테이블 모두 이방식을 사용
- Multiblock I/O : Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 방법. Extent 범위 단위에서 읽는다.db_file_muliblock_read_count 파라미터에 의해 결정된다.
- 파라미터가 16이면 한 번에 최대 16개 블록을 버퍼 캐시에 적재한다.
- 만약 db_block_size가 8,192 바이트면 한 번에 최대 131,072 바이트를 읽는 셈이 된다.
- 파라미터를 128로 바꾸면 1,048,576 바이트씩 읽는다.
- 대개 OS 레벨에서 I/O 단위가 1MB 이므로 db_block_size가 8,192 일 때는 최대 설정할 수 있는 값은 128이 된다.
- 이 파라미터를 128 이상으로 설정하더라도 OS가 허용하는 I/O 단위가 1MB면 1MB씩만 읽는다.
- 인덱스는 리프블록끼리 Double Linked List 구조로 연결되어 있어, 물리적으로 한 Extent에 속한 블록들을 I/O Calll발생 시점에 같이 적재하여 올렸을 때, 그 블록들이 논리적 순서로는 한참 뒤쪽에 위치할 수 있으므로, 실제 사용되지 못한 채 버퍼상에서 밀려 날 수도 있으므로 Singl Block I/O방식이 효율적이다.
- Index Range Scan 뿐 아니라 Index Full Scan시에도 논리적인 순서에 따라 Single Block I/O방식으로 읽는다.
- Index Fast Full Scan은 Multiblock I/O 방식을 사용한다.
서버 프로세스는 Disk에서 블록을 읽어야 하는 시점마다 I/O 서브시스템에 I/O 요청을 하고 대기 상태에 빠지는데 대표적인 대기 이벤트는 다음과 같다.
- db file sequential read 대기 이벤트 : Single Block I/O방식으로 I/O를 요청할 때 발생(index scan)
- db file scattered read 대기 이벤트 : Multiblock I/O방식으로 I/O를 요청할 때 발생(full scan)
-- sequential, scattered 평균 대기 시간
SQL> select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
2 from sys.v_$system_event a, sys.v_$system_event b
3 where a.event = 'db file sequential read'
4 and b.event = 'db file scattered read';
SEQ READ SCAT READ
---------- ----------
.83 3.42
- 대량의 데이터를 Multiblock I/O 방식으로 읽을 때 Single Block I/O 보다 성능상 유리한 것은 I/O Call 발생횟수를 그만큼 줄여주기 때문이다.
SQL> drop table t purge;
테이블이 삭제되었습니다.
SQL> create table t
2 as
3 select * from all_objects;
테이블이 생성되었습니다.
SQL> alter table t add
2 constraint t_pk primary key(object_id);
테이블이 변경되었습니다.
-- # Oracle 버전 : 10g(db_file_multiblock_read_count = 16)
select /*+ index(t) */ count(*)
from t where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.01 84 90 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 84 93 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=90 pr=84 pw=0 time=17665 us)
42672 INDEX RANGE SCAN T_PK (cr=90 pr=84 pw=0 time=42702 us)(object id 109394)
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 84 0.00 0.00
SQL*Net message from client 2 12.00 12.00
-- # Oracle 버전 : 11g(db_file_multiblock_read_count = 128)
select /*+ index(t) */ count(*)
from t where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.03 142 147 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.04 142 150 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=147 pr=142 pw=0 time=0 us)
70417 INDEX RANGE SCAN T_PK (cr=147 pr=142 pw=0 time=19552 us cost=160 size=986076 card=75852)(object id 84135)
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 scattered read 19 0.01 0.02
db file sequential read 10 0.00 0.00
SQL*Net message from client 2 0.00 0.00
-- # Oracle 버전 : 11g(db_file_multiblock_read_count = 128)
-------------------------------------------------p1------p2-----------p3----------------------------------
WAIT #17: nam='db file scattered read' ela= 57 file#=5 block#=316425 blocks=7 obj#=84135 tim=1342509778986042
WAIT #17: nam='db file scattered read' ela= 53 file#=5 block#=316433 blocks=7 obj#=84135 tim=1342509778986786
WAIT #17: nam='db file sequential read' ela= 13 file#=5 block#=316440 blocks=1 obj#=84135 tim=1342509778987499
WAIT #17: nam='db file scattered read' ela= 83 file#=5 block#=316441 blocks=7 obj#=84135 tim=1342509778987725
WAIT #17: nam='db file scattered read' ela= 55 file#=5 block#=316449 blocks=7 obj#=84135 tim=1342509778988423
WAIT #17: nam='db file sequential read' ela= 13 file#=5 block#=356608 blocks=1 obj#=84135 tim=1342509778989066
WAIT #17: nam='db file scattered read' ela= 53 file#=5 block#=356609 blocks=7 obj#=84135 tim=1342509778989246
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=356617 blocks=7 obj#=84135 tim=1342509778989937
WAIT #17: nam='db file sequential read' ela= 12 file#=5 block#=356624 blocks=1 obj#=84135 tim=1342509778990587
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=356625 blocks=7 obj#=84135 tim=1342509778990776
WAIT #17: nam='db file scattered read' ela= 53 file#=5 block#=356633 blocks=7 obj#=84135 tim=1342509778991467
WAIT #17: nam='db file sequential read' ela= 13 file#=5 block#=356640 blocks=1 obj#=84135 tim=1342509778992107
WAIT #17: nam='db file scattered read' ela= 52 file#=5 block#=356641 blocks=7 obj#=84135 tim=1342509778992291
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=356649 blocks=7 obj#=84135 tim=1342509778992984
WAIT #17: nam='db file sequential read' ela= 12 file#=5 block#=356656 blocks=1 obj#=84135 tim=1342509778993612
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=356657 blocks=7 obj#=84135 tim=1342509778993800
WAIT #17: nam='db file scattered read' ela= 53 file#=5 block#=356665 blocks=7 obj#=84135 tim=1342509778994489
WAIT #17: nam='db file sequential read' ela= 12 file#=5 block#=356672 blocks=1 obj#=84135 tim=1342509778995128
WAIT #17: nam='db file scattered read' ela= 52 file#=5 block#=356673 blocks=7 obj#=84135 tim=1342509778995307
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=356681 blocks=7 obj#=84135 tim=1342509778995996
WAIT #17: nam='db file sequential read' ela= 12 file#=5 block#=356688 blocks=1 obj#=84135 tim=1342509778996629
WAIT #17: nam='db file scattered read' ela= 55 file#=5 block#=356689 blocks=7 obj#=84135 tim=1342509778996819
WAIT #17: nam='db file scattered read' ela= 71 file#=5 block#=273538 blocks=6 obj#=84135 tim=1342509778997726
WAIT #17: nam='db file sequential read' ela= 13 file#=5 block#=273544 blocks=1 obj#=84135 tim=1342509778998293
WAIT #17: nam='db file scattered read' ela= 54 file#=5 block#=273545 blocks=7 obj#=84135 tim=1342509778998473
WAIT #17: nam='db file sequential read' ela= 13 file#=5 block#=273552 blocks=1 obj#=84135 tim=1342509778999111
WAIT #17: nam='db file scattered read' ela= 52 file#=5 block#=273553 blocks=7 obj#=84135 tim=1342509778999288
WAIT #17: nam='db file sequential read' ela= 12 file#=5 block#=273560 blocks=1 obj#=84135 tim=1342509778999923
WAIT #17: nam='db file scattered read' ela= 19127 file#=5 block#=273561 blocks=7 obj#=84135 tim=1342509779019176
----------------------------------------------------------------------------------------------------------
FETCH #17:c=15997,e=34646,p=142,cr=147,cu=0,mis=0,r=1,dep=0,og=1,plh=4152626091,tim=1342509779020141
- 위 트레이스(v.10g) 결과를 보면, 논리적으로 93개 블록을 읽는 동안 84개의 디스크 블록을 읽었다.
- 이벤트 발생 현황을 보면 db file sequential read 대기 이벤트가 84번 발생했다.
- 즉, 84개 인덱스 블록을 Disk에서 읽으면서 84번의 I/O Call이 발생한 것이다.
-- # Oracle 버전 : 10g
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 16
-- # Oracle 버전 : 11g
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
- db_block_size는 8,192이고, Multiblock I/O(db_file_multiblock_read_count) 단위는 16(v.10g)이다.
- 앞에서와 같은 양의 인덱스 블록을 Multiblock I/O 방식으로 읽도록 하기 위해 인덱스를 index fast full scan 방식으로 읽도록 유도해 보자.
- index_ffs 힌트를 사용하면 된다.
- Multiblock I/O 단위가 16이므로 데이터파일에서 똑같이 84개 블록을 읽었을 때 5(=84/16)번의 I/O Call이 발생할 것으로 예상된다.
- 디스크 I/O가 발생하도록 하려면 먼저 테이블과 인덱스를 Drop 했다가 다시 생성해야 한다.
select /*+ index_ffs(t) */ count(*)
from t where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.01 84 96 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 84 99 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=96 pr=84 pw=0 time=13522 us)
42672 INDEX FAST FULL SCAN T_PK (cr=96 pr=84 pw=0 time=42786 us)(object id 109396)
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 scattered read 12 0.00 0.00
SQL*Net message from client 2 13.12 13.12
- 똑같이 84개 블록을 디스크에서 읽었는데, I/O Call이 12번에 그쳤다.
- Single Block I/O 할 때보다는 크게 줄었지만 예상했던 5보다는 두 배 많은 수치다.
- 84/12 = 7 이므로 평균 7~8개씩 읽은 셈이다.
- OS에서 I/O 단위가 65,536(=8,192X8) 바이트인 것일까? 트레이스 파일을 열어 확인해 보자.
-- # Oracle 버전 : 10g(db_file_multiblock_read_count = 16)
EXEC #19:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5312766948262
WAIT #19: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=109394 tim=5312766948334
-------------------------------------------------p1------p2-----------p3----------------------------------
WAIT #19: nam='db file scattered read' ela= 74 file#=4 block#=1090 blocks=7 obj#=109396 tim=5312766949324
WAIT #19: nam='db file scattered read' ela= 99 file#=4 block#=1098 blocks=7 obj#=109396 tim=5312766950465
WAIT #19: nam='db file scattered read' ela= 56 file#=4 block#=1105 blocks=8 obj#=109396 tim=5312766951503
WAIT #19: nam='db file scattered read' ela= 80 file#=4 block#=1114 blocks=7 obj#=109396 tim=5312766952696
WAIT #19: nam='db file scattered read' ela= 70 file#=4 block#=1121 blocks=8 obj#=109396 tim=5312766953776
WAIT #19: nam='db file scattered read' ela= 89 file#=4 block#=1130 blocks=7 obj#=109396 tim=5312766954983
WAIT #19: nam='db file scattered read' ela= 66 file#=4 block#=1137 blocks=8 obj#=109396 tim=5312766956031
WAIT #19: nam='db file scattered read' ela= 103 file#=4 block#=1146 blocks=7 obj#=109396 tim=5312766957243
WAIT #19: nam='db file scattered read' ela= 71 file#=4 block#=1153 blocks=8 obj#=109396 tim=5312766958293
WAIT #19: nam='db file scattered read' ela= 74 file#=4 block#=1674 blocks=7 obj#=109396 tim=5312766959489
WAIT #19: nam='db file scattered read' ela= 78 file#=4 block#=1681 blocks=8 obj#=109396 tim=5312766960565
WAIT #19: nam='db file scattered read' ela= 28 file#=4 block#=1690 blocks=2 obj#=109396 tim=5312766961684
----------------------------------------------------------------------------------------------------------
FETCH #19:c=20000,e=13521,p=84,cr=96,cu=0,mis=0,r=1,dep=0,og=1,tim=5312766961918
WAIT #19: nam='SQL*Net message from client' ela= 585 driver id=1413697536 #bytes=1 p3=0 obj#=109396 tim=5312766962645
FETCH #19:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=5312766962722
WAIT #19: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=109396 tim=5312766962782
*** 2012-07-10 10:52:27.291
WAIT #19: nam='SQL*Net message from client' ela= 13126184 driver id=1413697536 #bytes=1 p3=0 obj#=109396 tim=5312780089022
STAT #19 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=96 pr=84 pw=0 time=13522 us)'
STAT #19 id=2 cnt=42672 pid=1 pos=1 obj=109396 op='INDEX FAST FULL SCAN T_PK (cr=96 pr=84 pw=0 time=42786 us)'
=====================
- db file scattered read 대기 이벤트가 실제 12번 발생한 것을 볼 수 있고, 세 번째 파라미터(p3)를 보면 마지막 것만 빼고 매번 7개 또는 8개씩을 읽었다.
- 테이블스페이스에 할당된 익스텐트 크기를 확인해 보면 그 이유를 쉽게 찾을 수 있다.
SQL> select extent_id, block_id, bytes, blocks
2 from dba_extents
3 where owner = USER
4 and segment_name = 'T_PK'
5 and tablespace_name = 'USERS'
6 order by extent_id ;
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 1081 65536 8
1 1089 65536 8
2 1097 65536 8
3 1105 65536 8
4 1113 65536 8
5 1121 65536 8
6 1129 65536 8
7 1137 65536 8
8 1145 65536 8
9 1153 65536 8
10 1673 65536 8
11 1681 65536 8
12 1689 65536 8
13 개의 행이 선택되었습니다.
- 모든 익스텐트가 8개 블록으로 구성돼 있는 것이 원인이었다.
- Multiblock I/O 방식으로 읽더라도 익스텐트 범위를 넘지는 못한다고 앞에서 설명했다.
- 예를 들어, 모든 익스텐트에 20개 블록이 있고 db_file_multiblock_read_count가 8이면, 익스텐트마다 8, 8, 4개씩 세 번에 걸쳐 읽는다.
익스텐트 크기 때문에 예상보다 조금 더 많은 I/O Call이 발생하긴 했지만, Single Block I/O 때보다 훨씬 적은 양의 I/O Call이 발생하는 것을 알 수 있었다.
- 10g 부터는 테이블 액세스 없이 인덱스만 처리 할때는 Index Range Scan 또는 Index Full Scan 일 때도 Multiblock I/O 방식으로 읽는 경우가 있다.
- Singl Block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU쪽 end로 연결되므로 한번 적재되면 버퍼 캐시에 비교적 오래 머문다.
- Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU쪽 end로 연결되므로 적재되고 얼마 지나지 않아 버퍼캐시에서 밀려난다.
참조 문서
- 이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3119
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.