Db file scattered read

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] Basic Info

오라클은 사용자가 요청한 데이터가 SGA의 버퍼 캐쉬에 존재하지 않을 경우 서버 프로세스가 데이터 파일로부터 해당 데이터 블록을 버퍼 캐쉬로 로드한다. 이를 Conventional Path I/O라 한다. Conventional Path I/O는 멀티 블록 I/O와 싱글 블록 I/O로 나뉘어지게 된다. 멀티블록 I/O는 한 번에 여러 개의 연속된 블록을 읽어 들이는 작업을 수행하는 I/O이며, 싱글블록 I/O는 한 번에 하나의 블록만 읽어 들이는 작업을 수행하는 I/O 이다.

오라클은 풀테이블스캔(Full Table Scan. FTS)이나 인덱스 패스트 풀스캔(Index Fast Full Scan)을 수행하는 경우 성능 보장을 위해서 가능한 여러 개의 블록을 한꺼번에 읽는 멀티블록 I/O를 수행한다. 멀티블록 I/O를 한번 수행할 때마다 물리적인 I/O가 끝나기를 기다리게 되며, db file scattered read 이벤트를 대기하게 된다.

멀티블록 I/O는 DB_FILE_MULTIBLOCKREAD_COUNT 파라미터로 지정된 값만큼 수행한다. 이 값은 OS마다 최대값에 한계가 있는데, 다음과 같은 방법으로 최대값을 확인할 수 있다.

SQL> alter system set db_file_multiblock_read_count=10000;
시스템이 변경되었습니다.
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE ------------------------------ --------- -------------------------- db_file_multiblock_read_count integer 128

오라클은 FTS를 수행하는 경우에도 싱글블록 I/O를 수행하는 경우가 있다. 이 경우에는 FTS라고 하더라도 db file sequential read 대기가 발생하게 된다. FTS에서 싱글 블록 I/O를 사용하거나 MBRC보다 작은 수의 블록을 읽어들이는 경우는 다음과 같다.

  • 익스텐트 경계에 도달한 경우 : 가령 하나의 익스텐트가 9 블록이고 한번의 멀티 블록 IO로 8개의 블록을 읽는다면 한번의 멀티 블록 IO로 8 블록을 읽고 남은 하나의 블록은 싱글 블록 IO로 읽게 된다. 만일 남은 블록이 2개라면 멀티 블록 IO를 수행하되 2개의 블록만을 읽는다.
  • 스캔 도중에 캐시된 블록이 있을 경우 : 가령 8개의 블록을 읽는데 그 중 세번째 블록이 캐시되어 있다면 오라클은 앞의 2개를 멀티 블록 IO로 읽고 세번째 블록에 대해서는 한번의 Logical IO를 수행하고, 다시 남은 5개의 블록을 멀티 블록 IO로 읽게 된다. 이런 경우가 자주 발생할 경우 불필요한 IO가 유발되어 FTS의 속도를 저하시키는 원인이 될 수 있다.
  • Chained Row가 있는 경우 : FTS를 수행하다가 Chained Row를 만나면 오라클은 나머지 로우를 읽기 위해 추가적인 IO를 일으키는데, 이때 싱글 블록 IO를 수행하게 된다. Chained Row와 Migrated Row의 차이점에 대해 분명히 이해할 필요가 있다. Chained Row는 로우의 크기가 블록보다 큰 경우 발생한다. 따라서 더 큰 크기의 블록을 사용하거나 PCTFREE를 줄이는 것을 제외하고는 Chained Row을 제거할 수 있는 방법은 없다. Migrated Row는 처음에는 하나의 블록에 들어갔지만 이후 로우의 크기가 커지면서 블록 안에 공간이 없는 경우에 발생한다. 이 경우 실제 로우는 다른 블록으로 이동하고, 원래의 로우에는 옮겨간 블록과 로우에 대한 위치를 가리키는 ROWID가 들어간다. Migrated Row는 특히 인덱스를 통해 테이블을 스캔하는 경우에 성능에 큰 영향을 준다. 하나의 로우를 읽기 위해 두 개 이상의 블록을 읽어야 하기 때문이다. Migrated Row가 FTS에 주는 영향에 대해서는 약간의 고려가 필요하다. FTS은 HWM 이하의 모든 블록을 처음부터 순서대로 읽는 작업이다. 오라클은 FTS 수행 중 Migrated Row를 만나면 추가로 싱글 블록 IO를 일으키지 않고 읽기 작업을 계속 진행한다. 어차피 스캔을 수행하는 도중 다시 읽게 될 것을 알기 때문이다. 따라서 HWM의 위치가 동일하다면 Migrated Row가 있건 없건 FTS 자체의 성능은 거의 차이가 없다. 물론 Migration이 발생하면서 익스텐트가 추가되고 HWM이 더 멀리 이동했다면 FTS의 성능에 영향을 준다. Migrated Row를 제거하고자 할 때는 이 사실에 유념해야 한다.


