[SQLP] 1-5. I/O 메커니즘

🧷 I/O 메커니즘

🖇️ 블록 단위 I/O

오라클을 비롯한 대부분의 DBMS에서 I/O는 블록(Block) 단위로 수행된다. 이는 특정 레코드를 읽거나 쓰기 위해 해당 레코드가 포함된 블록 전체를 읽고 써야 함을 의미한다.

 

오라클의 기본 블록 크기는 8KB이며, 단 1바이트를 읽더라도 최소 8KB를 읽어야 한다. 이 원칙은 테이블뿐만 아니라 인덱스에서도 동일하게 적용된다.

 

예를 들어, 다음 두 개의 SQL 문을 비교해 보자.

SELECT COL FROM TBL WHERE NUM > 100;

SELECT * FROM TBL WHERE NUM > 100;

 

두 쿼리가 동일한 실행 계획을 사용한다면, 서버에서 발생하는 I/O 작업량은 동일하다. 특정 레코드 하나만 읽더라도 해당 레코드가 포함된 블록 전체를 읽어야 하기 때문이다. 단, 네트워크를 통해 클라이언트로 전송되는 데이터 양은 차이가 날 수 있다.

 

SQL 성능을 결정하는 가장 중요한 성능 지표는 액세스 하는 블록 개수이며, 옵티마이저가 실행 계획을 결정할 때도 가장 큰 영향을 미친다. 블록 단위 I/O는 다음과 같은 경우에 적용된다.

• 메모리 버퍼 캐시에서 블록을 읽고 쓸 때
• 데이터 파일에 저장된 데이터 블록을 직접 읽거나 쓸 때 → Direct Path I/O
• 데이터 파일에서 DB 버퍼 캐시로 블록을 적재할 때 → Single Block Read or Multiblock Read 방식
• 버퍼캐시에서 변경된 블록을 다시 데이터파일에 저장할 때
  • DBWR 프로세스가 수행하며, 성능 향상을 위해 한 번에 여러 블록씩 처리

즉, SQL 성능 최적화의 핵심은 불필요한 블록 액세스를 줄이는 것이다.

 

✅ Oracle Block Size

 

오라클에서 지원하는 블록 크기는 2K, 4K, 8K, 16K, 32K, 64K이며, 데이터베이스를 생성할 때 표준 블록 크기를 지정한다. 다른 크기의 블록을 함께 사용하려면 별도의 테이블스페이스 및 버퍼 풀(Buffer Pool)을 구성해야 한다.

 

현재 설정된 블록 크기는 다음 명령어로 확인할 수 있다.

SHOW PARAMETER BLOCK_SIZE; -- 1번 방법

SELECT VALUE-- 2번 방법
FROM V$PARAMTER
WHERE NAME = 'db_block_size';

 

🖇️ Sequential VS Random 액세스

 

데이터베이스에서 블록 단위 I/O를 수행할 때, 블록을 읽는 방식에는 Sequential 액세스와 Random 액세스가 있다.

1. Sequential 액세스

Sequential 액세스는 레코드 간 논리적 또는 물리적인 순서를 따라 차례대로 데이터를 읽는 방식이다. 즉, 연속된 블록을 순차적으로 스캔하며 데이터를 읽어온다.

(1) 인덱스 블록에서의 Sequential 액세스

오라클의 인덱스 리프 블록(Leaf Block)에 저장된 레코드는 주소 값을 따라 논리적으로 연결되어 있으며, 이를 기반으로 순차적으로 블록을 읽는다. 위 그림에서 5번 방식이 여기에 해당한다. 오라클 내부적으로 인덱스 Prefetch 기능을 사용하여 성능을 향상한다.

 

➔ 대표적인 예: Index Range Scan

(2) 테이블 블록에서의 Sequential 액세스

