SQL) I/O 효율화 원리 Ⅰ

jinsung·2025년 12월 16일
SQLPoraclesql데이터자격증

SQL

목록 보기
26/46
post-thumbnail

1. 블록 단위 I/O

오라클을 포함한 모든 DMBS에서 I/O는 블록 단위로 이루어진다.
레코드를 하나만 읽을 때도 그 레코드가 포함된 블록을 전체를 읽는다는 의미이다.

select ename from emp where sal >= 2000;

select * from emp where sal >= 2000;

위 두 쿼리는 서버에서 발생하는 I/O 측면에서의 일량은 같다.

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

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

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

  • 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때 (Direct Path I/O)

  • 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때
    => Single Block Read / Multiblock Read

  • 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때
    => Dirty 버퍼를 주기적으로 데이터파일을 기록하는 것
    DBWR 프로세스에 의해 수행되며, 한 번에 여러 블록씩 처리

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

오라클에서 허용하는 블록 크기는 2K, 4K, 8K, 16K, 32K, 64K이다.
데이터베이스를 생성할 때 표준 블록 크기를 지정하며, 다른 크기 블록을동시에 사용하려면 각각 별도의 테이블 스페이스와 버퍼 Pool을 구성해 주어야 한다.

Sequential 액세스 vs Random 액세스

Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말한다.
인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결돼 있고, 이 포인터를 따라 스캔하는 것이다.
테이블 레코드 간에는 포인터로 연결되지 않지만 테이블을 스캔할 때는 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다.
Sequential 액세스 성능을 향상시키려고 오라클 내부적으로 Multiblock I/O, 인덱스 Prefetch 기능을 사용한다.

Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식을 말한다.
Random 액세스 성능을 향상시키려고 오라클 내부적으로 버퍼 Pinning, 테이블 Prefetch 기능을 사용한다.

한 번 액세스할 때 Sequential 방식으로 그 안에 있는 모든 레코드를 읽는다면 비효율이 없다.
하지만, 하나의 레코드를 읽으려고 Sequential 방식으로 모든 레코드를 읽는다매우 비효율 적이다.
Sequential 액세스 방식을 사용하려면 선택도가 높아야 한다.

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

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

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

2. Memory vs Disk I/O

디크스를 통한 입출력은 물리적으로 액세스 암이 움직이면서 헤드를 통해 데이터를 입출력하기 때문에 매우 느리다.
반면, 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 매우 빠르다.
그래서 모든 DBMS가 버퍼 캐시를 경유해 I/O를 수행한다.
=> 읽고자 하는 블록을 버퍼 캐시에서 찾고 없을 경우에만 디스크를 통해 입출력함.

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

버퍼 캐시 히트율(Buffer Cache Hit Ratio)이란?

버퍼 캐시 히트율이란 버퍼 캐시 효율을 측정하는 지표를 말한다.
전체 읽은 블록 중에서 얼만큼 메모리 버퍼 캐시에서 찾았는지의 비율을 말한다.

BCHR = ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100

=> 논리적 I/O (총 읽은 블록 수)는 query + current
물리적 I/O = disk

==> BCHR = ((query + currnet - disk) / (query + current)) * 100

