11주차 개별 정리 자료 - 김태민

[김태민]

06. I/O 효율화 원리

< 데이터베이스 성능 튜닝의 3대 핵심 요소 >

- 라이브러리 캐시 최적화

- 데이터베이스 Call 최소화

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

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

01. 블록 단위 I/O

- 오라클을 포함한 모든 DBMS에서 I/O는 블록 단위로 이루어진다. 블록 단위로 I/O 한다는 것은, 하나의 레코드에서 하나의 컬럼을

   읽으려 할 때도 레코드가 속한 블록 전체를 읽게 됨을 뜻한다.

- 하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들인다면 설령 무거운 디스크 I/O를 수반하더라도

   비효율은 없다. - 'Sequential 액세스'

- 하지만 레코드 하나를 읽으려고 블록을 통째로 액세스한다면 메모리 버퍼에서 읽더라도 비효울이 존재한다. - 'Random 액세스'

쿼리1) SELECT ENAME FROM EMP WHERE SAL >= 2000;

쿼리2) SELECT * FROM EMP WHERE SAL >= 2000;

--> 블록 단위 I/O 원리 때문에 위 두 쿼리를 처리할 때 서버에서 발생하는 I/O 측면에서의 일량은 같다.(실행이 같을 때)

SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이며, 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야할 블록 개수다. 옵티마이저가 인덱스를 이용해 테이블을 액세스할지 아니면 Full Table Scan 할지를 결정하는 데 있어 가장 중요한 판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 하는 블록 개수다.

< 블록 단위 I/O는 버퍼 캐시와 데이터파일 I/O 모두에 적용된다 >

- 메모리 버퍼 캐시에서 블록을 읽고 쓸 때

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

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

- 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : Dirty버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR

  프로세스에 의해 수행된다. 성능 향상을 위해 한 번에 여러 블록씩 처리한다.

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

오라클에서 허용하는 블록 크기는 2K, 4K, 8K, 16K, 32K, 64K이다.

데이터베이스를 생성할 때 표준 블록 크기를 지정하며, 다른 크기의 블록을 동시에 사용하려면 각각 별도의 테이블스페이스와 버퍼

Pool을 구성해 주어야 한다.

(1) Sequential vs. Random 액세스

-> Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말한다. 인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결돼 있고, 이 포인터를 따라 스캔하는 것은 Sequential 액세스 방식이다.

위 그림에서 5번이 여기에 해당한다. 테이블 레코드 간에는 포인터로 연결되지 않지만 테이블을 스캔할 때는 물리적으로 저장된

순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다. Sequential 액세스 성능을 향상시키려고 오라클 내부적으로 Multiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다.

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

위 그림에서 1,2,3,4,6이 여기에 해당한다. 1,2,3번 액세스는 인덱스 깊이에 따라 1~3 블록 정도 읽는 것이므로 대개 성능에 영향을

미치지 않고, 주로 4,6번 액세스가 성능 저하를 일으킨다.

NL조인에서 Inner테이블 액세스를 위해 사용되는 인덱스에서는 1,2,3번까지도 성능에 지대한 영향을 미칠 수 있다. Random 액세스

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

Sequential 액세스도 100개를 읽었는데 그 중 99개를 버리고 1개만 취한다면 Random 액세스보다 나을 게 없다.

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

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

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

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

(2) Sequential 액세스 선택도 높이기

-> 34639개 레코드를 선택하려고 71735개 레코드를 스캔 했으므로 선택도는 48%다.

Full Scan 선택도가 이 정도면 나쁘지 않아 보인다. 읽은 블록 수는 1027개다

-> 위 쿼리는 1개 레코드를 선택하려고 71735개 레코드를 스캔했으므로 0.002%정도다. 선택도가 매우 낮으므로 테이블 Full Scan

     비효율이 크다. 이처럼 테이블을 스캔하면서 읽은 레코드 중 대부분 필터링되고 일부만 선택된다면 아래처럼 인덱스를

    이용하는 게 효과적이다.

