11g - Serial direct read 동작원리

㈜엑셈 컨설팅본부 /DB컨설팅팀 임 경석

개요

오라클 11g 에서 처음 소개된 Serial direct read 는 대량의 데이터를 처리하는 엑사데이타 에서 매우 중요한 기능이다 . 스마트 스캔을 하기 위해서는 반드시 테이블 full scan 과 direct path read 가 선행되어야 하기 때문이다 . 그러나 , serial direct read 의 동작은 옵티마이져 환경에 영 향을 받지 않으므로 힌트를 적용하여 제어할 수 없 고 , 뜻대로 동작하지 않거나 중간에 변경되기 도 한다 . 그렇다고 임의로 동작하는 것은 아니며 몇 가지 규칙이 존재한다 . 필자는 serial direct read 가 어떠한 규칙에 의해 결정 되고 , 변경 되는지 몇 가지 사례를 통해 자세히 소개하고자 한 다 .

Serial direct read 란

DW 환경에서는 대량의 데이터를 조회하는 경우가 빈번하다 . 따라서 , 테이블이나 인덱스에 대한 풀 스캔을 피할 수 없다 . 오라클에서 풀 스캔은 여러 개의 블록을 한번에 버퍼캐시로 읽어 들이 는 멀티블록 I/O 를 통해 처리된다 . 멀티블록 I/O 는 크기가 제한된 버퍼캐시를 통해 이루어지므 로 내부적으로 복잡한 과정이 필요 하므로 버퍼캐시에 상당한 부담 이 될 수 있다 . 블록을 읽기 위 해 프리리스트 를 검색하고 버퍼를 변경할 때 마다 래치를 획득 하여 cache buffers chains latch, cache buffers lru chain latch 와 같은 이벤트가 발생할 수 있다 . 이러한 블록들은 LRU 리스트 끝에 위치 하지만 다른 세션들이 필요로 하는 캐시 된 블록들을 age out 시키고 , 이미 변경된 블 록들에 대해서는 다량의 CR 복사본을 생성할 수 있다 .

따라서 , 이러한 이유들로 인해 일반적으로 테이블 을 풀 스캔 할 경우 버퍼캐시를 거 치지 않는 PQ(Parallel Query ) 를 많이 사용한다 . PQ 슬레이브 세션들은 direct path read 를 통해 PGA 로 데이터를 직접 읽어 들이므로 버퍼캐시에 대한 부담을 줄여주고 성능에도 효과적이다 . 하지만 , 과도한 PQ 사용은 CPU, MEMORY 사용률 증가와 빈번한 체크포인트 로 인해 오히려 시스템 부 하를 가중시킬 수 있다 .

오라클은 Parallel 옵션을 사용하지 않고 direct read 가 가능한 SDPR(serial direct path read) 을 11g 에서 처음 소개하였다 . SDPR 은 테이블 full scan 이나 인덱스 fast full scan 시 싱글 모 드 로 버퍼캐시를 거치지 않고 블록을 직접 읽어 들이는 것을 말한다 . 사실 이러한 기능은 오라클 10G 에도 있었지만 본격적으로 적용된 것은 11g 부터 이다 . 특히 , 11gr2 이후 에는 몇 가지 파 라미터를 통해 적절한 제어도 가능해졌다 .

serial direct read 는 히든파라미터 _serial_direct_read 로 정의한다 . 10G 에는 기본값이 false 였지만 , 11gr2 부터 auto 로 변경 되면서 오라클이 적절하게 direct read 를 적용할 수 있 게 되었다 . _serial_direct_read 옵션은 true, false, auto, always, never 로 크게 5 가지 로 정의할 수 있 다 . 이중 true, always 는 풀 스캔 하는 모든 세그먼트를 항상 SDPR 로 처리하고 , 반대로 never 는 어느 경우든 처리하지 않는다 . 하지만 , false 의 경 우 never 와 같은 의미로 보이지만 오히려 auto 와 비 슷하게 동작한다 .

어떻게 동작 하는가

데이터베이스에 존재하는 모든 테이블이 SD PR 의 대상이 되는 것은 아니다 . 오라클은 SDPR 이 가능한 테이블의 선택 기준을 히든파라미터 _small_table_threshold 에 정의하였다 . 기본값은 _db_block_buffers 의 2% 로써 이보다 작 으면 오라클이 작은 테이블로 인식한다는 의미이다 . 10g 에서는 테이블을 풀 스캔 할 때 작은 테이블은 LRU 리스트의 중간에 위치하고 , 큰 테이블 은 LRU 리스트의 맨 끝에 위치하므로 age out 되기가 쉬워 physical read 가 발 생할 확률이 높 음을 의미 했다 . 그러나 , 11gr2 에서 해당 파라미터는 direct read 가 가능한 테이블 크기의 최소 임계치로 정 의한다 .