[편집] Parameter & Wait Time

[편집] Wait Parameters

db file scattered read 대기이벤트의 대기 파라미터는 다음과 같다.

  • P1 : File#
  • P2 : Starting Block#
  • P3 : 블록수

[편집] Wait Time

I/O 관련 대기이벤트이므로 타임 아웃이 발생하지 않으며, 세션은 지정된 개수의 블록에 대한 I/O가 완료될 때까지 대기한다.


[편집] Check Point & Solution

[편집] 오라클의 I/O 레이어 별 해결책

SGA에 해당 데이터를 로드하는 I/O는 일반적인 작업이다. 그러므로 db file scattered read 이벤트는 db file sequential read 이벤트와 함께 오라클에서 가장 보편적으로 발생하는 대기이벤트이다. 데이터파일에서 블록을 읽으려면 결국 멀티 블록 IO나 싱글 블록 IO를 수행할 수 밖에 없기 때문이다. db file scattered read 대기를 사람들이 꺼리는 이유는 그것이 물리적 I/O와 관련이 되어 있다는 것, 그리고 FTS과 함께 나타난다는 이유 때문이다. 이것은 결국 FTS가 나쁜 것인가 하는 문제와 연결되는데, 대답은 그럴 수도 있고 아닐 수도 있다는 것이다. 모든 작업은 적절히 사용될 때만 좋은 것이고 그렇지 않은 경우는 나쁜 것이다. 가령 넓은 범위의 인덱스 스캔은 버퍼 캐시 영역을 빠른 속도로 소진시켜서 치명적인 성능문제를 야기시킬 수 있다.

오라클의 I/O 레이어를 기준으로 db file scattered read 대기 문제에 대한 해결책을 논의해보자.

[편집] 어플리케이션 레이어

db file scattered read 대기가 주로 발생하는 SQL 문을 추출해야한다. 만일 불필요하게 FTS나 인덱스 패스트풀스캔을 수행하고 있다면 SQL 문을 수정하거나 좀 더 합리적인 인덱스를 생성해주면 문제는 해결된다. 넓은 범위의 데이터를 읽을 때는 FTS가 훨씬 유리한 경우가 많다. 무리하게 인덱스를 생성시켜 주는 것이 아니라, 해당 SQL문의 특성을 고려하여 FTS가 유리한지 인덱스범위스캔이 유리한지에 대한 판단이 필요하다.

[편집] 오라클 메모리 레이어