-> 위 쿼리에서 참조하는 컬럼이 모두 인덱스에 있으므로 인덱스만 스캔하고 결과를 낼 수 있다.

     인덱스를 스캔하면서 103개 블록을 읽고 1개 레코드를 얻었다.

-> LIKE로 하면 검색할 레코드 수가 많아 진다. 그 다음 OBJECT_NAME을 검색하니 오래 걸린다.(앞에가 정조건이 나오는 것이

    좋다.)

- 1개 레코드를 얻으려고 실제 스캔한 레코드의 수를 세는 방법

-> 인덱스 선두 컬럼이 조건이 아니므로 OWNER LIKE 'SYS%' 조건에 해당하는 로우가 읽어야 할 대상 범위지만, 다행히 스캔 시작

     지점은 OWNER = 'SYS' 조건과 OBJECT_NAME >= 'ALL_OBJECTS' 조건에 의해 결정된다.

-> 1/19352*100 = 0.005%의 선택도, 테이블뿐만 아니라 인덱스를 Sequential 액세스 방식으로 스캔할 때도 비효율이 있는 것을 알

    수 있다. 인덱스는 테이블과 달리 정렬된 순서를 유지하므로 일정 범위를 읽다가 멈출 수 있다는 점만 다르다.

- 인덱스 스캔의 효율은 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.

-> Fetch 2번인 이유 : array size가 10이라고 하면 먼저 fetch 한번 하여 array에 넣고 뒤에 무엇이 있는지 모르니깐 fetch 한번 더

    했기 때문에

-> 세 개의 CR 블록 읽기가 발생했다. 인덱스 루트 블록과 리프 블록만 읽었기 때문이다.

-> 한 건을 얻으려고 스캔한 건수도 한 건( 정확히 얘기하면, ONE-PLUS 스캔까지 두 건)일 것이다.

-- 책에서는 총 2건 나왔다 --> 선택도가 100%

(3) Random 액세스 발생량 줄이기

- 인덱스에 속하지 않는 컬럼을 참조하도록 쿼리를 변경함으로써 테이블 액세스가 발생하도록 할 것이다.

 

-> OBJECT_ID 컬럼이 인덱스에 없기 때문에 테이블로 가서 랜덤 액세스를 한다.

-> 왼쪽에 보이는 Rows는 각 수행 단계에서 출력(Flow-Out)된 로우 수를 의미한다.

-> 인덱스로부터 30731건을 출력했으므로 테이블을 그 횟수만큼 방문하게 된다. 그 과정에서 1022(1090-68)개 블록을 Random 액세스했다. 내부적으로 블록을 30731번 방문했지만 Random 액세스 횟수가 1022번에 머무는 것은 버퍼 Pinning 효과 때문이다.

※ 버퍼 Pinning 효과

- 오라클은 과다한 체인 탐색에 의한 래치 경합을 줄이기 위해 Buffer Pinning 기법을 사용한다.

- 특정 세션의 동일 SQL 콜 (Call) 내에서 2번 이상 스캔된 버퍼는 Pinned 상태로 변환 된다.

- Pinned 상태란 래치를 획득하지 않고도 곧바로 버퍼영역에 갈 수 있도록 버퍼헤더의 위치를 저장하고 있다는 의미이다.

- Pinned 상태의 버퍼는 버퍼 캐시에서 밀려나지 않기 때문에 저장된 위치를 이용해 래치 획득없이 곧바로 찾아갈 수 있다.

- Pinned 상태의 버퍼를 탐색할 경우에는 session logical reads 지표값 대신 buffer is pinned count 지표 값이 증가한다.

- Pinned 상태의 버퍼라고 하더라도 메모리 I/O 자체는 발생하며, 대신 래치 획득과 해시체인탐색과 같은 부가적인 작업이 줄어든다.

--> 한번의 Fetch Call 내에서 두번 이상 Access되는 Buffer에 대해 Memory에서 Flush되지 않도록 Pin(buffer pin)을 걸어서

      cache buffer chains latch의 획득없이 Buffer를 읽는 것을 말한다.

-> 위에서 최종 한 건을 선택하려고 너무 많은 Random 액세스가 발생했다. object_name을 필터링하려고 테이블을 많이 방문한

    것이므로 인덱스 액세스 단계에서 필터링할 수 잇도록 object_name을 추가해보자.