테이블의 레코드는 인덱스처럼 포인터로 직접 연결되지 않기 때문에 논리적인 연결고리가 없다. 대신, 오라클은 익스텐트(Extent) 정보를 세그먼트 헤더에서 맵(Map)으로 관리하며, 이 정보를 바탕으로 물리적으로 연속된 블록을 순서대로 읽어 나간다. 오라클은 Multiblock I/O 기능을 사용하여 한 번에 여러 블록을 읽는 방식으로 성능을 향상한다.

 

➔ 대표적인 예: Full Table Scan

2. 랜덤(Random) 액세스

Random 액세스는 레코드 간 논리적 또는 물리적인 순서를 따르지 않고, 특정 데이터를 찾기 위해 한 블록씩 접근하는 방식이다. 위 그림에서 5번을 제외한 방식이 여기에 해당한다. 오라클은 버퍼 Pinning(Buffer Pinning)과 테이블 Prefetch 기능을 활용하여 Random 액세스 성능을 향상한다.

 

➔ 대표적인 예: Index Unique Scan, Index Range Scan 후 Table Access by ROWID

3. I/O 튜닝의 핵심

I/O 튜닝에서는 Sequential 액세스와 Random 액세스를 적절히 활용하는 것이 중요하다.

 

Random 액세스는 특정 레코드 하나를 찾기 위해 한 블록씩 랜덤 하게 접근하면, I/O가 많아지고 성능이 저하될 수 있기 때문에 비효율적일 수 있다. 따라서, Random 액세스를 최소화하는 것이 중요하다.

 

Sequential 액세스가 항상 효율적인 것은 아니다. 테이블 전체를 순차적으로 읽는다고 해도, 읽어온 데이터 중 결과 집합으로 선택되는 비율(선택도, Selectivity)이 낮다면 비효율적이다. 예를 들어, 만 개의 데이터를 읽고 1개만 선택된다면, Random 액세스보다 효율적이지 않다. 즉, Sequential 액세스의 효율성은 선택도(Selectivity)에 의해 결정된다.

 

✔️ I/O 튜닝의 핵심

1. Sequential 액세스의 선택도를 높인다.
   • 불필요한 Full Table Scan을 줄이고, 필요한 데이터만 읽도록 유도한다.
   • 필요한 경우 적절한 인덱스를 활용하여 전체 스캔을 줄인다.
2. Random 액세스 발생량을 줄인다. : 인덱스를 최적화하여 불필요한 Random 액세스를 줄인다.

 

🖇️ Memory VS Disk I/O

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

① Disk I/O의 비효율성

 

디스크를 통한 입출력(I/O)은 물리적으로 액세스 암(Arm)이 움직여 헤드를 통해 데이터를 읽고 쓰는 과정이 필요하기 때문에 속도가 느리다. 반면, 메모리에서 이루어지는 I/O는 전기적 신호를 이용한 작업이므로 디스크 I/O에 비해 압도적으로 빠르다. 이러한 이유로 모든 DBMS는 데이터를 읽을 때 먼저 버퍼 캐시(Buffer Cache)를 탐색한 후, 캐시에 없는 경우에만 디스크에서 데이터를 가져온다.

 

② 데이터 캐싱의 필요성

 

SQL을 실행하는 과정에서 데이터 블록을 지속적으로 읽어야 하는데, 자주 사용하는 블록을 매번 디스크에서 불러오는 것은 비효율적이며 SQL 성능을 저하시킨다. 따라서 DBMS는 데이터 캐싱(Caching) 메커니즘을 활용하여, 한 번 디스크에서 읽은 데이터 블록을 메모리에 저장하고 재사용함으로써 반복적인 I/O 호출을 줄인다.

 

③ 유한한 메모리 자원

 

이론적으로 모든 데이터와 인덱스 블록을 DB 버퍼 캐시에 올려놓고 사용하면 성능이 극대화되겠지만, 비용과 기술적 한계로 인해 현실적으로 불가능하다. 따라서 제한된 메모리 자원을 보다 효율적으로 활용하기 위해 LRU(Least Recently Used) 알고리즘을 적용하여 자주 액세스되는 블록이 캐시에 더 오래 유지되도록 한다.

 