버퍼 캐시의 크기가 지나치게 작다면 그만큼 물리적 I/O가 반복해서 필요하고 그와 비례해서 db file scattered read 대기도 늘어난다. 이 경우 free buffer waits 대기이벤트와 함께 나타날 확률이 높다. FTS에 의한 db file scattered read 대기의 심각성은 읽기작업의 성능저하뿐만 아니라 버퍼 캐시의 효율성을 떨어뜨려 세션의 작업에 영향을 줄 수 있다는데 있다. 이런 관점에서 FTS를 다루는 효과적인 방법 중 하나는 다중 버퍼 풀을 사용하는 것이다. 어차피 한번 읽고 재사용하지 않을 데이터를 무리하게 버퍼 캐시 영역에 보관해서 다른 사용자의 작업에까지 영향을 줄 필요가 있겠는가? 다중 버퍼 풀은 버퍼 캐시를 효과적으로 관리하는 강력한 방법이지만 불행히도 잘 사용되지 않고 있다. 실제 테스트를 통해 이 기능이 얼마나 성능에 많은 영향을 미칠 수 있는지 검증해보기로 하자. 테스트 시나리오는 다음과 같다.

  • 테이블의 크기가 16M 정도인 다섯 개의 테이블 READ_TEST1 ~ READ_TEST5 를 생성한다.
  • Case1에서는 DB_CACHE_SIZE 값을 32M로 주고 다섯 개의 테이블을 모두 Default 버퍼로 올린다.
  • Case2에서는 DB_CACHE_SIZE 값을 16M, DB_RECYCLE_CACHE_SIZE 값을 16M로 주고 READ_TEST1 테이블은 Default 버퍼로, READ_TEST2 ~ READ_TEST5 네개의 테이블은 RECYCLE 버퍼로 올린다.
  • Case1과 Case2에서 db file scattered read 대기가 어떻게 발생하는지 확인한다.

-- 테스트의 목적은 동시에 5개의 세션이 각각 read_test1 ~ read_test5 에 대해 FTS를 수행했을때(즉 세션1=read_test1 스캔, 세션2=read_test2 스캔, 세션3=read_test3 스캔...), read_test1의 스캔 속도를 보장하는 것이다. read_test1은 자주 액세스되는 테이블이다.

Case1 : read_test1 ~ read_test5 모두 Default 버퍼풀을 사용한 경우에는 모든 세션이 높은 db file scattered read 대기시간을 보인다.

세션1 : read_test1 테이블을 10번 FTS
EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- db file scattered read 1038 7053 SQL*Net message from client 19 810 db file sequential read 92 437 ...
세션2 : read_test2 테이블을 10번 FTS
EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- db file scattered read 1054 7075 SQL*Net message from client 19 2532 db file sequential read 83 452 ...

Case2: read_test2 ~ read_test5 테이블은 Recycle 버퍼풀을 사용하기 때문에, Default 버퍼풀에 대한 경합은 사라진다. 따라서 Default 버퍼풀을 사용하는 read_test1에 대해 FTS를 수행하는 세션1은 db file scattered read 대기시간이 다른 세션에 비해 매우 낮다. 한가지 재밌는 사실은, read_test1 테이블뿐만 아니라 read_test2 ~ read_test5 테이블에 대한 db file scattered read 대기시간도 줄어들었다는 사실이다. 버퍼 캐시에 대한 경합이 줄어들기 때문에 그 효과가 두개의 버퍼 풀 모두에 나타난 것으로 해석할 수 있다.

세션1 : read_test1 테이블을 10번 FTS
EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- db file scattered read 124 882 <- READ_TEST1 에 대한 대기는 크게 줄어든다. db file sequential read 7 14 log file sync 2 4 ...
세션2 : read_test2 테이블을 10번 FTS
EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- db file scattered read 1170 6552 db file sequential read 78 381 read by other session 13 33 ..

위의 테스트결과에서 알 수 있듯이 버퍼 풀을 효과적으로 사용할 경우에, 상당한 성능개선효과를 거둘 수 있다는 사실을 기억하기 바란다. 다중 버퍼 풀은 세가지 면에서 버퍼 캐시의 성능을 개선시킨다. 첫째, 자주 액세스되는 객체를 메모리에 상주시킴으로써 물리적인 I/O를 최소화한다. 둘째, 휘발성의 데이터는 빠른 속도로 메모리에서 재활용함으로써 메모리의 낭비를 최소화한다. 셋째, 각 버퍼마다 별도의 cache buffers lru chain 래치를 사용하기 때문에 래치 경합을 감소시키는 효과가 있다.

FTS를 효과적으로 수행하는 또 한가지 방법은 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터의 값을 높이는 것이다. 이 파라미터는 멀티 블록 IO 수행시 한번에 읽는 블록 수를 결정한다. 따라서 이 값이 높으면 그만큼 FTS 속도가 향상되며 db file scattered read 대기도 비례해서 줄어든다. 이 값을 시스템 전체 레벨에서 높게 하는 것은 바람직하지 못하며, alter session set ... 명령을 이용해서 해당 SQL을 수행하는 구간동안만 높이는 것이 좋다. 이 값이 높아지면 FTS에 대한 비용이 낮게 계산되어 SQL 실행 계획이 변경될 가능성이 있기 때문이다.

