06.I/O 효율화 원리 - 01. 블록 단위 I/O

I/O효율화를 달성하기 위해서는 쉽지 않은데다 장기간의 훈련이 필요합니다. I/O효율화 튜닝을 잘하려면 인덱스 원리, 조인 원리, 옵티마이저 원리에 대한 이해가 필수적입니다.

 

오라클을 포함한 모든 DBMS에서 I/O는 블록(block)단위로 이루어집니다. 블록 단위로 I/O한다는 것은, 하나의 레코드에서 하나의 컬럼만을 읽으려 할 때도, 레코드가 속한 블록 전체를 읽게 됨을 뜻합니다. 이는 데이터베이스 I/O성능과 튜닝원리를 이해하는데 매우 중요합니다.

 

하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽는다면 설명 무거운 디스크 I/O를 수반하더라도 비효율은 없을 것입니다.(Sequential 액세스)

하지만 레코드 하나를 읽으려고 블록을 통째로 액세스한다면 그것은 메모리 버퍼에서 읽더라도 비효율이 존재합니다.(Random 액세스)

 

블록단위 I/O원리 때문에 아래 두 쿼리를 처리할 때 서버에서 발생하는 I/O측면에서의 일량은 같습니다.

- select ename from emp where sal >= 2000;

- select * from emp where sal >= 2000;

 

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

옵티마이저가 인덱스 스캔을 한지 full table scan할지를 결정하는데 가장 중요한 판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 할 블록 개수입니다.

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

참고로, 오라클 딕셔너리 정보를 저장하는 딕셔너리 캐시는 로우 단위로 I/O를 수행하여 로우 캐시라고도 부릅니다.

오라클에서 허용하는 블록 크기는 2K,4K,8K,16K,32K입니다. 데이터베이스를 구성할 때 표준 블록크기를 지정합니다.

 

(1) Sequential vs. Random 액세스

 

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

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

 

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

위의 그림에서는 1,2,3,4,6번이 여기에 해당합니다. 주로 4,6번 액세스가 성능 저하를 일으킵니다. NL 조인에서 inner 테이블 액세스를 위해 사용되는 인덱스에서는 1,2,3번까지도 성능에 지대한 영향을 미칠 수 있습니다. 이 기능을 향상시키려고 오라클 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용합니다.

 

Sequential 액세스 효율은 선택도에 의해 결정도비니다. 같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있습니다.

아래는 핵심 튜닝 원리입니다.

- Sequential 액세스의 선택도를 높입니다.

- Random 액세스 발생량을 줄입니다.