오라클 성능고도화 원리와 해법 Ⅰ (6장)

I/O 효율화 원리

  - 데이터베이스 튜닝의 3대 핵심 요소

    1) 라이브러리 캐시 최적화

    2) 데이터베이스 Call 최소화

    3) I/O 효율화 및 버퍼캐시 최적화

  - I/O 효율화 튜닝을 잘하려면 인덱스 원리, 조인 원리, 옵티마이저 원리에 대한 이해가 필수적이다

 

블록 단위 I/O

  - 오라클을 포함한 모든 DBMS에서 I/O는 블록 단위로 이루어진다. 블록 단위로 I/O 한다는 것은, 하나의 레코드에서 하나의 컬럼만을 읽으려 할 때도 레코드가 속한 블록 전체를 읽게 됨을 뜻한다

  - Sequential 액세스 : 하나의 블록을 액세스 해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들임 (디스크 I/O 수반하지만 비효율 없음)

  - Random 액세스 : 레코드 하나를 읽으려고 블록을 통째로 액세스 (메모리 버퍼에서 읽더라도 비효율 존재) 

  - 블록 단위 I/O 원리 때문에 select 절에 *  또는 컬럼 하나만 조회해도 서버에서 발생하는 I/O 측면에서의 일량은 같다

  - SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이며, 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수다

  - 블록 단위 I/O 적용

    1) 메모리 버퍼 캐시에서 블록 읽고 쓸 때

    2) 데이터 파일에 저장된 데이터 블록 직접 읽거나 쓸 때

    3) 데이터파일에서 DB 버퍼 캐시로 블록을 적재 할 때 : Single Block Read 또는 Multiblock Read 방식 사용

    4) 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것. DBWR 수행. 성능 향상을 위해 한 번에 여러 블록씩 처리

  - 오라클 딕셔너리 정보를 저장하는 딕셔너리 캐시는 로우 단위로 I/O를 수행한다. 딕셔너리 캐시를 '로우 캐시' 라고 부르는 이유

  - Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말한다

  - Sequential 액세스 성능을 향상 시키려고 오라클 내부적으로 Multiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다

  - Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식을 말한다

  - Random 액세스 성능을 향상시키려고 오라클 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용한다

  - 총 읽은 레코드에서 결과 집합으로 선택되는 비중을 선택도라고 한다. 결국 Sequential 액세스 효율은 선택도에 의해 결정된다

  - I/O 튜닝의 핵심 원리 두가지

    1) Sequential 액세스의 선택도를 높인다

    2) Random 액세스 발생량을 줄인다

  - 클러스터링 팩터가 좋을수록 버퍼 Pinning에 의한 블록 I/O 감소 효과는 더 커진다

 

Memory vs Disk I/O

  - 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 디스크 I/O에 비해 비교할 수 없을 정도로 빠르다. 그래서 모든 DBMS는 버퍼 캐시를 경유해 I/O를 수행한다

  - 물리적인 디스크 I/O가 필요할 때면 서버 프로세스는 I/O 서브시스템에 I/O Call을 발생시키고 잠시 대기하게 되므로 비용이 큰 것이다. 디스크에 발생하는 경합이 심할수록 대기 시간도 길어진다

  - 결국 디스크 I/O를 최소화하고, 대부분 처리를 메모리에서 할 수 있도록 버퍼 캐시 효율성을 높이는 것이 데이터베이스 성능을 좌우하는 열쇠라고 하겠다

  - 버퍼 캐시 히트율 (BCHR)  = 1 - (물리적 블록읽기) / (논리적 블록읽기) X 100

  - 모든 블록 읽기는 버퍼 캐시를 통해 이루어진다. 즉, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고 없을 때 디스크로부터 읽어 들이며, 이때도 디스크로부터 곧바로 읽는 게 아니라 먼저 버퍼 캐시에 적재한 후에 읽는다

  - 다른 대기 이벤트가 없었다면 CPU time과 Elapsed time 간에 발생한 시간차는 대부분 디스크 I/O 때문이라고 이해하면 된다

  - '논리적 블록 읽기'를 '메모리 블록 읽기'로 이해하기보다 '블록 요청 횟수' 또는 '총 읽은 블록 수'로 이해하는 것이 정확하다

  - 논리적인 블록 요청 횟수를 줄이고, 그럼으로써 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리다

  - 특히, 같은 블록을 여러 세션이 동시에 액세스함으로 인해 래치 경합과 버퍼 Lock 경합까지 발생한다면 메모리 I/O 비용이 오히려 디스크 I/O 이상으로 커질 수 있다(대량 데이터 NL 조인 방식으로 작은 테이블 반복 Lookup 하는 경우)

  - 오라클 RAC에서는 인스턴스끼리 네트워크를 통해 캐시 된 블록들을 서로 공유하므로 메모리 I/O 성능에도 네트워크 속도가 지대한 영향을 미치게 되었다. 통계적으로는 논리적 블록 읽기로 계수되지만 그중 일부는 다른 RAC 노드에서 네트워크를 통해 전송받은 것일 수 있기 때문이다

  - 디스크 속도가 문제이든, SAN 문제이든, 아니면 RAC 인터커넥트가 문제이든 I/O 성능에 관한 가장 확실하고 근본적인 해결책은 논리적인 블록 요청 횟수를 최소화하는 것. 방법은 SQL 튜닝에 있다

 

