SQL 튜닝이론 - 테이블 액세스의 디스크 I/O를 줄이는 Batch I/O(배치 I/O)

SQL 튜닝이론 - 테이블 액세스의 디스크 I/O를 줄이는 Batch I/O(배치 I/O)

 

INDEX ROWID를 이용한 테이블 랜덤 액세스는 높은 비용을 요구합니다. 인덱스를 통해 테이블을 조회하면 디스크 I/O가 발생하기 때문입니다. 이때 부분범위처리 방식을 이용하면 상위 N개의 집합을 빠르게 조회할 수 있습니다. 부분범위처리가 가능한 조건은 레코드가 인덱스 칼럼순으로 정렬되어 SORT ORDER BY를 생략할 수 있어야 합니다.

 

테이블 랜덤 액세스의 효율을 높이려는 많은 시도 중 가장 눈에 띄는 개선방안은 BATCH I/O의 도입입니다. BATCH I/O는 테이블 액세스마다 발생하는 BLOCK I/O를 미뤘다가 읽어야 하는 블록이 일정량 쌓이면 한 번에 처리하는 I/O 방식입니다. 12C부터 ROWID를 이용하는 테이블 액세스에는 항상 BATCH I/O 방식이 적용됩니다.

 

데이터 정렬 이슈

 

BATCH I/O가 작동되면 조회 결과가 인덱스의 칼럼정렬 순서와 다를 수 있습니다. 테이블 블록을 모두 버퍼캐시에서 찾을 수 있으면 인덱스 컬럼정렬 순서와 동일하지만 BATCH I/O를 통해 테이블 랜덤 액세스로 읽어오는 레코드는 순서가 달라집니다.

 

-- 인덱스를 통해 SORT ORDER BY 를 생략한 실행계획

CREATE INDEX EMP_X01 ON EMP(DEPTNO, JOB, EMPNO);
SET AUTOTRACE TRACEONLY EXP;

SELECT * FROM EMP E
WHERE DEPTNO = 20
ORDER BY JOB, EMPNO

-- EXECUTION PLAN
-- SELECT STATEMENT
--        TABLE ACCESS BY INDEX ROWID
--            INDEX RANGE SCAN

 

아래는 BATCH I/O를 사용할 때의 실행계획입니다.

 

-- 배치 I/O를 활용한 실행계획

CREATE INDEX EMP_X01 ON EMP(DEPTNO, JOB, EMPNO);
SET AUTOTRACE TRACEONLY EXP;

SELECT /*+batch_table_access_by_rowid(e)*/ * 
FROM EMP E
WHERE DEPTNO = 20
ORDER BY JOB, EMPNO

-- EXECUTION PLAN
-- SELECT STATEMENT
--    SORT ORDER BY
--        TABLE ACCESS BY INDEX ROWID BATCHED
--            INDEX RANGE SCAN

 

실행계획을 보면 테이블 액세스에 BATCHED 가 추가됐고 SORT ORDER BY 도 추가된 것을 확인할 수 있습니다. BATCH I/O로 인해 데이터의 정렬 순서를 보장할 수 없기 때문에 옵티마이저는 SORT ORDER BY를 실행합니다. 애초에 인덱스 구조상 SORT ORDER BY를 생략할 수 없거나 사용자가 ORDER BY 하지 않는 경우에는 옵티마이저는 기본적으로 BATCH I/O를 선택합니다.

 

-- 인덱스 구조상 SORT ORDER BY를 생략할 수 없는 경우
SELECT * FROM EMP WHERE DEPTNO = 20 ORDER BY EMPNO;

-- EXECUTION PLAN

-- 0 SELECT STATEMENT
-- 1   SORT ORDER BY
-- 2     TABLE ACCESS BY INDEX ROWID BATCHED
-- 3       INDEX RANGE SCAN

-- 사용자가 ORDER BY 하지 않는경우
SELECT * FROM EMP WHERE DEPTNO=20;

-- EXECUTION PLAN

-- 0 SELECT STATEMENT
-- 1     TABLE ACCESS BY INDEX ROWID BATCHED
-- 2       INDEX RANGE SCAN

 

BATCH I/O는 시스템 레벨에서 비활성화되는 경우가 있습니다. 이는 인덱스 칼럼이 정렬된다는 점을 이용해 필요한 ORDER BY를 생략한 쿼리문을 실행하기 위함입니다. 예시는 아래와 같습니다.

 

-- 인덱스를 이용해 order by 없이 변경일시 순으로 상위레코드 10개 출력
SELECT /*+INDEX(H 상태변경이력_PK)*/ 장비번호, 변경일시, 상태코드
FROM 상태변경이력 H
WHERE 장비번호 = :eqp_no
AND ROWNUM < 10

-- 인덱스를 이용해 order by 없이 변경일시 역순으로 상위레코드 1개 출력
(
  SELECT /*+INDEX_DESC(H 상태변경이력_PK)*/ 장비번호, 변경일시, 상태코드
  FROM 상태변경이력 H
  WHERE 장비번호 = P.장비번호
  AND ROWNUM <= 1
)
FROM 장비 P
WHERE 장비구분코드 = 'A0001'

 

 

인덱스를 이용하면 결과집합이 자동으로 인덱스 키값 순서로 정렬되는 점을 이용해 ORDER BY를 생략한 채 ROWNUM, INDEX/INDEX_DESC 힌트를 사용하는 패턴이 과거에 많이 이용됐습니다. SORT ORDER BY OPERATION을 생략해 부분범위처리를 위해 ROWNUM 없이 아래와 같이 인덱스만 사용하는 경우도 있습니다.

 

SELECT /*+INDEX(H 상태변경이력_PK) */ 장비번호, 변경일시, 상태코드
FROM 상태변경이력 H
WHERE 장비변경 = :eqp_no ;

 

ORACLE 12C부터 BATCH I/O가 도입되면서 정렬 순서가 보장되지 않기 때문에 위 패턴은 반드시 수정되야 합니다. BATCH I/O가 실행되지 않도록 no_batch_table_access_by_rowid 힌트를 사용해도 되지만 order by를 추가하는 것이 권장됩니다. 인덱스 정렬을 믿고 order by를 생략하는 개발패턴은 지양되야 합니다.