➞ 결국, 디스크 I/O를 최소화하고 가능한 많은 처리를 메모리에서 수행하도록 버퍼 캐시의 효율성을 극대화하는 것 이 데이터베이스 성능을 좌우하는 핵심 요소라 할 수 있다.

2. 논리적 VS 물리적 I/O

(1) 논리적 I/0

논리적 I/O 
    = SQL 처리하는 과정에 발생한 총 블록 I/O
    = 메모리 I/O + Direct Path I/O (버퍼 캐시를 경유하지 않는 경우 포함)

 

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

 

따라서, SQL을 수행하면서 읽은 총 블록 I/O의 개수 가 논리적 I/O 에 해당한다. 논리적 I/O는 개념적으로 메모리 I/O 와 다르지만, 실제 수치는 동일하게 나타난다.

(2) 물리적 I/0

물리적 I/O 
    = 디스크 상에서 발생한 총 블록 I/O
    = 메모리상의 버퍼 캐시에서 찾지 못한 일부 논리적 I/O
    = 디스트 I/O

 

DB 버퍼 캐시에서 블록을 찾지 못하면 디스크에서 직접 데이터를 읽게 되는데, 이 과정에서 발생하는 블록 I/O가 물리적 I/O이다.

 

SQL을 실행할 때 논리적 I/O의 총량은 동일하지만, 물리적 I/O의 값은 달라질 수 있다. 그 이유는 동일한 SQL을 반복 실행할수록 DB 버퍼 캐시에 해당 테이블의 블록이 점점 더 많이 적재되므로, 디스크에서 직접 데이터를 읽을 필요가 줄어들어 물리적 I/O가 감소하기 때문이다.

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

버퍼 캐시 히트율(BCHR) 은 버퍼 캐시의 효율성을 측정하는 중요한 지표로, 전체 읽은 블록 중 디스크 I/O 없이 메모리에서 직접 찾은 블록의 비율을 의미한다.

 

BCHR을 계산하는 공식은 다음과 같다.

BCHR 
    = (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) X 100
    = ( (논리적 I/O - 물리적 I/O) / 논리적 I/O) X 100
    = (1 - 물리적 I/O / 논리적 I/O) X 100

 

이 공식을 보면, BCHR이 높을수록 디스크 I/O 없이 메모리에서 데이터를 찾은 비율이 높다 는 것을 알 수 있다.

 

① 성능 향상과 SQL 튜닝

 

논리적 I/O의 총량은 SQL이 실행될 때 일정하기 때문에, BCHR을 높이기 위해 물리적 I/O를 직접 줄이는 것은 어렵다. 따라서 SQL 성능 최적화를 위해 가장 효과적인 방법은 논리적 I/O 자체를 줄이는 것이다.

 

논리적 I/O를 줄이기 위해서는 읽어야 할 총 블록 개수를 줄이는 것 이 핵심이다. SQL 실행 시 논리적 I/O는 일정하지만, SQL 튜닝을 통해 감소시킬 수 있는 변수이다.

 

즉, I/O 튜닝의 핵심 원리는 논리적 블록 요청 횟수를 최소화하여 디스크에서 읽어야 할 블록 수를 줄이는 것이다.

 

② BCHR의 한계점

 

BCHR은 중요한 성능 지표이지만, 몇 가지 한계가 있다.

• 같은 블록을 반복적으로 액세스 하는 경우
  • 특정 애플리케이션이 같은 블록을 반복적으로 읽으면, 논리적 I/O 요청이 많아도 BCHR이 높게 나타날 수 있다.
  • 예를 들어, 작은 테이블을 매우 자주 액세스하는 경우 모든 블록이 메모리에서 즉시 검색 되므로 BCHR은 높아진다.
  • 반복적으로 블록을 찾는 과정에서 래치(Latch) 획득 비용이 증가하므로, 예상보다 높은 성능 부담이 발생할 수 있다.