테이블을 풀 스캔 하면 먼저 테이블의 세그먼트 헤더블록을 읽어 HWM 이하 차지하는 블록이 몇 개인지 조회한다 . 블록의 개수가 _small_table_threshold 보다 크면 direct path read 로 처 리되고 , 이보다 작으면 버퍼캐시를 통한 멀티블록 I/O 로 인해 db file scattered read 로 처리 된다 .

11gR2 이전에는 테이블의 크기가 _small_table_threshol d 설정 값의 5 배 또는 그 이상이 되 어야 SDPR 이 가능하였다 . 그러나 , 11gR2 이후에는 _small_table_threshold 값과 테이블의 크기가 거의 비슷해지는 순간 발생한다 . 또한 , 테이블의 전체 블록 수와 비교하여 버퍼캐시에 캐 시 되어 있는 테이블의 블록 또는 더티 블록 이 차지하는 비율에 따라 달라 지기도 한다 . 특정 테 이블이 차지하는 전체 블록 중 50% 이상이 버퍼캐시에 캐시되어 있거나 25% 이상 이 더티 상태 로 캐시 될 경우 , 테이블 의 전체 블록수가 _small_table_threshold 보다 크더라도 SDPR 은 발 생하지 않는다 . 이와 같은 사실은 SDPR 이 옵티마이져에 의한 것이 아닌 실제 세그먼트 블록을 조회하는 런타임 시점에 결정되기 때문이다 . 따라서 , 테이블의 크기 , 버퍼캐시의 크기 , 버퍼캐시 에 캐시 된 테이블의 블록 수를 고려하여 SDPR 여부는 언제든 바뀔 수 있어 좀더 안정적인 성능 을 보장할 수 있다 .

11gR2 이후에는 이와는 다른 한가지 방식이 추가 되었다 . SDP R 의 가능여부를 실제 테이블 의 블록 수를 조회하여 결정하던 것을 테이블의 통계정보에 저장된 블록 수 (dba_tables.blocks) 로 결정하는 것이다 . 이것 은 히든 파라미터 _direct_read_decision_statistics_driven 로 제어 할 수 있으며 기본값은 true 이다 . 통계정보의 blocks 를 참조할 경우 통계정보가 바뀌지 않는 한 테이블의 크기 에 변화가 있더라도 SDPR 결정에 영향을 주지 않는다 . 따라서 , 통계정보를 활 용하여 S DPR 를 좀더 유연하게 적용할 수 있다 . 하지만 , 버퍼캐시에 캐시 된 테이블 의 블록 수 와 상태에 따라서는 여전히 영향을 받는다 .

s erial direct read 등장 배경에는 엑사데이타 와 밀접한 관련이 있다 . 아마도 엑사 테이타의 주 요 기능인 스마트 스캔이 테이블을 direct read 로 풀 스캔 할 때 가능하기 때문일 것이다 . SSD 플래시디스 크 활용이 증가하면서 플래시디스크를 통한 direct read 는 I/O 속도를 한 단계 더 진 화시켰다 . 따라서 , serial direct read 를 적절하게 사용하면 성능에 큰 도움이 될 수 있다 .

(1) 테이블 크기 변화에 따른 serial direct read.

_small_table_threshold 를 기준으의할 것은 오라클 11gR2 의 경우 , 정확한 테스트를 위 해 테이블 통계정보를 생성하지 않거나 _direct_read_decision_statistic s_driven=false 로 하 여 통계정보 blocks 로 SDPR 이 결정되지 않게 해야 한다 . 테스트로 사용되는 테이블은 한 블록 에 한 개의 로우만 저장되도록 생성하고 , 테이블 크기 변화에 따라 SDPR 발생여부를 측정할 수 있는 프로시져를 생성한다 .

