Direct path read
|
목차
|
Basic Info
direct path read 이벤트대기는 Parallel Query 수행시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O 에 의해 발생한다. direct path I/O 는 SGA 내의 버퍼캐쉬를 거치지 않고 세션의 PGA 로 직접 블록을 읽어 들이는 것으로 direct read 는 I/O 방식(synchronous I/O, asynchronous I/O)에 상관없이 수행될 수 있으나, 하드웨어 플랫폼과 DISK_ASYNCH_IO 파라미터에 영향을 받는다. Direct read I/O 는 일반적으로 디스크에 위치한 임시(temporary) 세그먼트를 액세스 하는 동안 사용된다. 이러한 작업은 정렬(sort), 병렬조회(parallel query) 및 해쉬조인(hash join)시에 발생한다.
슬레이브 세션이 direct path read 를 수행하는 동안 코디네이터 세션(Coordinator Session)은 슬레이브 세션으로부터 응답이 오기를 기다리며 PX Deq: Excute Reply 이벤트를 대기하는 것으로 관찰된다. Parallel Query 수행시 발생하는 direct path read 대기는 필연적인 것이다. 만일 direct path read 이벤트의 대기시간이 지나치게 높게 나온다면 다음과 같은 관점에서 튜닝포인트를 찾아보아야 한다.
이 이벤트의 대기횟수와 대기시간은 오해의 소지가 있을 수 있다. 만일 비동기식 I/O가 사용되지 않았다면, 세션은 I/O 가 완료될
때까지 대기한다. 하지만, I/O 요청이 시작된 시점부터 대기시간을 계산하지 않고, I/O 요청이 완료된 후 데이터를 액세스 할 때 direct
path read 대기이벤트를 기다리게 된다. 따라서 대기시간은 상당히 짧게 나타난다.
만일 비동기식 I/O 가 사용가능하고 현재 사용 중이라면, 세션은 다수의 direct read 요청을 한 후 , PGA 내부에 캐쉬 된 블록들에 대한 처리를 진행한다. 세션이 PGA내부에 캐쉬 된 블록들이 없어서 처리를 진행하지 못하는 시점에 direct path read 대기이벤트가 발생된다. 따라서, 읽기 요청횟수는 대기횟수와 동일하지 않다. 이러한 불일치 때문에, V$SYSTEM_EVENT 와 V$SESSION_EVENT 뷰에서 보여지는 direct path read 대기이벤트의 수치는 신뢰할 수 없다.
LOB 세그먼트를 읽을 때 발생되는 direct path read 대기는, 오라클 8.1.7부터는 direct path read(lob) 대기이벤트로 별도로 구분된다. 보통 direct path read 대기이벤트는 임시(temporary) 또는 일반 테이블스페이스로부터 direct read 오퍼레이션을 수행하는 SQL문에 의해 발생된다. ORDER BY, GROUP BY, UNION, DISTINCT, ROLLUP과 같이 정렬이 필요한 함수를 수행하는 SQL문은, PGA 내부에서 허용할 수 있는 것 보다 더 큰 데이터를 정렬해야 할 때 소트 런(sort run)들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스의 소트 런들은 최종 결과를 만들기 위해 순차적으로 PGA로 읽혀지고 머지 된다. 소트 런들을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다. Hint또는 옵티마이저의 판단에 의해 MERGE 조인을 수행하는 SQL문 또한 정렬작업이 필요하다.
힌트(hint)또는 옵티마이저의 판단에 의해HASH 조인을 수행하는 SQL문은 PGA 내부에서 허용할 수 없는 크기의 해쉬 파티션들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스에 기록된 해쉬 파티션은 SQL문의 조건에 맞는 레코드를 찾기 위해 다시 PGA로 읽어 들여진다. 해쉬 파티션을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다.
V$SESSION_EVENT 뷰의 TOTAL_WAITS 또는 TIME_WAITED 값을 이용하여 direct path read 대기이벤트를 평가하지 않는 것이 좋다. 대신에 아래의 쿼리를 사용하여 대량의 direct read 를 수행하고 있는 세션을 찾아낼 수 있다. physical reads direct 는 parent세션에 의해 시작된 direct reads와 parent세션이 관리하는 슬레이브 프로세스에 의해 발생된 모든 direct reads 의 합으로 구성된다.
select a.name, b.sid, b.value,
round((sysdate - c.logon_time) * 24) hours_connected
from v$statname a, v$sesstat b, v$session c
where b.sid = c.sid
and a.statistic# = b.statistic#
and b.value > 0
and a.name = 'physical reads direct'
order by b.value;
NAME SID VALUE HOURS_CONNECTED
------------------------- ---- ---------- ------------------------
physical reads direct 2 41 980
physical reads direct 4 41 980
physical reads direct 5 445186 980
대량의 direct reads 를 발생시키는 세션을 찾는 것 이외에, 세션이 데이터를 읽어오는 위치 (임시 테이블스페이스, 데이터파일 등), 대기를 발생시키는 SQL문을 파악하여야 한다. 다음의 쿼리는 이것에 대한 답을 줄 수 있다. 임시 테이블스페이스에서 데이터를 읽는 세션은 소트 또는 해쉬 세그먼트를 읽고 있을 것이다. 데이터파일로부터 데이터를 읽고 있는 세션은 병렬 쿼리 슬레이브(parallel query slave)이다.
select a.event,
a.sid,
c.sql_hash_value hash_value,
decode(d.ktssosegt,
1,'SORT', 2,'HASH', 3,'DATA',
4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',
null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.sql_hash_value hash_value,
decode(e.ktssosegt,
1,'SORT', 2,'HASH', 3,'DATA',
4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',
null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_temp_files b, v$parameter c,
v$session d, x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1,2;
EVENT SID HASH_VALUE SEGMENT TABLESPACE_N FILE_NAME
------------------ ---- ---------------- ----------- ------------------ -----------------
direct path read 8 511952958 SORT TEMP_BATCH temp_batch_01.dbf
direct path read 9 3138787393 ORDERS orders_01.dbf
direct path read 11 3138787393 ORDERS orders_01.dbf
direct path read 12 3138787393 ORDERS orders_01.dbf
direct path read 14 3138787393 ORDERS orders_01.dbf
임시 테이블스페이스로부터 소트 세그먼트를 읽고 있는 세션은, SORT_AREA_SIZE (또는 오라클 9i에서 PGA_AGGREGATE_TARGET 을 사용하는 경우 work area size )가 메모리 소트를 수행할 만큼 충분치 않다는 것을 의미한다. 하지만 이것은 문제가 되지 않는다. 모든 소트가 메모리에서만 수행되는 것은 현실적으로 불가능하기 때문이다. 하지만, 많은 멀티패스( multi pass ) 소트는 가능한 방지해야 한다. 왜냐하면, 멀티패스 소트는 임시 테이블스페이스에 대한 상당한 I/O 를 유발하며 매우 느리기 때문이다. SQL문이 멀티패스 소트를 하는지 어떻게 확인할 수 있을까? 오라클 9i이전 버전에서는 쉽지 않은 일이다. 10032 트레이스 이벤트를 설정한 후 트레이스파일을 확인해야만 한다. 하지만, 오라클 9i부터는, 소트를 수행하는 SQL문의 hash value를 이용하여 V$SQL_WORKAREA 또는 V$SQL_WORKAREA_ACTIVE 뷰를 조회해 보는 것만으로도 확인이 가능하다. 소트에 대한 더욱 자세한 사항은 International Oracle Users Group (IOUG) 2004 conference proceedings (www.ioug.org) 의 “If Your Memory Serves You Right” 기술백서를 참고하라.
이러한 경우, 튜닝의 목표는 디스크 소트 횟수를 최소화하는 것이다. SORT_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )을 크기를 증가함으로써 디스크 소트 횟수를 줄일 수 있다. 하지만, 이것은 극단적으로 SORT_AREA_SIZE 가 작게 설정된 경우가 아니라면 근본적인 해결방법이라고는 할 수 없다. 먼저 어플리케이션에서 소트가 반드시 필요한지를 확인해야 한다. 어플리케이션들은 DISTINCT와 UNION 함수를 남용하여 사용하는 경향이 있다. 가능하다면 UNION보다는 UNION ALL을 사용하고, SORT MERGE 보다는 HASH 조인, HASH 조인보다는 NESTED LOOPS조인을 사용하도록 해야 한다. 또한 옵티마이저가 드라이빙 테이블을 올바르게 선택했는지도 확인할 필요가 있다. 결합 인덱스의 컬럼을 자주 쓰이는 ORDER BY절과 잘 맞게 설정해 놓으면 소트를 피할 수 도 있다. 오라클 9i라면 PGA_AGGREGATE_TARGET 을 사용하여 SQL work area 를 자동적으로 할당 받아 사용할 수 있도록 설정하는 것도 고려해 볼 만 하다. 통계적으로 보았을 때, 자동적으로 메모리관리를 하도록 하면 메모리소트의 비율을 더 높여준다.
세그먼트를 읽고 있는 세션을 발견했다면, HASH_AREA_SIZE (오라클 9i 에서 PGA_AGGREGATE_TARGET 을 사용하고 있는 경우는 work area size )가 작아서 메모리에 해쉬 테이블을 수용하지 못하는 경우이다. 해결방법은 이미 언급됐던 것과 유사하다. 만약 HASH_AREA_SIZE 가 너무 작은 경우만 아니라면 HASH_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )를 조정하기 전에 먼저 어플리케이션과 SQL문을 튜닝해야 한다.
만약에 병렬 쿼리(parallel query) 슬레이브에서 direct reads가 발생한다면, 병렬 스캔(parallel scan)이 parent SQL문에 적합한지와 슬레이브의 개수가 적당한지 확인해야 한다. 또한 쿼리 슬레이브들이 시스템의 CPU와 디스크 자원을 모두 써버리지는 않는지도 확인해야 한다. Parent SQL문의 hash value와 쿼리 슬레이브들이 수행하는 SQL의 hash value가 동일하지 않기 때문에 parent SQL문을 찾는 것은 쉽지 않은 일이다. 오라클 8.1.5에서 V$PX_SESSION 뷰가 소개되기 전까지는 더욱 힘든 일이었다. 아래의 2가지 예제는, 병렬 쿼리가 수행될 때 parent SQL문장을 찾는 방법을 오라클 8.1.5이전 버전과 이후 버전에 대해서 각각 설명한다.
--오라클 8.1.5이전 버전
--Note: 아래의 쿼리는 SYS 유저에 의해 수행되는 병렬 쿼리 문장들을 구분할 수 없다
--왜냐하면, SYS 유저들은 동일한 AUDSID를 공유하기 때문이다.
select decode(ownerid,2147483644,'PARENT','CHILD') stmt_level,
audsid,
sid,
serial#,
username,
osuser,
process,
sql_hash_value hash_value,
sql_address
from v$session
where type <> 'BACKGROUND'
and audsid in (select audsid
from v$session
group by audsid
having count(*) > 1)
order by audsid, stmt_level desc, sid, username, osuser;
STMT_L AUDSID SID SERIAL# USERNAME OSUSER PROCESS HASH_VALUE SQL_ADDR
------ -------- ---- ---------- ------------- ---------- ----------- ---------------- --------
PARENT 3086501 20 779 INTREPID cdh8455 16537 3663187692 A0938E54
CHILD 3086501 12 841 INTREPID cdh8455 16544 817802256 A092E1CC
CHILD 3086501 14 2241 INTREPID cdh8455 16546 817802256 A092E1CC
CHILD 3086501 17 3617 INTREPID cdh8455 16540 817802256 A092E1CC
CHILD 3086501 21 370 INTREPID cdh8455 16542 817802256 A092E1CC
아래의 쿼리는 오라클 8.1.5이상 버전에서 사용된다.
select decode(a.qcserial#, null, 'PARENT', 'CHILD') stmt_level,
a.sid,
a.serial#,
b.username,
b.osuser,
b.sql_hash_value,
b.sql_address,
a.degree,
a.req_degree
from v$px_session a, v$session b
where a.sid = b.sid
order by a.qcsid, stmt_level desc;
STMT_L SID SERIAL# USERNAME OSUSER HASH_VALUE SQL_ADDR DEG REQ_DEG
--------- ----- -------- ------------- --------- ------------- -------------- ------ -------
PARENT 20 779 INTREPID cdh8455 3663187692 A0938E54
CHILD 17 3617 INTREPID cdh8455 817802256 A092E1CC 4 4
CHILD 21 370 INTREPID cdh8455 817802256 A092E1CC 4 4
CHILD 12 841 INTREPID cdh8455 817802256 A092E1CC 4 4
CHILD 14 2241 INTREPID cdh8455 817802256 A092E1CC 4 4
Parameter & Wait Time
Wait Parameters
Direct path read 대기이벤트의 대기 파라미터는 다음과 같다.
- P1 : Absolute File#
- P2 : Starting Block#
- P3 : 블록수
Wait Time
I/O관련 이벤트이므로 타임아웃이 발생하지 않으며, 세션은 I/O가 완료될 때까지 대기한다
Check Point & Solution
Parallel Query의 성능을 높인다
Parallel Query를 수행하는 과정에서의 direct path read 대기는 필연적인 것으로 이 대기 자체를 튜닝하는 것은 불가능하다. 오히려 SQL 튜닝을 통해 Parallel Query 자체의 성능을 개선시키는 것이 옳은 접근 방법이다. 시스템의 용량에 비해 불필요하게 Parallel Query를 수행하는 것은 오히려 성능을 저하시키는 요인이 된다. 한가지 기억할 것은 데이터 파일에 대해 직접 읽기 작업을 수행하기 전에 읽기의 대상이 되는 객체의 더티 블록이 데이터 파일에 기록이 되어야 한다는 것이다. 즉 체크포인트가 발생하게 된다. 이 작업을 수행하는 동안 코디네이터 세션은 enq: TC - contention 대기를 겪게 된다.
I/O 시스템의 성능을 높인다.
I/O 개요
I/O는 오라클에서 가장 중요한 자원이다. 오라클이 제공하는 모든 기능은 결국 어떻게 하면 데이터를 파일에 잘 쓰고 잘 읽느냐에 관한 것이다. 따라서 자연스럽게 오라클 성능문제 중 많은 수가 I/O와 관련이 있다. I/O와 관련된 오라클의 성능문제를 이해하려면 오라클에서의 I/O 작업은 여러 개의 레이어(Layer)로 이루어져 있다는 사실을 이해해야 한다. 오라클에서의 I/O 작업 레이어를 다음과 같이 나누어 설명해 보기로 하자.
1. 어플리케이션 레이어 : select/insert/update/delete/truncate...
2. 오라클 메모리 레이어: Buffer cache | PGA
3. 오라클 세그먼트 레이어: Datafile, tempfile, Tablespace, Segment
4. OS/디바이스 레이어: Asynch I/O, Direct I/O, Raw device, RAID, ...
I/O 성능문제의 원인을 파악하는 것과 해결책을 찾는 과정은 항상 1 -> 2 -> 3 -> 4 의 순서를 따라야 한다. 논리적인 면에서뿐만 아니라 경제적인 면에서 더욱 그렇다. 각각의 레이어에 대해 필요한 사전지식과 메커니즘에 대해서 논의해보자.
어플리케이션 레이어(Application Layer)
어플리케이션을 효과적으로 구현하여, 불필요한 I/O를 최소화해야 한다. 비효율적인 어플리케이션을 그대로 두고 시스템을 튜닝한다는 것은 거의 불가능하다. 오라클은 I/O를 효율적으로 처리하는 다양한 기법들을 제공한다. Parallel Query, Parallel DML, Nologging, Direct load, Direct read 등이 대표적인 예이다. 오라클의 버전이 올라갈수록 더욱 효과적으로 작업을 처리할 수 있는 강력한 SQL 기능이 추가된다. 해석함수( Analytical Function )가 대표적인 경우인데, 이 함수를 잘 이용하면 I/O를 크게 줄이면서 다양한 집계기능을 구현할 수 있다. 이러한 최신의 SQL 문장을 사용하여 I/O부하를 줄이도록 해야 한다. 데이터의 성격에 따라 클러스터(Cluster), IOT, 파티셔닝(Partitioning), 비트맵 인덱스(Bitmap Index)등의 기능을 적절히 사용해서 I/O를 효과적으로 사용하는 것 또한 어플리케이션의 몫이다.
오라클 메모리 레이어(Oracle Memory Layer)
버퍼 캐시는 오라클 I/O 관리의 핵심이다. 자주 사용하는 블록들을 메모리에 캐시함으로써 물리적인 I/O를 줄일 수 있다. 오라클의 버전이 올라감에 따라 버퍼 캐시를 처리하는 알고리즘이 끊임없이 개선되고, 더불어 새로운 관리 방법들이 제공된다. 버퍼 캐시를 효과적으로 사용하게 되면 물리적 I/O가 줄어들고 자연스럽게 I/O 성능문제가 해결되는 경우가 많다. 오라클이 제공하는 기능들에는 다음과 같은 것들이 있다.
- 첫째, Touch count 기반의 효율적인 LRU 알고리즘을 제공한다.
- 둘째, Buffer Pinning 기법을 통해 불필요한 래치 경합을 줄이고, 현재의 읽기 작업에 사용될 확률이 높은 블록들을 메모리에서 밀려나지 않게끔 한다.
- 셋째, 다중 버퍼 풀( Multiple buffer pool ) 기능을 이용하면 휘발성 블록과 메모리 상주 블록을 구분해서 효과적으로 관리할 수 있다. 시스템에서 보편적으로 자주 사용되는 객체들은 Default 버퍼를 사용한다. 비교적 주기적으로 사용되는 작은 크기의 객체들은 Keep 버퍼에 상주시키는 것이 좋다. 반대로 아주 적은 빈도로 사용되는 큰 크기의 객체들은 Recycle 버퍼를 사용함으로써 중요한 메모리 영역을 낭비하는 일을 방지할 수 있다.
- 넷째, 오라클 9i부터는 블록 크기를 2K ~ 32K까지 사용 가능하다. 객체의 속성을 고려하여 큰 사이즈의 블록을 사용하는 것이 유리한 경우(가령 로우의 크기가 크고 풀테이블스캔으로 데이터를 읽는 경우가 많은 경우)에는 큰 크기의 블록을 사용함으로써 성능 개선 효과를 얻을 수 있다.
- 다섯째, 메모리에 올릴 필요가 없는 대용량의 데이터를 처리할 때는 버퍼 캐시를 우회하는 방법을 사용할 수 있다. 이러한 기능을 direct path I/O 라고 부른다. Direct path I/O 를 사용하면 SGA영역을 거치지 않기 때문에 메모리 공유를 위한 동기화 메커니즘이 불필요하고 그만큼 성능이 개선된다. Direct path I/O 의 반대말은 conventional path I/O 로 SGA 즉, 버퍼 캐시를 경유하는 것을 말한다. 오라클은 영구 세그먼트(Permanent Segment)와 임시 세그먼트(Temporary Segment) 모두에 대해 direct path I/O 를 지원한다. Parallel Query나 Parallel DML등은 영구 세그먼트에 대해 direct path I/O 를 사용한다. 정렬 작업은 임시 세그먼트에 대해 direct path I/O 를 사용한다. LOB 세그먼트는 약간 독특한 처리 메커니즘을 가지고 있는데, LOB 컬럼 생성시 부여하는 스토리지(Storage) 속성에 따라 direct path I/O 를 사용할 수도 있고, conventional path I/O 를 사용할 수도 있다.
오라클 세그먼트 레이어(Oracle Segment Layer)
일반적인 데이터들은 데이터파일에 저장된다. 임시 테이블스페이스(Temporary tablespace)를 사용할 경우, 기본적으로 데이터 파일이 아닌 임시파일(Temp file)에 데이터를 저장한다. 오라클 7.3 이전 버전에서는 정렬 작업을 위해 영구 테이블스페이스(Permanent Tablespace)를 사용해야 했는데 이 경우 과도한 익스텐트의 할당과 해제로 인해 많은 성능문제가 야기되며, 특히 ST 락 경합으로 인한 성능 저하 현상이 생기는 경우가 많았다. 오라클 8i부터 사용가능한 임시 테이블스페이스(Temporary tablespace)와 임시파일(Tempfile) 기능을 사용하면 ST 락 경합은 더 이상 문제가 되지 않는다. 오라클 8i부터 제공되는 LMT( Locally Managed Tablespace )와 오라클 9i부터 제공되는 ASSM( Automatic Segment Space Management )을 사용하면 익스텐트 및 세그먼트 공간의 부적절한 관리에서 오는 성능문제를 대부분 해결할 수 있다. 대용량의 테이블은 파티션(Partition)을 이용해서 관리하는 것이 유리한 경우가 많다. 관리적인 측면에서뿐만 아니라, 대량의 데이터를 처리하는 경우 원하는 범위만을 스캔하는 것이 가능하므로 필요한 I/O 범위를 줄이는 효과가 있다.
OS/디바이스 레이어(Device Layer)
오라클은 가능하면 비동기 I/O(Asynchronous IO)를 사용할 것을 권장한다. 비동기 I/O는 읽기 작업뿐만 아니라 특히 DBWR이나 LGWR등 쓰기작업을 수행하는 프로세스가 비동기적으로 작업을 처리할 수 있도록 해줌으로써 I/O 작업의 속도를 전반적으로 개선시켜준다. 불행히도 많은 OS들에서 진정한 비동기 I/O는 로디바이스(Raw device)에서만 사용한 것으로 알려져있다. 비동기 I/O를 사용하는 것이 불가능하다면 OS차원에서 Direct I/O를 사용하는 것이 바람직하다. Direct I/O를 사용하는 경우 OS의 버퍼 캐시를 우회함으로써 불필요한 I/O 작업을 최소화한다. DBWR 프로세스를 복수개로 사용하는 것 또한 방법이 될 수 있다. 컨트롤 파일(control file)의 개수나 리두로그 파일의 개수가 불필요하게 많다면 복구가 가능한 최소한만큼만 유지하는 것도 도움이 된다.
Direct I/O를 사용하는 경우에는 로디바이스를 사용할 필요가 없다는 견해가 많다. Direct I/O를 사용할 경우 OS의
버퍼 캐시를 경유하지 않으므로 그 작동 방식이 로디바이스와 거의 동일하기 때문이다. 하지만, 로디바이스가 I/O 성능개선의 중요한 방법이라는
사실 자체는 의심의 여지가 없는 것으로 보인다.
한가지 유념할 것은 로디바이스나 Direct I/O가 비효율적으로 과다하게 I/O를 수행하는 어플리케이션에 대한 무조건적인 해결책이
아니라는 사실이다. 가령 매우 비효율적인 I/O를 수행하는 어플리케이션의 속도를 개선시키기 위해 기존의 파일 시스템을 로디바이스로 수정했다고
가정하자. 어플리케이션의 성능이 개선될까? 불행하게도 그렇지 않을 확률이 있다. 로디바이스를 사용함으로써 I/O 작업 자체는 빨라지지만,
OS에서 제공하는 버퍼 캐시를 사용하지 못하기 때문이다. 가령 파일 시스템을 사용할 경우에는 100만번의 Physical Read중 실제로는
10만번만이 실제 디스크 읽기를 유발하고, 나머지는 90만번은 OS의 버퍼 캐시에서 이루어질 수 있다. 이런 경우에 로디바이스를 사용하면
100만번의 Physical Read가 모두 디스크 읽기로 이어지기 때문에 오히려 어플케이션의 성능이 저하될 수도 있다. 어플리케이션의 적절한
튜닝이 항상 우선이며, I/O 시스템의 성능 개선은 I/O 시스템이 “실제로” 느린 경우에만 필요하다.
RAID로 구성된 I/O 시스템을 사용하는 경우에는 RAID 레벨(level)을 신중하게 선택해야 한다. 리두 로그 파일과 같이
쓰기작업이 왕성한 자원에 대해 RAID-5를 사용하는 것은 성능에 큰 문제를 일으킨다. 데이터파일의 경우에도 RAID-5는 문제를 일으키는
경우가 많다. 가능하면 RAID1+0이나 RAID0+1을 사용하는 것이 기본적인 권고안이다. 파일들을 물리적으로 분리시킴으로써 디스크간의 경합을
피하는 것 또한 중요하다. SAN과 같이 통합된 스토리지 시스템을 이용하는 경우에는 해당 엔지니어와의 협의를 통해 파일들간에 적절한 분산이
이루어지도록 설정할 필요가 있다. 또한 아카이브 모드로 데이터베이스를 운영하는 경우에는 리두 로그와 아카이브 로그간의 경합이 발생하지 않도록
구성해 주어야 한다.
I/O 시스템을 변경하는 것은 많은 시간와 돈을 요구하는 경우가 많기 때문에 항상 최후의 선택사항으로 남겨 두어야
한다. 1~4 단계만으로 문제가 해결되지 않는 경우에만 5단계의 튜닝을 고려하는 것이 바람직하다.
RAID의 정의에 대해서만 간단하게 알아보기로 하자. RAID는 Redundant Arrays of Inexpensive(Independent) Disks의 약자로 복수개의 디스크를 조합해서 I/O 시스템을 구성하는 기술을 말한다. 소프트웨어적으로 구현될 수도 있으나 성능 면에서는 하드웨어적으로 구현된 것이 훨씬 유리하다.
- RAID0 : 스트라이핑(Striping). 데이터를 여러 개의 디스크에 분할 저장하도록 구성하는 것을 말한다. 데이터의 부하가 자동으로 분산되므로 성능면에서 유리하지만, 하나의 디스크만 고장나도 전체 I/O가 불가능해지는 단점이 있다.
- RAID1 : 미러링(Mirroing). 여러 개의 디스크에 같은 데이터를 저장하는 기법을 말한다. 하나의 디스크가 고장나도 I/O에 아무런 장애가 없다는 점에서 신뢰성이 높다. 하지만 항상 원하는 용량의 2배에 해당하는 디스크가 필요하다는 단점이 있다.
- RAID5 : 최소한 3개의 디스크를 사용해서 분산 저장하는 방식으로, 데이터 저장시 패리티(Parity) 비트를 저장해서 이후 하나의 디스크가 고장나도 패리티 비트를 이용해서 복구가 가능한 기법을 말한다. 디스크 활용도가 가장 높다고 할 수 있지만 패리티 저장에 따른 부하로 쓰기작업이 왕성한 경우 성능이 저하되는 문제점이 있다.
오라클은 데이터파일이나 컨트롤 파일, 리두 로그 파일에 대해서 RAID5보다는 RAID0+1 또는 RAID1+0을 사용할 것을 권장한다. RAID0+1은 물리적인 스트라이핑에 논리적인 미러링을 구현하는 것을 말하며, RAID1+0은 물리적인 미러링에 논리적인 스트라이핑을 구현하는 것을 말한다.
메타링크 문서번호 30286.1 에서 RAID 구성에 대한 자세한 정보를 제공한다.
Direct Path I/O
오라클의 I/O는 기본적으로 SGA(버퍼 캐시)를 경유한다. 하지만 특수한 상황에서는 SGA를 우회해서 PGA에 데이터를 올린다. 데이터를 공유할 필요가 없을때는 버퍼 캐시에 데이터를 적재하는 과정에서 발생하는 오버헤드를 피함으로써 성능을 개선하는 것이 가능하다. 버퍼 캐시내의 변경된 블록을 데이터파일에 기록하는 것은 DBWR 고유의 작업이다. 반면 버퍼 캐시를 우회하는 쓰기 작업은 개별 프로세스가 직접 수행하게 된다. 이처럼 버퍼 캐시를 우회하는 I/O 작업을 direct path I/O 라고 부른다. 오라클은 다음과 같은 경우에 direct path I/O 를 사용한다.
- 정렬작업을 위해 정렬 세그먼트(Sort segment)를 읽고 쓰는 경우. direct path read temp , direct path write temp 이벤트를 대기한다.
- Parallel Query를 위해 데이터파일을 읽는 경우. direct path read 이벤트를 대기한다.
- PDML이나 CTAS를 위해 데이터파일을 쓰는 경우. direct path write 이벤트를 대기한다.
- NOCACHE 속성으로 생성된 LOB 세그먼트를 읽고 쓰는 경우. direct path read(lob) , direct path write(lob) 이벤트를 대기한다.
- I/O 시스템이 데이터를 읽어서 오라클에 반환하는 속도보다 훨씬 빠른 속도로 버퍼를 요구할 때. 이 경우 오라클 성능개선을 위해 readahead I/O (이후에 읽을 것으로 판단되는 데이터를 미리 한꺼번에 읽는 I/O 작업을 말함)를 이용한다. 이 경우 direct path read 이벤트를 대기한다.
Direct path I/O 와 관련된 통계값에 대해 정리하면 다음과 같다.
- physical reads : 디스크에서 읽은 블록수. Direct path I/O 여부와 무관하게 물리적인 읽기 작업이 발생할 경우에는 항상 증가한다.
- physical reads direct : Direct path I/O 를 통해 읽은 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
- physical reads direct (lob) : LOB 데이터를 direct path I/O 를 통해 읽는 블록수
- physical writes : 디스크에 기록한 블록수. Direct path I/O 여부와 무관하게 물리적인 쓰기 작업이 발생할 경우에는 항상 증가한다.
- physical writes direct : Direct path I/O 를 통해 기록한 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
- physical writes direct(lob) : LOB 데이터를 direct path I/O 를 통해 기록한 블록수
- sort(disk) : 디스크를 이용한 정렬 작업 회수. 디스크를 이용한 정렬 작업이 발생할 경우에는 정렬 세그먼트에 대해 direct path I/O 를 사용한다.
- sort(memory) : 메모리를 이용한 정렬 작업 회수
물리적인 읽기 작업 중 버퍼 캐시를 경유한( conventional path I/O ) 읽기 작업은 다음 공식으로 계산할 수 있다.
conventional physical reads = physical reads –( physical reads direct + physical reads direct(lob) )
Direct path I/O 의 성능문제는 대부분 I/O 시스템의 성능과 직접적인 관련이 있다. Direct path I/O 는
버퍼 캐시를 경유하지 않기 때문에 동기화에 따른 오버헤드가 없다. 따라서 경합으로 인한 성능저하 현상이 발생하지 않는다. 정렬작업과 같은 경우를
제외하고는 튜닝 작업을 통해 direct path I/O에서의 대기회수 및 대기시간을 줄이는 것은 불가능하다. 만일 direct path I/O
의 수행 성능상에 문제가 생긴다고 판단되면 I/O 자체의 성능을 개선시키는 것에 초점을 맞추는 것이 옳다.
오라클의 direct path I/O 는 OS의 direct IO와 다른 개념이라는 것에 유의하자. 오라클의
direct path I/O 는 SGA의 버퍼 캐시를 경유하지 않는 것이고, OS의 direct I/O는 OS의 버퍼 캐시를 경유하지 않는
것이다. 오라클의 캐시와 OS의 캐시를 같이 사용하는 것을 더블 버퍼링(double-buffering)이라고 부르는데, 보통 성능에 이롭지 않은
것으로 알려져 있다.
하지만 더블버퍼링이 성능에 미치는 영향의 정도는 시스템의 특성이나 어플리케이션의 특성에 따라 다를 수 있다.
_DB_FILE_DIRECT_IO_COUNT 의 조정
_DB_FILE_DIRECT_IO_COUNT 히든 파라미터의 값이 direct path I/O 에서의 최대 I/O 버퍼 크기를 결정한다. 오라클 9i부터 이 값은 기본적으로 1M의 값을 가진다. 하지만 실제로는 O/S나 하드웨어 설정에 따라 최대값이 결정된다. 이 값을 높이면 Parallel Query의 성능이 높아질 수도 있으나, 대부분 실제 사용가능한 값은 1M보다 작은 값이므로 실제로는 변경할 필요가 없다.
Event Tip
direct path read와 undo
direct path read 가 비록 데이터파일에서 직접 데이터를 읽지만, 언두를 참조하는 메커니즘은 동일하다. 즉, direct path read 는 SGA를 경유하지 않을 뿐, 읽기 일관성(Read consistency)을 보장하는 방법은 동일하다. 이것을 증명하는 방법은 크기가 작은 언두 테이블스페이스(Undo tablespace)를 생성한 후, Parallel Query를 수행하면서 다른 세션에서 DML을 과다하게 수행할때 ORA-01555(Snapshot too old) 에러가 나는 것을 관찰하는 것이다.
ERROR at line 1:
ORA-12801: error signaled in parallel
query server P002
ORA-01555: snapshot too old: rollback segment number 68
with name "_SYSSMU68$" too small
위의 에러는 곧 PQ 슬레이브 세션이 데이터파일에 대해 direct read 를 수행하면서 변경된 블록을 발견하면 언두 데이터를 참조하는 것으로 해석할 수 있다.
데이터 파일에 대한 direct path read의 증명
PQ 수행시 슬레이브 세션에서의 direct path read가 임시 영역이 아닌 데이터파일에 대한 direct path read 인 것을 어떻게 증명할 수 있을까? 하나의 세션에서 PQ를 수행한 후, PQ가 수행되는 동안 다른 세션에서 V$SESSION_WAIT 뷰를 조회해서 P1 값을 얻으면 어떤 파일에 대한 direct path read 인지 알 수 있다. 아래 스크립트를 보자.
Session A : Degree가 4 인 pq_test 테이블에 대해 PQ를 여러번 수행하면서 direct path read 유발
declare
v_count number;
begin
for idx in 1 .. 100 loop
select count(*) into v_count from pq_test;
end loop;
end;
/
Session B: Session A에서 발생한 PQ의 슬레이브 세션에 대해 direct path read 이벤트를 캡쳐한다.(Session A의 SID = 162)
set serveroutput on size 100000
declare
begin
for px in (select * from v$px_session where qcsid = 162) loop
for wait in (select * from v$session_wait where
sid = px.sid and event like '%direct path read%') loop
dbms_output.put_line('SID='||wait.sid ||
', P1=' || wait.P1);
end loop;
end loop;
end;
/
Session B의 수행결과는 아래와 같다.
SID=138, P1=1
SID=152, P1=1
SID=144, P1=1
...
SID=142, P1=1
SID=144, P1=1
SID=138, P1=1
direct path read 대기이벤트의 P1 = file#이므로 해당되는 파일이 실제 데이터파일인지 확인할 수 있다.
SQL>exec print_table('select * from v$datafile where file# = 1'); 을
FILE# : 1
...
BLOCK_SIZE : 8192
NAME :
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UKJADB\SYSTEM01.DBF
PLUGGED_IN : 0
BLOCK1_OFFSET : 8192
AUX_NAME : NONE
위와 같이 system01.dbf라는 데이터 파일에 대한 direct path read임을 알 수 있다.
DB_FILE_DIRECT_IO_COUNT
DB_FILE_DIRECT_IO_COUNT 파라미터는 direct path read 성능에 영향을 미칠 수 있다. 해당 파라미터는 direct reads , direct writes 에 대한 최대 I/O 버퍼크기로 설정해야 한다. 오라클 8i까지는 대부분의 플랫폼에서 기본 설정값은 64 블록이었다. 따라서
DB_BLOCK_SIZE 가 8K인 경우 direct reads , direct writes 에 대한 최대 I/O 버퍼크기는 512K(8K*64)이다. 최대 I/O 버퍼 크기는 하드웨어의 한계 값에 의해서도 제한된다. 오라클 9i에서는 DB_FILE_DIRECT_IO_COUNT 파라미터는 hidden 파라미터로 변경되었고, 블록수가 아니라 바이트(byte)단위로 변경되었다. 오라클 9i의 기본 설정값은 1 MB이다. 실질적인 direct I/O 크기는 하드웨어 환경설정(configuration) 및 한계 값에 의해서도 영향을 받는다
Direct Read I/O크기 알기
세가지 방법으로 실제적인 direct read I/O 크기를 알 수 있다.
- direct read 를 수행하는 세션에 대해 10046 트레이스 이벤트를 레벨 8로 설정한다. P3 파라미터는 읽은 블록수를 나타낸다. 아래의 예제의 경우, 블록 크기가 8K 이므로 direct path read I/O 크기는 64K(8K*8블록)이다. 또한, V$SESSION_WAIT 뷰를 조회하여 direct path read 대기이벤트의 P3값을 확인할 수 있다.
WAIT #1: nam='direct path read' ela= 4 p1=4 p2=86919 p3=8
WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86927 p3=8
WAIT #1: nam='direct path read' ela= 10 p1=4 p2=86935 p3=8
WAIT #1: nam='direct path read' ela= 39 p1=4 p2=86943 p3=8
WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86951 p3=8
WAIT #1: nam='direct path read' ela= 38 p1=4 p2=86959 p3=8
...
- O/S의 truss, tusc, trace, 또는strace를 이용하여 direct reads 혹은 direct writes 를 수행하는 UNIX 프로세스를 트레이스 한다. 오라클 9i에서 truss 리포트의 일부분으로 direct I/O 크기가 64K임을 알 수 있다.
9218/1: kaio(AIOWAIT, 0xFFBECE98) = 1
9218/1: lwp_cond_signal(0xFEB7BFA0) = 0
9218/3: pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) = 65536
9218/1: lwp_cond_signal(0xFEB69FA0) = 0
9218/4: pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) = 65536
- 10357 트레이스 이벤트를 설정하여(예: alter session set events '10357 trace name context forever, level 1') direct I/O를 수행하는 세션에 대한 디버그 정보를 확인한다.
Unix process pid: 4375, image: oracle@kccdeds73 (P000)
*** SESSION ID:(9.18) 2004-02-08 21:47:01.908
DBA Range Initialized: length is 1570, start dba is 0100602b
kcblin: lbs=fc86c1cc flag=8 slot_cnt=32 slot_size=65536 state obj=24321224
kcblin: state objects are: Call=243a2210,Current Call=243a2210, Session=24321224
kdContigDbaDrCbk:starting from tsn 5
kdContigDbaDrCbk:starting from rdba 0100602b
kdContigDbaDrCbk:returning 1570 blocks
kcblrs:issuing read on slot : 0
kcbldio:lbs=fc86c1cc slt=fc86408c typ=0 async=1 afn=4 blk=602b cnt=8 buf=fc87fe00
kcblrs:issuing read on slot : 1
kcbldio:lbs=fc86c1cc slt=fc864210 typ=0 async=1 afn=4 blk=6033 cnt=8 buf=fc89fe00
kcblcio: lbs=fc86c1cc slt=fc86408c type=0 afn=4 blk=602b cnt=8 buf=fc87fe00
...
이전의 예제에서, 트레이스 파일은 쿼리 슬레이브(query slave)#0 의 수행 내역이다. direct path 오퍼레이션을 위해 32개의 I/O 슬롯이 가용하다(slot_cnt=32). 하나의 슬롯이 I/O 단위이며, 각각의 슬롯은 65536 bytes이다(slot_size=65536). 읽기 오퍼레이션 시 비동기식 I/O가 사용된다(async=1). 쿼리 슬레이브는 데이터파일 #4를 읽는다(afn=4). 읽어 들이는 블록개수는 8개이다(cnt=8). 블록크기는 8K이므로 65536 bytes를 전송한다.
이 경우, direct I/O 슬롯크기는 프로세스가 1 MB 전부를 사용하지 못하게 한다.
_DB_FILE_DIRECT_IO_COUNT 파라미터의 기본 설정값은 1 MB이다. 슬롯크기는 10351 이벤트를 사용하여 변경할 수
있다. 또한 슬롯의 개수도 10353 이벤트를 사용하여 변경할 수 있다. Caution : 앞에서 기술한 정보를 이용하여, 자신이 사용하는
시스템에 대한 direct I/O 의 처리량을 파악할 수 있다. 슬롯크기와 direct I/O 슬롯의 개수에 대한 기본 설정값을 쉽게 변경하지
마라. 변경 작업 전에 자신이 사용하는 하드웨어의 한계를 파악해야 하며, 어플리케이션과 SQL문을 최적화시키는 데 초점을 맞추어야 한다.
'ORACLE > OWI' 카테고리의 다른 글
| Latch의 발생과 경합의 원인 (0) | 2009.03.09 |
|---|---|
| Oracle에서 Latch 가 성능 저하의 원인이 아닙니다 (0) | 2009.03.01 |
| Enqueue 와 Latch (0) | 2009.03.01 |
| LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법 (0) | 2008.12.22 |
| oracle lock 종류 (0) | 2008.12.22 |