• 래치 경합 및 버퍼 Lock 경합 발생하는 경우
  • 같은 블록을 여러 세션이 동시에 액세스 하면, 래치 경합과 버퍼 Lock 경합 이 발생할 수 있다.
  • 메모리 I/O 비용이 오히려 디스크 I/O보다 더 커질 수도 있다.

따라서 BCHR이 100%라고 하더라도 논리적으로 읽어야 할 블록의 개수가 많다면 SQL 튜닝을 통해 논리적 I/O 자체를 줄이는 것이 필수적이다.

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

• I/O 성능은 메모리 I/O, 디스크 I/O 발생량뿐 아니라 네트워크 속도에도 영향을 받는다.
• NAS, SAN을 통한 데이터 전송 방식이 일반화되면서 네트워크 성능이 중요한 변수로 작용한다.
• 오라클은 CPU, RAM, 디스크를 일체형으로 개발한 MPP 방식의 어플라이언스 제품들을 이용하여 네트워크 속도를 줄이려고 한다.
• RAC 환경에서는 네트워크를 통한 캐시 공유로 인해 네트워크 속도가 메모리 I/O에도 영향을 준다.
• I/O 최적화의 가장 확실하고 근본적인 해결책은 논리적 블록 요청을 줄여 불필요한 데이터 전송을 최소화하는 것이다.

 

🖇️ Single Block I/O VS Multiblock I/O

 

데이터 블록을 버퍼 캐시에 찾지 못했을 때, I/O Call을 통해 데이터 파일에서 버퍼 캐시에 적재하는 방식에는 Single Block I/O와 Multiblock I/O가 있다.

1. Single Block I/O

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

 

✔️사용사례

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

2. Multiblock I/O

I/O Call을 수행할 때, 인접한 여러 블록을 한꺼번에 읽어 메모리에 적재하는 방식이다. 오라클 블록 크기와 관계없이 OS는 보통 1MB 단위로 I/O를 수행한다. (OS마다 다름)

 

테이블 Full Scan처럼 물리적으로 저장된 순서에 따라 읽을 때는 허용된 범위 내에서 인접한 블록들을 함께 읽는 것이 유리하다. 인접한 블록은 같은 익스텐트에 속한 블록을 의미하며, 익스텐트 경계를 넘어 읽지는 않는다.

 

Multiblock I/O의 크기는 db_file_multiblock_read_count 파라미터에 의해 결정된다. 예를 들어, 값이 16이면 한 번에 최대 16개 블록을 버퍼 캐시에 적재한다. 하지만 파라미터 값을 아무리 높게 설정하더라도 OS의 I/O 단위가 1MB라면 한 번에 1MB까지만 읽는다.

 

✔️ 장점

• 많은 데이터 블록을 읽을 때 효율적이다.
• 인덱스를 사용하지 않는 테이블 전체 스캔(Full Scan)에 유리하다.
• 테이블이 클수록 Multiblock I/O 단위를 크게 설정하는 것이 효과적이다.
  • I/O 횟수를 줄여서 프로세스가 대기 큐에서 실행 대기하는 횟수를 줄일 수 있다.

 

3. Table Full Scan VS Index Range Scan

(1) Table Full Scan VS Index Range Scan

① Table Full Scan

• Sequential 액세스 + Multiblock I/O 방식으로 동작
• 테이블의 모든 블록을 순차적으로 스캔하여 읽는 방식
• 한 블록의 모든 레코드를 읽고, 캐시에 없으면 한 번의 I/O Call로 인접한 여러 블록을 읽는다.

② Index Range Scan

• Random 액세스 + Single Block I/O 방식으로 동작
• 인덱스를 스캔하면서 얻은 ROWID를 이용해 테이블 레코드를 찾아가는 방식
• 한 레코드 검색을 위해 1번 이상의 I/O Call이 필요

③ 성능 비교

 