_small_table_threshold 를 1000 으로 하고 SDPR_TEST 테이블의 블록수가 800 블록부터 한 블록씩 증가하여 1500 블록 까지 커지도록 프로시저를 실행한 결과 , 테이블의 크기가 976 블록 을 1 블록 초과하는 977 블록이 되는 순간 최초로 direct read 가 발생 한다 . 이것은 _small_table_threshold 가 1000 일 때 테이블 크기의 최소임계 치는 976 블록 임을 의미한다 . 따라서 , HWM 까지의 크기가 97 6 블록 을 초과하는 테이블 을 풀 스캔 한다면 direct path read 로 실행 될 수 있다는 의미이다 .

테스트 세션에 대해 10046 트레이스를 걸어 좀더 자세히 살펴보면 , 제일먼저 db file sequential read 이벤트를 대기하면서 file#=4, bl ock#=17098 블록을 읽는 것을 확인할 수 있다 . 해당블록은 SDPR_TEST 테이블의 세그먼트 헤더 블록으로 이를 통해 테이블이 차지하는 실제 HWM 이하까지의 블록 수를 조회한다 . 테이블의 실제 블록 수를 _small_table_threshold 값과 비교하여 SDPR 을 수행하기로 결정 되면 , 뒤이어 더티 상태의 블록들을 디스크에 반영하는 체크포인트가 이루어지고 enq: KO - fast object checkpoint 이벤트를 대기한다 . 체크포인 트 는 SDPR_TEST 테이블에 한하여 매우 짧은 시간 수행되고 , 체크포인트가 끝나면 direct path read 로 테이블을 풀 스캔 하게 된다 . 실제 버퍼캐시 상태를 x$bh 뷰를 통해 조회하면 , SDPR_TEST 테이블의 전체 블록 중 세그먼트 헤더에 해당하는 블록 한 개만 버퍼캐시에 존재 한다는 것을 알 수 있다 .

DBA_IND_EXPRESSIONS (2) 테이블의 캐시된 블록 수 변화에 따른 serial direct read .

SDPR_TEST 테이블이 977 블록이상 커지더라도 항상 SDPR 로 수행되는 것은 아니다 . SDPR_TEST 테이블이 현재 버퍼캐시에 얼마나 캐시 되어 있는 지에 따라 SDPR 여부는 달라질 수 있다 . 테이블의 전체 블록 중 50% 이상이 버퍼캐시에 이미 캐시 되어 있다면 더 이상 SDPR 은 발생 하지 않는다 . 테이블 전체 블록을 디스크로 부터 직접 읽는 것 보다 캐시 되어있는 블록을 읽는 것 이 더 유리하다고 판단해서이다 . 그러면 , SDPR_TEST 테이블이 버퍼캐시에 어느 정도 캐시 되 었을 때 SDPR 이 발생하지 않는지 테스트를 통해 살펴보자 .

먼저 , 버퍼캐시에 블록을 캐시 하기 위한 프로시저를 생성한다 . SDPR_TEST 테이블 300 블록 을 미리 캐시한 후 , 캐시 된 블록이 한 개씩 증가하도록 프로시저를 실 행 하면 블록이 캐시된 블 록이 몇 개일 때 SDPR 이 멈 추는지 확 인해 보자

프로시저 실행 결과 SDPR_TEST 테이블 전체 블록 중 50% (497/977*100 = 50.8 ) 인 497 개 블록이 캐시 되는 시점에 더 이상 SDPR 은 발생하지 않는다 . SDPR_TEST 테이블을 풀 스캔 할 때 , 버퍼캐시에 이미 캐시 된 블록수가 497 개 이상 존재한다면 , direct path read 가 아닌 버퍼캐시를 경유하는 db file scattered read 로 처리된다 .

여기서 한가지 주의할 점은 테스트에 사용된 테이블은 통계정보가 존재하지 않으므로 프로시저 를 처음 수행할 때 dynamic sampling 이 발생할 수 있다는 것이다 . dynamic sampling 이 수 행되면 테이블을 샘플링 하는 과정에 블록의 일부가 버퍼캐시에 미리 캐시된 채 프로시저가 실 행될 수 있어 497 보다 작은 결과값이 나올 수 있다 . 따라서 , 정확한 결과값을 얻기 위해 프로시 저를 한번 더 실행하거나 dynamic sampling 이 되지 않도록 optimizer_dynamic_sampling 파라미터 레벨을 '0' 으로 변경하여야 한다 .

(3) 캐시된 더티 블록수 변화에 따른 serial direct read 결정