- INDEX에 object_name추가한 트레이스

-> 인덱스로부터 1건을 출력했으므로 테이블을 1번 방문한다. 실제 발생한 테이블 Random 액세스도 1(5-4)번이다. 같은 쿼리를

    수행했는데 인덱스 구성이 바뀌자 테이블 Random 액세스가 대폭 감소하였다.

02. Memory vs. Disk I/O 

(1) I/O 효율화 튜닝의 중요성

- 디스크를 경유한 입출력은 물리적으로 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느리다. 반면, 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 디스크 I/O에 비해 비교할 수 없을 정도로 빠르다. 

DB 버퍼 캐시를 경유한다는 것은, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 찾지 못할 때만 디스크에서 읽는 것을 말한다.

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

- 유한한 메모리 자원을 좀 더 효율적으로 사용해야 하므로 자주 액세스하는 블록들이 캐시에 더 오래 남아 있도록 LRU 알고리즘을

   사용한다. 결국 디스크 I/O를 최소화하고, 대부분 처리를 메모리에서 할 수 있도록 버퍼 캐시 효율성을 높이는 것이 데이터베이스

   성능을 좌우하는 열쇠라고 하겠다.

(2) 버퍼 캐시 히트율 (Buffer Cache Hit Ratio)

- 버퍼 캐시 효율을 측정하는 지표

- 전체 읽은 블록 중에서 얼만큼을 메모리 버퍼 캐시에서 찾았는지를 나타내는 것

<공식>

BCHR = ( 캐시에서 곧바로 찾은 블록 수/ 총 읽은 블록 수 ) * 100

         = ( (논리적 블록읽기 - 물리적 블록읽기) / 논리적 블록읽기) * 100

   = ( 1 - (물리적 블록읽기) / (논리적 블록읽기) ) * 100

- '논리적 블록읽기' = '총 읽은 블록 수'

- '캐시에서 곧바로 찾은 블록 수' = '논리적 블록읽기' - '물리적 블록읽기'

- Direct Path Read 방식으로 읽는 경우를 제외하면 모든 블록 읽기는 버퍼 캐시를 통해 이루어진다. 즉, 읽고자 하는 블록을 먼저

  버퍼 캐시에서 찾아보고 없을 때 디스크로부터 읽어들이며, 이때도 디스크로부터 곧바로 읽는 게 아니라 먼저 버퍼 캐시에 적재한

  후에 읽는다.

  ( Direct Path Read : Disk Sort 과정에서 Sort Runs로부터 블록을 읽거나 병렬 쿼리로 Full Scan을 수행할 때 Direct Path Read

    방식이 사용된다. )

- 따라서 SQL을 수행하는 동안 캐시에서 읽은 총 블록 수를 '논리적 블록 읽기'라고 한다. 그리고 캐시에서 곧바로 찾은 블록 수는

  '총 읽은 블록수'에서 '물리적 블록읽기'를 차감해서 구한다.

-> current 블록 : DML 발생시

-> query 블록 : SELECT

-> 위에서 Disk 항목이 '물리적 블록읽기'에 해당한다. '논리적 블록읽기'는 Query와 Current 항목을 더해서 구하며, Direct Path Read 방식으로 읽은 블록이 없다면 이 두 값을 더한 것이 '총 읽은 블록 수'가 된다. 총 1,351,677개 블록을 읽었는데,  그 중 601,458개는 디스크에서 버퍼캐시로 적재한 후에 읽었다. 따라서 위 샘플에서 BCHR는 56%다.

BCHR = (1 - (Disk / (Query + Current))) * 100

   = ( 1 - (601,458 / (1,351,677 + 0))) * 100

   = 55.5%

논리적으로 100개 블록 읽기를 요청하면 56개는 메모리에서 찾고, 44개는 메모리에 없어 디스크 I/O를 발생시켰다는 의미가 된다.

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

BCHR를 구하는 공식을 통해 알 수있는 것처럼 '논리적 블록 읽기'를 '메모리 블록 읽기'로 이해하기 보다 '블록 요청횟수' 또는