큰 크기의 블록을 사용하는 것 또한 FTS의 성능을 향상시키는 방법이다. 큰 크기의 블록은 다음과 같은 두 가지 관점에서 FTS의 성능을 개선시킨다. 첫째, 한 블록이 포함하는 로우수가 증가하므로 같은 크기의 테이블을 구성하는데 적은 수의 블록을 사용하게 된다. 따라서 그 만큼 멀티 블록 IO의 회수가 줄어든다. 둘째는 블록 사이즈가 크면 Row chaining이나 Row migration이 발생할 확률이 낮아진다. 따라서 그로 인한 부가적인 IO가 줄어들게 된다. 대부분의 OLTP 시스템에서는 표준 블록 크기(8K)만을 사용하는 것이 일반적이다. 하지만 대량의 데이터를 자주 스캔해야 하는 DSS 시스템의 경우에는 더 큰 크기의 블록을 사용함으로써 성능개선 효과를 얻을 수 있다.

[편집] 오라클 세그먼트 레이어

파티셔닝(Partitioning)을 적절히 수행함으로써 FTS의 범위를 줄일 수 있는지 검토할 필요가 있다. 가령 100만건의 데이터중 10만건을 얻기 위해 FTS를 수행해야 한다면, 10만건에 해당하는 범위를 파티션으로 나누었다면 FTS의 범위를 1/10로 줄일 수 있다.

[편집] OS/디바이스 레이어

SQL 최적화나 버퍼 캐시의 최적화로도 문제가 해결되지 않는다면, I/O 시스템 자체의 성능을 의심해보아야 한다. db file scattered read 이벤트의 대기회수와 대기시간을 비교해서 평균대기시간이 길다면 느린 I/O 시스템이 원인일 가능성이 높다. 앞서 논의한 것처럼 I/O 시스템의 성능문제는 매우 다양한 상황에서 발생할 수 있다. 다양한 팩터를 충분히 조사할 필요가 있다.

V$FILESTAT 뷰를 이용하면 데이터파일별로 멀티블록 IO와 싱글 블록 IO의 활동성에 관한 정보를 얻을 수 있다.

SQL> select f.file#, f.name, 
s.phyrds, s.phyblkrd, s.readtim, -- 전체 읽기 작업 정보
s.singleblkrds, s.singleblkrdtim, -- Single block IO
(s.phyblkrd - s.singleblkrds) as multiblkrd, -- Multi block IO 회수
(s.readtim - s.singleblkrdtim) as multiblkrdtim, -- Multi block IO 시간
round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3)
as singeblk_avgtim, -- Single block IO 평균대기시간(cs)
round((s.readtim-s.singleblkrdtim)/(s.phyblkrd-s.singleblkrds),3)
as multiblk_avgtim -- Multi block IO 평균대기시간(cs)
from v$filestat s, v$datafile f
where s.file# = f.file#;

만일 특정 파일에서 평균수행시간이 지나치게 높게 나온다면 해당 파일이 존재하는 I/O 시스템의 성능을 높임으로써 성능개선이 가능하다. 멀티 블록 IO의 적절한 평균대기시간에 대한 절대적인 수치는 없다. 일반적으로 10 milli-seconds 내외의 평균대기시간을 유지해야하는 것으로 알려져 있다.

[편집] Event Tip

[편집] Physical I/O 분류

Physical I/O 는 Conventional Path I/O와 Direct Path I/O 로 나누어진다. Conventional Path I/O는 일반적으로 알고 있는 버퍼캐쉬를 경유하여 블록을 읽는 작업을 의미한다. Direct Path I/O는 데이터파일에 있는 블록이 버퍼캐쉬를 거치지 않고 PGA로 올리는 것으로 Direct Path I/O가 발생하게 되면, I/O 작업이 발생되기 전에 체크포인트가 발생되어 더티버퍼를 데이터 파일에 쓰게 되어 데이터 파일과 버퍼캐쉬의 내용에 대해서 동기화를 한 후 Direct Path I/O가 발생하게 된다.