테이블의 블록 중 25% 이상이 더티 블록 상태로 캐시 된 경우에도 SDPR 은 발생하지 않는다 . 앞에서 설명했듯이 di rect read 는 오브젝트 단위의 체크포인트를 동반하므로 테이블의 모든 더 티 블록 들은 체크포인트의 대상이 된다 . 따라서 , 25% 이상 존재하는 더티 블록에 대해 체크포 인트를 적용한 후 디스크로 부터 다시 블록을 읽는 것 보다는 이미 캐시 된 상태의 블록을 읽는 것이 유리하다고 판단할 수 있다 .

그럼 실제 몇 개의 더티 블록이 캐시될 SDPR 이 더 이상 발생하지 않는지 테스트를 통해 살펴보 자 . 더티 블록이 버퍼캐시에 캐시 되도록 프로시저를 생성한 후 어느 시점에 SDPR 이 멈추는지 프로시저를 수행한 결과값을 통해 확인해보자 .

프로시저 수행결과 , SDPR_TEST 테이블 전체블록 중 25% ( 245/977*100 = 25 ) 인 245 개 의 더티 블록이 캐시 된 시점에 더 이상 SDPR 은 발생하지 않는다 . x$bh 뷰를 조회해 보면 SDPR_TEST 테이블의 더티 블록이 정확히 245 개 캐시 되는 시점에 direct path read 가 멈추 고 전체 977 블록 중 나머지 733 개 블록 이 멀티블록 i/o 로 인해 캐시(4) 통계정보 와 serial dir ect read

오라클 11g R 2 이후 에는 옵티마이져 통계정보에 저장된 tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt 를 참조하여 serial direct read 여부가 결정된다 .

통계정보를 적용하여 SDPR 을 결정하기 위해서는 _direct_ read_decision_statistics_driven 히든 파라미터 가 true 여야 한다 . 해당 파라미터는 11gr2 에 새로 추가되었으며 기본값 이 true 이다 . 이것은 11gr2 이후 에는 기본적으로 통계정보를 참조하여 SDPR 을 결정하겠다는 의미 이 다 . 통계정보에 저장된 테이블의 blocks 가 _small_table_threshold 값보다 크면 SDPR 로 처 리되고 작 으면 버퍼캐시를 경유하는 멀티블록 I/O 로 처리 된다 .

통계정보가 존재하는 테이블의 경우 테이블의 전체 블록 수에 큰 변화가 있더라도 SDPR 은 도 중에 변경되지 않는다 . SDPR 의 판단기준이 더 이상 테이블의 실제 블록 수를 통해 적용되지 않 기 때문이다 . 하지만 , 테이블의 통계정보가 새로 갱신되거나 버퍼캐시에 캐시 된 테이블 블록 수와 더티 블록의 수에 변화가 생긴다면 SDPR 여부는 언제든 바뀔 수 있다 . 이것은 앞에서 설명 했듯이 SDPR 결정이 옵티마이져 환경이 아닌 런타임 시점에 결정되기 때문이다 .

테이블 통계정보의 blocks 를 _small_table_threshold 값보다 크게 설정하여 테이블을 풀 스캔 할 때 항상 SDPR 로 실행되게 하고 , 테이블의 크기 , 버퍼캐시 상태에 따라 SDPR 이 어떻게 적 용되는지 테스트를 통해 살펴보자 .

테이블 통계정보의 blocks 를 small_table_threshold 보다 큰 1100 으로 하고 , 테이블 크기가 0 부터 1 블록씩 증가하여 1500 블록이 될 때까지 sdpr_pro 프로시저를 수행하면 0 블록을 초 과하는 시점인 실제 테이블 크기가 1 블록이상이 될 때 SDPR 이 최초로 발생한다 . 통계정보의 blocks 가 _small_table_threshold 값보다 크므로 1 블록을 읽을 때도 SDPR 로 처리 되는 것이 다 .

이와 반대로 테이블 통계정보의 blocks 가 _small_table_threshold 값보다 작으면 테이블이 아 무리 크더라도 SDPR 은 발생하지 않는다 . 따라서 , 데이터의 변화량이 큰 테이블 일수록 현재 상 태에 맞게 통계정보를 생성하는 것이 중요하다 . 이번에는 버퍼캐시에 캐시 된 테이블의 블록 수 와 더터 블록의 수가 변경될 경우 SDPR 은 어떻게 처리 되는지 살펴보자