'총 읽은 블록 수'로 이해하는 것이 정확하다.

<아래는 위 Call 통계의 원본 SQL을 튜닝한 결과 >

-> 튜닝을 통해 논리적인 블록 요청 횟수가 27번만 발생하니깐 발생하니까 메모리에서 읽은 블록 수도 27개에 그치고, 설령 BCHR가

0%여도 27개를 모두 디스크에서 읽더라도 쿼리는 항상 빠르게 수행될 것이다.

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

같은 블록을 반복적으로 액세스하는 형태의 애플리케이션이라면 논리적인 I/O 요청이 비효율적으로 많이 발생하는데도 BCHR는 매우 높게 나타난다. 작은 테이블을 자주 액세스하면 모든 블록이 메모리에서 찾아지므로 BCHR는 높겠지만 블록을 찾는 과정에서 래치를 얻어야 하므로 의외로 큰 비용을 수반한다.

특히, 같은 블록을 여러 세션이 동시에 액세스함으로 인해 래치 경합과 버퍼 Lock 경합까지 발생한다면 메모리 I/O 비용이 오히려 디스크 I/O이상으로 커질 수 있다. 따라서 BCHR가 100%라고 하더라도 논리적으로 읽어야 할 블록 수의 절대량이

많다면 반드시 SQL 튜닝을 실시해서 논리적인 블록 읽기를 최소화해야 한다.

- 대량의 데이터를 기준으로 NL 조인 방식을 사용해 작은 테이블을 반복적으로 LookUp하는 경우가 대표적이다.

(3) 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향

- 메모리 I/O, 디스크 I/O 발생량뿐 아니라 최근에는 네트워크 속도가 I/O 성능에 지대한 영향을 미치고 있다.

- 현재는 데이터베이스 서버와 스토리지 간에 NAS 서버나 SAN을 통해 연결되는 아키텍처를 사용한다.

우선, 서버와 스토리지 간에 더 크고 많은 파이프를 연결하려고 시도한다.  그리고 컬럼 기반(또는 컬럼 스토어) 데이터베이스가 DW

시장에서 각광을 받고 있다고 했는데, 오라클은 현재의 I/O 방식을 고수하는 대신 네트워크를 통한 데이터 전송량을 최소화하는 쪽으로 기술을 발전시켜 나가고 있다.

최근 오라클은, 데이터베이스 서버와는 독립적으로 스토리지 자체에 CPU와 RAM을 탑재하는 하드웨어적인 솔루션을 도입했다. 그럼으로써 쿼리 프로세싱 중 일부를 스토리지가 처리하게끔 하는 지능형 스토리지 서버 개념이다.

-> 이 기술의 핵심은, 스마트 스캔이라는 기술을 이용해 쿼리에서 필요로 하는 컬럼과 로우만을 네트워크를 통해 전송하도록 하는 데

     에 있다. 이 기술을 이용하면 스토리지에서 데이터베이스 서버로 보내지는 데이터양을 획기적으로 줄여주고, 서버의 CPU부하를

     감소시킨다.

CPU, RAM, 디스크를 일체형으로 개발한 MPP방식의 어플라이언스 제품

- 데이터를 여러 개 디스크에 분산 저장하고, 읽을 때도 동시에 '독립적으로' 읽어 병렬 I/O효과를 극대화한 제품들이다.

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

것일 수 있기 때문이다.

- 디스크 속도가 문제이든, SAN이 문제이든, 아니면 RAC 인터커넥트가 문제이든 I/O 성는에 관한 가장 확실하고 근본적인 해결책은

   논리적인 블록 요청 횟수를 최소화하는 것임을 잊지말자.

-> 네트워크 I/O 효율보다는 논리적인 블록 I/O를 줄이는게 좋다.

03. Single Block vs. Multiblock I/O

-> 위 Call 통계를 보면, 버퍼 캐시에서 69개 블록을 읽으면서 그 중 64개는 디스크에서 읽었다. 버퍼 캐시 히트율은 7.24%다.

     디스크에서 읽은 블록 수가 64개라고 I/O Call까지 64번 발생했음을 의미하지는 않는다. 64번일 수도 있고 그보다 작을 수 있다.

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