[편집] Index Full Scan VS Index Fast Full Scan

인덱스 스캔에서 발생될 수 있는 성능문제는 넓은 범위 인덱스 스캔에 의한 I/O 발생량을 많이 일으키는 경우가 거의 대부분이다. 그렇다면, 과다하게 넓은 범위 인덱스 스캔이 발생할 경우에는 어떻게 해야 할 것인가?

필연적으로 많은 양의 인덱스 스캔이 발생되어야만 하고, 정렬이 필요 없는 경우라면 Index fast full scan을 사용하여 싱글블록 I/O가 아닌 멀티블록 I/O로 유도하는 것이 성능적인 측면에서 바람직할 것이다. Index full scan은 언뜻 보면 Table Full Scan과 같이 멀티블록 I/O로 해석될 것처럼 오해할 수 있는데, Index full scan은 싱글블록 I/O가 발생되므로, I/O 발생량을 증가시키는 원인이 되므로 이러한 경우에는 필히 멀티블록 I/O로 수행되는 Index fast full scan으로 유도하여야 한다.

Index fast full scan을 제어하는 파라미터는 “_FAST_FULL_SCAN_ENABLED=TRUE”로 Default가 TRUE이다.

Index fast full scan을 유도하는 힌트는 /*+ index_ffs(table_alias index_name) */이다.

[편집] DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) 설정

언급된 것처럼, 높은 MBRC 수치는 옵티마이저가 full table scan을 선호하도록 영향을 줄 수 있다. 적절한 수치는 어플리케이션(DSS 또는 OLTP)에 따라 다르다. 높은 MBRC 수치는 full table scan 수행을 좀 더 빠르게 수행할 수 있도록 하므로, 배치 처리시 유리할 수 있다. 만일 데이터베이스 서버가 배치와 OLTP 처리를 모두 수행한다면, 적절할 수치를 찾아야 한다. 기본 설정값 8은 다소 작다.

만일 full table scan이 최선의 방법이라면, 시스템에서 제공하는 최대값으로 오브젝트를 스캔 하기를 원할 것이다. 왜 작은 값으로 시간을 낭비하겠는가? 최대값이 얼마인지 확인한 후 대량의 full table scan을 수행하는 프로세스에 해당 값을 적용해야 한다.

MBRC에 대한 한계는 있다. 그것은 sstiomax, DB_BLOCK_SIZEDB_BLOCK_BUFFERS 등 몇 가지 요소에 따라 좌우된다. sstiomax은 오라클 내부적인 한계 값이며, 이것은 읽기 또는 쓰기 수행 시, 한번의 I/O 에 전송될 수 있는 데이터 양을 제한한다. 그 값은 오라클 코드안에 내부적으로 정의 되어 있으며, 오라클 버전에 따라 다양하다. 오라클 초기 버전에서는 128K였으나 오라클8부터는 1M이다. DB_BLOCK_SIZE와 MBRC를 곱한 수치가 sstiomax 을 초과할 수 없다. 또한 MBRC는 DB_BLOCK_BUFFERS / 4보다 작아야만 한다. 게다가 MBRC는 솔라리스(Solaris)의 maxphys, 파일 시스템의maxcontig등 에 따라서도 제한된다. 하지만 이런 이야기가 너무 어렵다면, 사용 환경에 맞게 쉽게 설정할 수 있는 다른 방법이 있다. 그것은 MBRC의 값을 아래의 예에서 보는 바와 같이 아주 큰 값으로 설정하는 것이다. 그러면 오라클이 시스템에서 처리 가능한 최대값으로 설정한다. 그런 후 full table scan을 수행하는SQL을 실행시킨 뒤 V$SESSION_WAIT 뷰를 조회하면 된다. db file scattered read 대기이벤트의 P3 파라미터의 수치가 현재 시스템의 최대값이 된다. 다른 방법은 10046 트레이스 이벤트를 설정하는 것이다. 이 최대값은 데이터베이스 레벨에 설정하기 보다는, full table scan의 수행속도를 향상시킬 필요성이 있는 세션에 대해 설정해야 한다.