Single Block vs Multiblock I/O

  - 읽고자 하는 블록을 버퍼 캐시에서 찾지 못했을 때, I/O Call을 통해 데이터파일로부터 버퍼 캐시에 적재하는 방식 두 가지

  - Single Block I/O는 한 번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 것을 만한다. 인덱스를 통해 테이블을 액세스 할 때는, 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용한다

  - Multiblock I/O는 I/O Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 것을 말한다

  - 인접한 블록이란, 한 익스텐트 내에 속한 블록들을 말한다. 달리 말하면, Multiblock I/O 방식으로 읽더라도 익스텐트 범위를 넘지 못한다는 뜻이기도 하다

  - 인덱스 스캔할 때는 왜 한 블록씩 읽는 것일까? 인덱스는 블록 간 논리적 순서는 물리적으로 데이터파일에 저장된 순서와 다르다. 인덱스 블록간 논리적 순서란, 인덱스 리프 블록끼리 이중 연결 리스트 구조로 연결된 순서를 말한다. 하나의 블록을 캐싱하려면 다른 블록을 밀어내야 하는데, 이런 현상이 자주 발생한다면 버퍼 캐시 효율을 떨어뜨린다.

  - Index Range Scan 뿐 아니라 Index Full Scan 시에도 논리적인 순서에 따라 Single Block I/O 방식으로 읽는다. 인덱스의 논리적 순서를 무시하고 물리적인 순서에 따라 읽는 스캔 방식이 있는데, 이를 'Index Fast Full Scan'이라고 한다. 이때는 Table Full Scan과 마찬가지로 Multiblock I/O 방식을 사용하며, 한 번에 읽을 수 있는 최대 블록수도 똑같이 db_file_multiblock_read_count 파라미터에 의해 결정된다

  - Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU 쪽에 연결되므로 적재되고 얼마 지나지 않아 버퍼 캐시에서 밀려한다

 

Prefetch

  - 서로 다른 익스텐트에 위치한 블록을 배치 방식으로 미리 적재하는 것

  - Prefetch 된 블록들을 모니터링하는 기능은 CKPT 프로세스가 맡는다

  - 시스템 전반의 디스크 경합을 줄여주기보다는, I/O를 위한 시스템 Call을 줄이고 개별 쿼리의 수행 속도를 향상하는 데 도움

  - Prefetch는 db file parallel read 대기 이벤트로 측정된다

  - 인덱스 Prefetch : Sequential 액세스 성능을 향상하려고 Multiblock I/O와 인덱스 Prefetch 같은 기능을 사용

  - 인덱스 Prefetch 기능이 가장 효과적일 수 있는 상황은 Index Full Scan이 일어날 때다. 부분범위처리 방식으로 중간에 멈추지만 않는다면 모든 인덱스 리프 블록을 읽게 되기 때문이다

  - 테이블 Prefetch : Random 액세스 성능을 향상시키려고 버퍼 Pinning과 테이블 Prefetch 같은 기능 사용

  - 버퍼 Pinning은 Random 액세스에 의한 논리적 블록 요청 횟수를 감소시키고, 테이블 Prefetch는 디스크 I/O에 의한 대기 횟수 감소시킨다. 이 기능은 인덱스 클러스터링 팩터가 나쁠 때 특히 효과 발휘한다

 

Direct Path I/O

  - Direct Path I/O 작동하는 경우

    1) Temp 세그먼트 블록들을 읽고 쓸 때 

    2) 병렬 쿼리로 Full Scan 수행

    3) nocache 옵션을 지정한 LOB 컬럼 읽을 때

    4) direct 옵션을 지정하고 export 수행

    5) parallel DML을 수행

    6) Direct Path Insert를 수행

  - Direct Path Read/Write Temp : 데이터를 정렬할 때는, PGA 메모리에 Sort Area를 이용한다. 정렬할 데이터가 많아 Sort Area가 부족해지면 Temp 테이블스페이스를 이용하는데, Sort Area에 정렬된 데이터를 Temp 테이블스페이스에 쓰고 이를 다시 읽을 때 Direct Path I/O 방식 사용한다

  - Direct Path Read : 병렬로 Direct Path Read를 수행하려면 메모리와 디스크 간 동기화를 먼저 수행함으로써 Dirty 버퍼를 해소해야 한다(정합성 문제)

  - Direct Path Write : append 힌트, 병렬 모드로 insert, direct 옵션 지정하고 SQL*Loader로 데이터 로드, CTAS 수행. Direct Path Insert 시에는 Freelist를 참조하지 않고 테이블 세그먼트 또는 각 파티션 세그먼트의 HWM 바깥 영역에 데이터를 순차적으로 입력한다 (Undo 발생량 최소화, Redo 로그 최소화-nologging)

  - Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock이 걸린다는 사실이다

 