- Single Block I/O

- Multiblock I/O

Single Block I/O : 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 것.

인덱스를 통해 테이블을 액세스할 때는, 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용한다.

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

오라클 블록 사이즈가 얼마건 간에 OS 단에서는 보통 1MB 단위로 I/O를 수행한다.

테이블 Full scan처럼 물리적으로 저장된 순서에 따라 읽을 때는 그릇(1MB)이 허용하는 범위 내에서 인접한 블록들을 같이 읽는 것이 유리하다. '인접한 블록'이란, 한 익스텐트 내에 속한 블록들을 말한다.

-> 익스텐트 : 데이터 블록의 집합

-> 세그먼트 : 익스텐트의 집합

Multiblock I/O 단위는 db_file_multiblock_read_count 파라미터에 의해 결정된다.

이 파라미터가 16이면 한 번에 최대 16개 블록을 버퍼 캐시에 적재한다.

대개 OS 레벨에서 I/O 단위가 1MB이므로 db_block_size가 8,192바이트 일 떄는 최대 설정할 수 있는 값은 128이 된다. 이 파라미터를 128 이상으로 설정하더라도 OS가 허용하는 I/O단위가 1MB면 1MB 씩만 읽는다.

인덱스를 스캔할 때는 한 블록씩 읽는 이유

- 인덱스 블록간 논리적 순서는 물리적으로 데이터파일에 저장된 순서와 다르다.

- 인덱스 블록간 논리적 순서란, 인덱스 리프 블록끼리 이중 연결 리스트 구조로 연결된 순서를 말한다.

- 하나의 블록을 캐싱하려면 다른 블록을 밀어내야 하는데, 이런 현상이 자주 발생한다면 버퍼 캐시 효율만 떨어뜨린다. 따라서 인덱스 스캔 시에는 Single Block 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 파라미터에 의해 결정된다.

- db file sequential read 대기 이벤트 : Single Block I/O 방식으로 I/O를 요청할 때 발생

- db file scttered read 대기 이벤트 : Multiblock I/O 방식으로 I/O를 요청할 때 발생

-

- 대량의 데이터를 Multiblock I/O 방식으로 읽을 때 Single Block I/O보다 성능상 유리한 것은 I/O Call 발생횟수를 그만큼 줄여주기

   때문이다.

SELECT /*+ INDEX(T8) */ COUNT(*)
FROM T8
WHERE OBJECT_ID > 0;

-> 논리적으로 152개 블록을 읽는 동안 143개의 디스크 블록을 읽었다.

-> 이벤트 발생 현황을 보면 DB FILE SEQUENTIAL READ 대기이벤트가 10번 발생헀다.

     즉, 143개 인덱스 블록을 읽으면서 143번의 I/O Call이 발생한 것이다.

<리눅스에서>

-> DB_BLOCK_SIZE는 8192이고, Multiblock I/O 단위는 128이다.

- 앞에서와 같은 양의 인덱스 블록을 Multiblock I/O 방식으로 읽도록 하기 위해 인덱스를 index fast full scan 방식으로 읽도록 유도해 보자. ==> index_ffs 힌트를 사용하면 된다.

143개 블록을 읽었을 때 2(143/128) 번의 I/O call이 발생할 것으로 예상된다.

< Multiblock I/O방식으로 읽도록 index fast full scan방식으로 읽도록 유도 >

--> 15번이 발생한 이유 : 익스텐트 크기 때문에

- 이번에는 137개 블록을 디스크에서 읽었는데, I/O call이 15번이 발생했다.

- Single Block I/O할 때보다 19->15로 줄었다.

<테이블 스페이스에 할당된 익스텐트 크기>

-> 모든 익스텐트가 8개 블록으로 구성돼 있는 것이 원인. (Multiblock I/O 방식으로 읽더라도 익스텐트 범위를 넘지 못함.)

예를 들어, 모든 익스텐트에 20개 블록이 있고 db_file_multiblock_read_count가 8이면, 익스텐트마다 8,8,4개씩 세번에 걸쳐 읽는다.