Table Full Scan은 한 번의 I/O Call로 인접한 여러 블록을 읽을 수 있어 대용량 데이터 검색 시 성능이 우수하다. 스토리지의 스캔 성능이 좋아질수록 SQL 실행 성능도 좋아진다. 하지만, 큰 테이블에서 소량 데이터를 검색할 때는 비효율적이기 때문에 반드시 인덱스를 사용해야 한다.

 

Index Range Scan을 통한 테이블 액세스는 캐시에서 블록을 찾지 못하면 레코드 하나를 읽기 위해 1번 이상의 I/O Call을 해야 하는 방식이다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아져도 성능이 크게 좋아지지 않는다. 또한, 읽었던 블록을 또 반복해서 읽기 때문에 물리적 I/O뿐만 아니라 논리적 I/O측면에서도 비효율적이다.

 

즉, 소량 데이터 검색에는 Index Range Scan이, 일정량 이상의 데이터 검색에는 Table Full Scan이 유리하다.

 

✅ 10g부터 바뀐 점

• 10g부터는 Index Range Scan 또는 Index Full Scan 시에도 Multiblock I/O 방식이 적용될 수 있는데, 테이블 액세스 없이 인덱스만 읽고 처리할 때가 그렇다.
• 인덱스를 스캔하면서 테이블을 Random 액세스 할 경우에는 여전히 Single Block I/O 방식을 사용한다.

(2) Index Range Scan이 Single Block I/O 방식을 쓰는 이유

디스크 I/O는 비용이 크므로 I/O Call이 한 블록씩 읽는 것보다 여러 블록을 읽는 것이 성능 향상에 도움이 된다. 그럼에도 불구하고 인덱스 스캔 시 Single Block I/O 방식을 사용하는 이유는 다음과 같다.

• 인덱스 블록 간 논리적 순서는 물리적 저장 순서와 다르다.
  • 논리적 순서는 인덱스 리프 블록들이 이중 연결 리스트 구조로 연결된 순서를 의미한다.
  • 물리적으로 순차적으로 저장된 테이블 블록과 달리, 인덱스 블록은 ROWID에 따라 불규칙하게 저장될 수 있다.
• 논리적 순서를 유지하려면 Single Block I/O 방식이 더 효율적이다.
  • 여러 블록을 한꺼번에 읽으면 필요한 순서대로 정렬되지 않아 추가적인 연산 비용이 발생할 수 있다.

Index Full Scan도 같은 원리로 동작한다. 하지만 Index Fast Full Scan의 경우 논리적 순서를 무시하고 물리적 순서대로 읽기 때문에 Multiblock I/O를 사용한다.

4. 버퍼캐시 탐색 대상

모든 블록 I/O는 기본적으로 메모리 버퍼 캐시를 경유한다. (단, Direct Path I/O 제외)

 

✔️ 버퍼 캐시에 적재되는 I/O 대상

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

Single Block I/O로 읽은 블록들은 LRU 리스트의 MRU(Most Recently Used) 쪽에 배치되어 캐시에 오래 남는다. 반면, Multiblock I/O로 읽은 블록들은 LRU(Least Recently Used) 쪽에 배치되어 금방 캐시에서 밀려 남는다.따라서 대량 데이터 Full Scan 했다고 해서 버퍼 캐시를 모두 차지할 걱정은 하지 않아도 된다.

 

✅ LRU 리스트 상 LRU와 MRU

• MRU (Most Recently Used): 가장 최근에 사용한 블록을 저장하는 리스트
• LRU (Least Recently Used): 가장 오랫동안 사용되지 않은 블록을 저장하는 리스트

5. 주요 대기 이벤트

서버 프로세스는 디스크에서 블록을 읽을 때마다 I/O 요청을 하며 대기 상태에 빠진다. 대표적인 대기 이벤트는 다음과 같다.

• db file sequential read → Single Block I/O 요청 시 발생
• db file scattered read → Multiblock I/O 요청 시 발생

이러한 대기 이벤트를 분석하면 SQL 성능을 최적화하는 데 도움이 된다.

 

---

Reference

• 친절한 SQL 튜닝
• 오라클 성능고도화 원리와 해법 1