RAC 캐시 퓨전

  - 오라클 RAC 모델은 공유 디스크 방식에 기반을 두면서 인스턴스 간에 버퍼 캐시까지 공유하는 캐시 퓨전 기술로 발전

  - RAC는 글로벌 캐시라는 개념을 사용한다. 즉, 클러스터링 돼 있는 모든 인스턴스 노드의 버퍼 캐시를 하나의 버퍼 캐시로 간주한다

  - 모든 데이터 블록에 대해 마스터 노드가 각각 정해져 있고, 그 노드를 통해 글로벌 캐시에 캐싱돼 있는 블록의 상태와 Lock 정보 관리한다

  - 읽고자 하는 블록이 로컬 캐시에 없을 때 마스터 노드에 전송 요청을 하고, 마스터 노드는 해당 클록을 캐싱하고 있는 노드에 메시지를 보내 그 블록을 요청했는 노드에 전송하도록 지시하는 방식이다. 만약 어드 노드에도 캐싱돼 있지 않다면 직접 디스크에서 읽도록 권한을 부여한다

  - RAC 환경에서의 Current 블록은 Shared 모드 Current와 Exclusive 모드 Current로 나뉜다. SCur 상태의 블록은 동시에 여러 노드에 캐싱될 수 있지만 XCur 상태의 블록은 단 하나의 노드에만 존재할 수 있다

  - 한 노드가 XCur 모드로 업그레이드를 요청하는 순간 다른 노드에 캐싱돼 있던 SCur 블록들은 모두 Null 모드로 다운그레이드된다. 더는 쓸 수 없는 PI(Past Image) 블록이 되는 것이다

  - RAC 전송 메커니즘 5가지

    1) 전송 없는 읽기 : Read with No Transfer

    2) 읽기/읽기 전송 : Read to Read Transfer

    3) 읽기/쓰기 전송 : Read to Write Transfer

    4) 쓰기/쓰기 전송 : Write to Write Transfer : 다른 인스턴스가 갱신 중인 블록을 읽고자 할 때 로우 Lock이 해제될 때까지 기다리지 않고 로우 Lock이 설정된 채 블록을 주고받는다는 것은 매우 중요한 사실

    5) 쓰기/읽기 전송 : Write to Read Transfer : 처음에는 CR Copy만을 전송하다가 일정 횟수 이상 요청이 반복적으로 들어오면 그때 Current 블록을 보내준다

 

Result 캐시

  - 한번 수행한 쿼리 또는 PL/SQL 함수의 결괏값을 Result 캐시에 저장해두는 기능을 11g 버전부터 제공

  - DML이 거의 발생하지 않는 테이블을 참조하면서, 반복 수행 요청이 많은 쿼리에 사용하면 I/O 발생량 현격히 감소

  - Result 캐시 메모리 두 가지 캐시 영역

    1) SQL Query Result 캐시 : SQL 쿼리 결과 저장

    2) PL/SQL 함수 Result 캐시 : PL/SQL 함수 결과값을 저장

  - Result 캐시는 SGA의 Shared Pool에 저장된다. SQA 영역이므로 모든 세션에서 공유할 수 있고, 인스턴스를 재기동하면 당연히 초기화된다

  - 오라클 서버 프로세스는 Result 캐시 메모리를 먼저 찾아보고, 캐싱돼 있다면 그것을 가져다가 결과 집합을 리턴

  - Reuslt 캐시 사용 사례

    1) 작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할 때

    2) 읽기 전용의 작은 테이블을 반복적으로 읽어야 할 때

    3) 읽기 전용 코드 테이블을 읽어 코드 명칭 반환하는 함수

  - Result 캐시 사용 자제

    1) 쿼리가 참조하는 테이블에 DML이 자주 발생할 때

    2) 함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값의 종류가 많고, 그 값들이 골고루 입력될 때

 

I/O 효율화 원리 

  - 필요한 최소 블록만 읽도록 쿼리를 작성한다

  - 최적의 옵티마이징 팩터를 제공한다 (first_rows, all_rows)

  - 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다

  - 시스템 통계 항목

    1) CPU 속도

    2) 평균적인 Single Block 읽기 속도

    3) 평균적인 Multiblock 읽기 속도

    4) 평균적인 Multiblock I/O 개수