- 10g부터는 Index Range Scan 또는 Index Full Scan일 때도 Multiblock I/O 방식으로 읽는 경우가 있는데, 위처럼 테이블 액세스 없이 인덱스만 읽고 처리할 때이다.

인덱스를 스캔하면서 테이블을 Random 액세스할 때는 9i 이전과 동일하게 테이블과 인덱스 블록을 모두 Single I/O 방식으로 읽는다.

- Single block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU 쪽으로 연결되므로 한번 적재되면 버퍼 캐시에 비교적 오래 머문다.

반면, Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU 쪽에 연결되므로 적재되고 얼마 지나지 않아 버퍼 캐시에서 밀려난다. 따라서 대량의 데이터를 Full Scan 했다고 해서 사용빈도가 높은 블록들이 버퍼 캐시에서 모두 밀려날 것을 우려하지 않아도 된다.

04. Prefetch

- 본 절에서 테이블 Prefetch와 인덱스 Prefetch를 지칭한다.

- 오라클을 포함한 모든 DBMS는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 Prefetch 기능을 제공.

   ( 한 번의 I/O Call을 통해 다량의 블록을 올려 성능향상에 도움을 주기 위해 - Multiblock I/O)

-- 테이블 Prefetch, 인덱스 Prefetch와 다른 점은 한 익스텐트에 속한 인접한 블록들을 Prefetch 한다는 점이다.

- Prefetch는 한번에 여러 개 Single Block I/O를 동시 수행하는 것을 말한다.

지금 설명하는 테이블 Prefetch와 인덱스 Prefetch는 인접하지 않은 블록, 즉 서로 다른 익스텐트에 위치한 블록을 배치방식으로

미리 적재하는 것을 말한다. I/O Call을 병렬 방식으로 동시에 여러 개 수행하는 것이므로 읽어야 할 블록들이 서로 다른 디스크 드라이브에 위치한다면 Prefetch에 의한 성능 향상은 더욱 배가 된다.

--> 이 기능은 곧 읽을 가능성이 높은 블록들을 미리 적재했을 때만 성능 향상에  도움을 준다.

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

-> 앞으로 읽어야 할 블록들을 미리 적재하는 기능이므로 시스템 전반의 디스크 경합을 줄여주기보다, I/O를 위한 시스템 Call을

줄이고 개별 쿼리의 수행 속도를 향상시키는데 주로 도움을 준다. 데이터 블록을 읽는 도중에 물리적인 디스크 I/O가 필요할 떄면 서버 프로세스는 I/O 서브시스템에 I/O Call을 발생시키고 잠시 대기 상태에 빠진다.

Prefetch 측정 : db file parallel read 대기 이벤트

(1) 인덱스 Prefetch

- 브랜치 블록에서 앞으로 읽게 될 리프 블록 주소를 미리 얻을 수 있으므로 I/O Call이 필요한 시점에 미리 캐싱해 둔다.

-> 2번 브랜치 블록을 읽고 5번 리프 블록을 읽으려는 시점에 5번 블록이 버퍼 캐시에 없으면 물리적인 디스크 I/O가 필요하다

이 떄 6번과 7번 블록까지 같이 적재해 놓는다면, 4번 리프 블록 스캔이 진행하는 동안 디스크 I/O때문에 대기할 가능성을 줄일 수 있다.

- Sequential 액세스 성능을 향상시키려고 Multiblock I/O와 인덱스 Prefetch 같은 기능을 사용한다.

인덱스 Prefetch 기능이 가장 효과적일 수 있는 상황은 Index Full Scan이 일어날 때다. 부분범위처리 방식으로 중간에 멈추지만

않는다면 모든 인덱스 리프 블록을 읽게 되기 때문이다. 그런데 Index Full Scan시 Prefetch 방식으로 I/O하려면 리프 블록 위쪽에 있는 브랜치 블록들을 추가로 읽어야 하기 떄문에 Prefetch 하지 않을 때보다 I/O가 약간 더 발생한다.