SDPR_TEST 테이블에 블록 2000 개를 생성하고 통계정보를 수집하면 통계정보가 없을 때 와 약간 다른 결과를 보여준다 . 통계정보가 없을 때는 버퍼캐시에 SDPR_TEST 테이블 블록이 50% 이상 캐시 되거나 더티 블록이 25% 이상 캐시 될 경우 SDPR 이 멈추었다 . 물론 버퍼캐시 및 테 이블의 크기에 따라 약간의 차이가 있지만 그래도 거의 비슷한 수준에서 멈추었다 . 그러나 , 통계 정보를 참조하는 경우 테이블의 캐시 된 블록수가 테이블의 크기와 거의 비슷한 100% 일 때 와 더 티 블록 이 50% 일 때 SDPR 이 멈춘다는 것이다 . 이것은 통계정보가 존재하지 않을 때의 캐 시 비율과 비교할 때 각각 2 배씩 증가한 것으로 보인다 . 이때 , 통계정보의 테이블 blocks 를 임 의로 1100 으로 작게 한다면 어떻게 될까

테스트결과 , 실제 테이블의 크기는 2000 블록 이상이지만 통계정보의 blocks 를 1100 으로 낮 추었더니 1070 개의 블록이 캐시 될 때 또는 497 개의 더티 블록이 캐시 될 때 멈추었다 . 이것 은 테이블 캐시 여부를 실제 테이블의 크기가 아닌 통계정보의 blocks 를 기준으로 결정한다는 것을 알 수 있다 . 테이블 통계정보의 blocks 가 1100 이므로 캐시 된 테이블 블록수가 통계정보 blocks 의 100% 에 가까운 1070 블록 , 더티 블록이 bloc ks 의 50% 이 497 블록에서 SDPR 이 멈추었다 . 통계정보가 없는 테이블을 풀 스캔 할 때는 커서를 실행할 때 마다 테이블의 실제 블 록 수를 세그먼트 헤더블록을 통해 조회 하였다 . 그러나 , 통계정보를 적용할 때는 tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt 값을 커서의 어딘가에 저장하고 실행할 때 마다 저장된 값을 참 조하여 SDPR 여부를 결정한다 . 따라서 , 커서가 invalid 되거나 , 통계정보를 새로 생성하거나 , 캐 시 및 더티 블록 비율이 변할 경우 SDPR 여부도 런타임 시점에 언제든 바뀔 수 있다 .

SDPR_TEST 테이블에 500 블록을 생성한 후 통계정보를 제거한 상태에서 테이블을 풀 스캔 하 면 실제 블록수가 _small_table_threshold 보다 작으므로 SDPR 는 발생하지 않는다 . 통계정 보의 blocks 를 1100 으로 설정하고 다시 동일한 SQL 을 실행할 경우 마찬가지로 SDPR 로 실 행되지 않는다 . 통계정보 blocks 가 1100 이므로 당연히 SDPR 로 처리 되야 하지만 그렇지 않 은 이유는 통계정보를 no_invalidate 로 생성했기 때문이다 . no_invalidate 옵션을 빼면 기본값 이 auto 모드 로 통계정보가 생성되어 일정시간이 지난 후에야 커서가 invalid 된다 . 따라서 , 먼 저 수행된 test - 1 번 커서는 재실행 했을 때도 커서가 아직 invalid 되지 않았으므로 SDPR 로 실 행되지 않지만 , 통계정보 생성 후 처음 실행되는 test - 2 번 커서는 통계정보 blocks 를 적용하여 SDPR 로 실행된다 . 따라서 , tes t - 2 커서는 invalid 되지 않거나 , 버퍼캐시에 테이블 블록의 캐시 및 더티 블록 상태가 바뀌지 않는 이상 계속 SDPR 로 실행된다 . [ 표 - 1] 은 앞에서 설명한 serial direct read 여부를 결정하는 판별기준을 표로 정리한 것이다 .

결론

지금까지 SDPR(serial direct read) 이 어떻게 동작하 , 경우에 따라 다양하고 복잡하게 느껴질 수도 있을 것이다 . 관련 된 파라미터를 변경하지 않 고 통계정보만 적절히 생성하여 오라클 이 알아서 수행하도록 놔두면 될 일이지만 , 뭐든지 부족 하거나 과하면 좋지 않듯이 불필요한 direct read 가 과하게 발생하거나 , 반대로 그렇지 못할 경 우 SDPR 의 동작방식을 알고 있다면 위에서 언급한 파라미터와 통계정보 등을 활용하여 적절한 튜닝도 가능할 것으로 생각한다 .

참고 문헌

http://afatk ulin.blogspot.co.uk , http://blog.tanelpoder.com , http://docs.oracle.com