alter session set db_file_multiblock_read_count = 1000;
select /*+ full(a) */ count(*) from big_table a;

-- 아래의 예제는 10046 트레이스 파일로부터 발췌한 것이다. 
-- 시스템에서 허용할 수 있는 최대 MBRC 는 128 블록임을 알 수 있다.
WAIT #1: nam='db file scattered read' ela= 17946 p1=6 p2=56617 p3=128
WAIT #1: nam='db file scattered read' ela= 21055 p1=6 p2=56745 p3=128
WAIT #1: nam='db file scattered read' ela= 17628 p1=6 p2=56873 p3=128
WAIT #1: nam='db file scattered read' ela= 29881 p1=6 p2=57001 p3=128
WAIT #1: nam='db file scattered read' ela= 33220 p1=6 p2=57129 p3=128
WAIT #1: nam='db file scattered read' ela= 33986 p1=6 p2=57257 p3=96
WAIT #1: nam='db file scattered read' ela= 46372 p1=6 p2=65577 p3=128
WAIT #1: nam='db file scattered read' ela= 33770 p1=6 p2=65705 p3=128
WAIT #1: nam='db file scattered read' ela= 41750 p1=6 p2=65833 p3=128
WAIT #1: nam='db file scattered read' ela= 34914 p1=6 p2=65961 p3=128
WAIT #1: nam='db file scattered read' ela= 33326 p1=6 p2=66089 p3=128

[편집] 왜 Physical I/O 비용이 비싼가?

많은 DBA들은 디스크 I/O 비용이 비싸다는 이야기를 들어왔고, 그들의 사고를 물리적 디스크와 I/O 서브시스템에 집중하도록 교육받아왔다. 물론, 스토리지 레이어는 가장 느린 콤포넌트다. 하지만 이것이 느린 이유의 전부는 아니다. 나머지 부분은 블록을 SGA로 적재할 때 오라클 내부에서 발생되는 일들 때문이다.

블록을 SGA로 적재할 때 수많은 일들이 발생한다. 간단히 말하면, 포그라운드 프로세스는 우선 프리 버퍼 리스트를 검색해야한다. 만일 maximum scan limit에 도달했을 때까지도 프리 버퍼를 찾지 못하면, 포그라운드 프로세스는 DBWR프로세스에게 프리 버퍼를 만들도록 요청한다. 그런 후 포그라운드 프로세스는 다시 프리 버퍼를 찾는다. 일단 프리 버퍼를 찾았으면, 프리 리스트 체인(free list chain) 에서 해당 블록을 제거한 후, 해당 버퍼를 LRU 리스트의 상단 부에 위치시키거나, LRU 리스트의 중간부분에 위치시킨다(LRU 리스트의 중간 부분에 위치시키는 것을 midpoint insertion이라고 하며, 오라클 8i 부터 적용되는 방식이다). 그런 후 해당 버퍼 헤더의 포인터는 적절하게 조정된다. 적어도 2개의 포인터 셋이 있으며, 변경할 때 마다 래치를 획득해야 한다. 블록의 헤더 구조 또한 초기화되고 수정되어야 한다. 버퍼를 할당하거나, 블록을 버퍼캐쉬로 적재하거나, 블록을 버퍼캐쉬로 적재하는 작업을 완료할 때까지 다른 프로세스가 해당 블록을 액세스 하는 것을 방지하기 위해 블록 헤더의 특정 bit를 초기화하고 수정해야 한다.

결과적으로 db file sequential readdb file scattered read 대기를 해결할 수 있는 최선의 방법은 메모리I/O와 디스크 I/O의 요청을 줄이는 것이다. 이것은 어플리케이션과 SQL문 튜닝을 통해 가능하다. 이제 디스크 I/O 비용이 얼마나 비싼지 알았으며, 메모리 I/O 의 비용 또한 비싸다는 것을 알게 될 것이다.

[편집] Analysis Case