= 1 - (disk / (query + current) * 100

OLTP 환경에서는 BCHR 99% 달성을 목표로 삼아야 한다.
논리적 I/O를 줄이고, 그럼으로써 물리적 I/O를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리이다.

같은 블록을 반복적으로 액세스하는 형태의 애플리케이션이라면 논리적 I/O가 비효율적으로 많이 발생해도 BCHR이 매우 높게 나타날 것이다.
=> BCHR가 성능지표로서 갖는 한계점

사실상 같은 블록을 반복적으로 액세스하면 BCHR은 높아지겠지만 블록을 찾는 과정에서 래치를 얻어야하기때문에 큰 비용을 수반한다.
따라서, BCHR이 100%라도 논리적 I/O의 절대량이 많다면 반드시 SQL 튜닝을 실시해 논리적 I/O를 최소화해야 한다.

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

메모리 I/O, 디스크 I/O 발생량뿐 아니라 네트워크 속도가 I/O 성능에 영향을 미치고 있다.
네트워크를 이용한 I/O 기법이 일반화되다 보니 I/O 성능 개선도 네트워크를 통한 전송량을 최소화하려는 쪽에 기술이 모아지고 있다.

아무리 네트워크, 파일시스템 캐시가 I/O 에 영향을 미쳐도, I/O 성능에 관한 가장 확실하고 근본적은 해결책인 논리적 I/O 를 최소화하는 것이다.

3. Single Block vs Multiblock I/O

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

1. Single Block I/O

2. Multiblock I/O

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

Multiblock I/O는 Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 것을 말한다.
'인접된 블록'이란 한 익스텐트 내에 속한 블록들을 말한다.
달리 말하면, Multiblock I/O 방식으로 읽더라도 익스텐트 범위를 넘지 못한다는 뜻이다.

Multiblock I/O 단위는 db_file_miltiblock_read_count 파라미터에 의해 결정된다.
아무리 파라미터 값을 크게 설정해도 최대 OS가 허용하는 I/O 단위만큼 읽는다.

인덱스를 스캔할 때는 왜 한 블록씩 읽는 것일까?

인덱스 블록간 논리적 순서란, 인덱스 리프 블록끼리 이중 연결 리스트 구조로 연결된 순서를 말한다.
물리적으로 한 익스텐트에 속한 블록들을 I/O Call 발생 시점에 같이 적재해 올렸는데, 그 블록들은 논리적 순서로는 한참 뒤쪽에 위치할 수 있다.
그러면 그 블록들은 실제 사용되지도 못하고 버퍼 상에서 밀려날 수 있다.
이런 현상이 자주 발생한다면 버퍼 캐시 효율이 떨어져 인덱스 스캔 시에는 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 파라미터로 결정한다.

서버 프로세스는 디스크에서 블록을 읽어야 하는 시점마다 I/O 서브시스템에 I/O 요청을 하고 대기 상태에 빠지는데 이때 대표적인 두 가지 대기 이벤트가 있다.

1. db file sequential read 대기 이벤트

Single Block I/O 방식으로 I/O를 요청할 때 발생

2. db file scattered read 대기 이벤트

Multiblock I/O 방식으로 I/O를 요청할 때 발생

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

4. Prefetch

오라클을 포함한 모든 DBMS는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 불러오는 Prefetch 기능을 제공한다.
디스크 I/O가 비용이 크기 때문에 한번의 I/O Call을 통해 다량의 블록을 퍼 올릴 수 있다면 그만큼 성능향상에 도움이 되기 때문이다.

Prefetch는 한번에 여러 개 Single Block I/O를 동시 수행하는 것을 말한다.
Prefetch는 테이블 Prefetch와 인덱스 Prefetch로 나뉘는데 인접하지 않은 블록,
즉 서로 다른 익스텐트에 위치한 블록을 배치방식으로 미리 적재하는 것을 말한다.**

이 기능은 곧 읽을 가능성이 높은 블록들을 미리 적재했을 때만 성능 향상에 도움을 주고, 실제 사용으로 연결되지 못하면 버퍼 캐시의 효율만 나빠진다.

오라클은 미리 적재했을 때 효과를 얻을 수 있는 오퍼레이션에만 이 기능을 적용하고, 그럼에도 연결되지 못한 채 캐시에서 밀려나는 블록들의 비율이 높다면 이 기능을 정지시킨다.

앞으로 읽어야 할 블록들을 미리 적재하는 기능이므로 시스템 전반의 디스크 경합을 줄여주기보다, I/O를 위한 시스템 Call을 줄이고 개별 쿼리의 수행 속도를 향상시키는 데 주로 도움을 준다.

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

1. 인덱스 Prefetch

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

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

2. 테이블 Prefetch

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

리프 블록에 있는 인덱스 레코드는 논리적인 순서에 따라 읽는다.
읽는 도중에 디스크 I/O가 필요해지면 현재 읽던 리프 블록 내에서 앞으로 액세스해야 할 테이블 블록 주소 목록을 미리 취합할 수 있다.

이 기능은 인덱스 클러스터링 팩터가 나쁠 때 효과를 더 발휘한다.
=> 클러스터링 팩터가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문이다.

버퍼 Pinning은 Random 액세스에 의한 논리적 블록 요청 횟수를 감소시키고, 테이블 Prefetch는 디스크 I/O 에 의한 대기 횟수를 감소시킨다.
한 번 액세스해 그 곳에 저장된 레코드를 모두 읽어들이므로 Random 액세스의 비효율을 완전히 제거할 수 있다.

5. Direct Path I/O

대용량 데이터를 읽고 쓸 때 버퍼 캐시를 경유한다면 오히려 성능이 나빠질 수 있다.
재사용 가능성이 없는 데이터들은 버퍼 캐시를 경유하지 않는 것이 유리하다.
이럴 때 오라클은 버퍼 캐시를 경유하지 않는 Direct Path I/O 기능을 제공한다.

Direct path I/O 기능의 작동 경우

  • 병렬 쿼리로 Full Scan 할 때
  • parallel DML을 수행할 때
  • Direct Path Insert를 수행할 때
  • Temp 테이블 스페이스의 세그먼트 블록들을 읽고 쓸 때
  • nocache 옵션을 지정하고 LOB 컬럼을 읽을 때
  • direct 옵션을 지정하고 export를 수행할 때

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 방식을 사용한다.
병렬도를 x를주면 x배 빨라지는게 아니라 그 이상이 빨라지는 이유이다.
따라서 대용량 데이터를 읽을 때는 Full Scan과 병렬 옵션을 적절히 사용함으로써 시스템 리소스를 적게 사용하도록 하는 것이 좋다.

Direct Path Read 과정에서 읽기 Call이 완료될 때까지 발생하는 대기 이벤트는 direct path read로 측정된다.

버퍼 캐시에만 기록된 변경사항이 아직 데이터파일에 기록되지 않은 상태에서 데이터파일을 직접 읽으면 데이터 정합성에 문제가 생긴다.
따라서 병렬로 Direct Path Read를 수행하려면 메모리와 디스크간 동기화를 먼저 수행함으로써 Dirty 버퍼를 해소해야 한다.

3. Direct Path Write

Direct Path Write는 병렬로 DML을 수행하거나 Direct Path Insert 방식으로 데이터를 삽입할 때 사용된다.
이 과정에서 I/O Call이 발생할 때마다 direct path write 대기 이벤트가 발생한다.

  • Insert - Select 문장에 /*+append*/ 힌트 사용
  • 병렬 모드로 Insert
  • CTAS 문장을 수행
  • direct 옵션을 지정하고 SQL*Loader로 데이터를 로드

일반적인 Insert

  1. Freelist를 통해 데이터를 삽입할 블록을 할당받는다.

  2. Freelist를 조회하면서 Random 액세스 방식으로 버퍼 캐시에서 해당 블록을 찾는다.

  3. 없으면 데이터파일에서 읽어 캐시에 적재한 후 데이터를 삽입한다.

=> 대량의 데이터를 삽입할 때 매우 느림

Direct Path Insert

Freelist를 참조하지 않고 테이블 세그먼트 또는 각 파티션 세그먼트의 HWM 바깥 영역에 데이터를 순차적으로 입력한다.

  • Freelist로부터 블록을 할당받는 작업 생략

  • insert 할 블록을 버퍼 캐시에 적재하지않고 데이터파일에 직접 insert

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

  • 테이블 nologging 시 Redo 로그 최소화 (데이터 딕셔너리 변경사항만 로깅)

Direct Path Insert 방식이나 병렬 DML 시 Exclusive 모드 테이블 Lock 이 걸린다.

=> 해당 테이블에 다른 트랜잭션이 DML을 수행하지 못함
따라서 트랜잭션이 빈번한 주간에 이 옵션은 사용하면 안 된다.

profile
jinsung
Data Engineer
이전 포스트

SQL) 데이터베이스 최소화 원리 Ⅲ

다음 포스트

SQL) I/O 효율화 원리 Ⅱ

0개의 댓글