- _index_prefetch_factor : 기본 값은 100이며, 이 값을 더 작게 설정할수록 옵티마이저는 인덱스 Prefetch를 더 선호하게 된다.

- _db_file_noncontig_mblock_read_count : 한 번에 최대 몇 개 블록을 Prefetch 할지를 지정한다. 1로 지정하면 Prefetch 기능이

   정지된다.

(2) 테이블 Prefetch ( 테이블 Lookup Prefetch' 또는 '데이터 블록 Prefetch')

- 인덱스를 경유해 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야 하는 상황이 발생할 수 있는데, 그때 다른

   테이블 블록까지 미리 적재해 두는 기능이다.

리프 블록에 있는 인덱스 레코드는 논리적인 순서를 따라 읽는다.

-> 5번 인덱스 리프 블록을 읽고 12번 테이블 블록을 읽으려는 시점에 12번 블록이 버퍼 캐시에 없으면 물리적인 디스크 I/O가 필요.

이 때 13, 15, 18번 블록까지 같이 적재해 놓는다면 5,6,7번 액세스 시에 디스크 I/O 때문에 대기하지 않아도 된다.

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

버퍼 Pinning은 Random 액세스에 의한 논리적 블록 요청 횟수를 감소시키고, 테이블 Prefetch는 디스크 I/O에 의한 대기 횟수를 감소 시킨다.

클러스터링 팩터가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하여 이 기능은 인덱스 클러스터링 팩터가 나쁠 때

효과적이다.

< 테이블 Prefetch를 제어하는 파라미터 >

- _table_lookup_prefetch_size : 기본 값은 40

- _table_lookup_prefetch_thresh : 기본 값은 2

- _multi_join_key_table_lookup : 기본 값은 TRUE

DB2 - 그림 6-5에서 4~11번까지 리프 블록을 먼저 스캔하고 거기서 얻은 결과 집합을 rowid 순으로 정렬한 후에 테이블을 액세스

앞으로 액세스하게 될 테이블 블록 순으로 정렬한 상태에서 액세스하기 때문에 각 테이블 블록을 한 번씩만 액세스하게 된다.

05. Direct Path I/O

- 일반적인 블록 I/O는 DB 버퍼 캐시를 경유한다. 즉, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 찾지 못할 떄만 디스크에서 읽는다.

- 시스템 전반의 I/O 성능을 향상시키려고 버퍼 캐시를 이용하지만 개별 프로세스 입장에서 대용량 데이터를 읽고 쓸 때 건건이 버퍼

캐시를 경유한다면 오히려 성능이 나빠질 수 있다. 재사용 가능성이 없는 임시 세그먼트 블록들을 읽고 쓸 때도 버퍼 캐시를 경유 하지 않는 것이 유리하다.

- 이럴 때 Direct Path I/O기능 제공

<Direct Path I/O가 작동하는 경우>

- Temp 세그먼트 블록들을 읽고 쓸 떄

- 병렬 쿼리로 Full Scan을 수행할 때

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

- direct 옵션을 지정하고 export를 수행할 떄

- parallel DML을 수행할 떄

- Direct Path Insert를 수행할 때

(1) Direct Path Read/Write Temp

- 데이터를 정렬할 때는, PGA 메모리에 할당되는 Sort Area를 이용한다. 정렬할 데이터가 많아 Sort Area가 부족해지면 Temp 테이블 스페이스를 이용하는데, Sort Area에 정렬된 데이터를 Temp 테이블스페이스에 쓰고 이를 다시 읽을 때 Direct Path I/O 방식을 사용한다. 이 과정에서 I/O Call이 완료될 때가지 대기가 발생하는데, direct path write temp와 direct path read temp 이벤트로 측정된다.

(2) Direct Path Read

- 병렬 쿼리로 Full Scan을 수행할 때도 Direct Path Read 방식을 사용한다. 병렬도(DOP)를 2로 주고 병렬쿼리를 수행하면 쿼리

수행 속도가 2배만 빨라지는 게 아니라 그 이상의 빠른 수행속도를 보이는 이유이다. 따라서 대용량 데이터를 읽을 때는 Full Scan과

병렬 옵션을 적절히 사용함으로써 시스템 리소스를 적게 사용하도록 하는 것이 좋다.  Direct Path Read 과정에서 읽기 call이 완료

될 때까지 대기가 발생하는 데, direct path read 이벤트로 측정된다.

- 버퍼 캐시에만 기록된 변경사항이 아직 데이터파일에 기록되지 않은 상태에서 데이터 파일을 직접 읽으면 정합성에 문제가 생긴다.

   따라서 병렬로 Direct Path Read를 수행하려면 메모리와 디스크간 동기화를 먼저 수행함으로써 DIrty 버퍼를 해소해야 한다.

-- Adaptive Direct Path Reads

사실 병렬 쿼리가 아니더라도 오라클 8.1.5이후 버전부터는 Hidden 파라미터 _serial_direct_read를 true로 변경해 Direct Path Read

방식으로 읽도록 할 수 있다. 게다가 11g부터는 이 파라미터가 false인 상태에서도 Serial Direct Path REad방식이 작동할 수 있으며,

이미 캐싱돼 있는 블록 개수, 디스크에 기록해야 할 Dirty 블록 개수(세그먼트 단위 체크포인트 일량을 결정) 등에 따라 오라클이 결정한다.

(3) Direct Path Write

- 병렬로 DML을 수행하거나 Direct Path Insert 방식으로 데이터를 insert 할 때 사용된다. 이 과정에서 I/O Call이 발생할 떄마다

direct path write 이벤트가 나타난다.

< Direct Path Insert방식으로 데이터를 입력하는 방법 >

- insert...select 문장에 /*+append*/힌트 사용

- 병렬 모드로 insert

- direct 옵션을 지정하고 SQL*Loader로 데이터를 로드

- CTAS(create table ... as select) 문장을 수행

- 일반적인(conventional) insert 시에는 Freelist를 통해 데이터를 삽입할 블록을 할당받는다.

  Freelist를 조회하면서 Random 액세스 방식으로 버퍼 캐시에서 해당 블록을 찾고, 없으면 데이터파일에서 읽어 캐시에 적재한 후에

  데이터를 삽입하므로 대량의 데이터를 insert 할 때 매우 느리다.

- Direct Path Insert 시에는 Freelist를 참조하지 않고 테이블 세그먼트 또는 각 파티션 세그먼트의 HWM(High-Water Mark) 바깥 영역에 데이터를 순차적으로 입력한다. Freelist로 부터 블록을 할당받는 작업이 생랼될 뿐 아니라 insert할 블록을 버퍼 캐시에 적재하지 않고 데이터파일에 직접 insert 하므로 일반적인 insert와는 비교할 수 없을 정도로 빠르다.

HWM 바깥 영역에 데이터를 입력하므로 Undo 발생량도 최소화된다

( HWM 뒤쪽에 입력한 데이터는 커밋하기 전까지 다른 세션에 읽히지 않으므로 Undo 데이터를 제공하지 않아도 되고, 롤백할 때는

  할당된 익스텐트에 대한 딕셔너리 정보만 롤백하면 되기 때문 )

- Direct Path Insert에서는 Redo 로그까지 최소화(데이터 딕셔너리 변경사항만 로깅)하도록 옵션을 줄 수도 있어 더 빠른 insert가 가능하다. 이 기능을 활성화하려면 테이블 속성을 nologging으로 바꿔주면 된다.

ALTER TABLE T NOLOGGING;  

참고로, Direct Path Insert가 아닌 일반 insert문을 로깅하지 않도록 하는 방법은 없다.

주의할 것은, Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock이 걸린다는 사실이다. 아래처럼 병렬 방식으로 DML을 수행할 때도 마찬가지다.

ALTER SESSION ENABLE PARALLEL DML;

DELETE /*+ PARALLED(B 4) */ FROM BIG_TABLE B; -> Exclusive 모드 TM Lock

-> 성능은 비교할 수 없을 정도로 빨라지겠지만 Exclusive 모드 테이블 Lock을 사용하면 해당 테이블에 다른 트랜잭션이 DML을 수행하지 못하도록 막는다. 따라서 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.