쿼리를 실행할 때 사용하는 인덱스에 쿼리에서 참조되는 칼럼이 모두 포함된 경우가 아니라면, 테이블 랜덤 액세스가 발생합니다.
인덱스 ROWID에 의한 테이블 액세스
인덱스에 저장된 ROWID는 오브젝트 번호, 데이터 파일 번호, 블록 번호로 구성된 물리적 주소정보입니다. ROWID에 포함된 DBA는 데이터 파일 상의 데이터블록이 저장된 물리적 위치정보입니다. 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하기 때문에 메모리의 버퍼블록 위치를 찾기 위해 해시맵을 사용합니다. 해시맵의 키 값으로 ROWID 내부의 데이터 블록 주소를 활용합니다. 버퍼 캐시는 SGA에 위치하기 때문에 버퍼블록을 찾는 과정에서 LATCH, BUFFER LOCK 등의 경합이 발생할 수 있습니다.
인덱스 ROWID를 이용해 테이블 블록을 읽는 메커니즘은 다음과 같습니다.
- 인덱스에서 레코드의 ROWID를 읽고 내부의 DBA(DataBlockAddress) 를 해시 함수에 적용해 해시값을 도출합니다.
- 도출한 해시 값을 이용해 해시 버킷을 찾아갑니다.
- 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더를 찾습니다.
- 해시 체인에서 블록 헤더를 찾으면 헤더에 저장된 포인터를 이용해 버퍼블록을 읽습니다.
- 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 FREE 버퍼를 찾습니다. 디스크에서 읽어올 블록을 적재하기 위한 빈 캐시 공간을 찾기 위함입니다.
- LRU 리스트에서 FREE 버퍼를 얻지 못하면 DIRTY 버퍼를 디스크에 기록해 FREE 버퍼를 확보합니다.
- FREE 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재합니다.
ROWID에 의한 테이블 엑세스는 위와 같은 과정을 거치기 때문에 속도가 느립니다. 특히 다량의 테이블 레코드를 읽을 때 성능 저하가 큽니다.
클러스터링 팩터
ORACLE은 클러스터링 팩터 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가합니다. 클러스터링 팩터는 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 모여있는 정도를 나타내는 계수입니다. 인덱스 레코드의 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않으면 클러스터링 팩터는 0입니다.
인덱스 손익분기점
TABLE FULL SCAN 방식이 INDEX RANGE SCAN 방식보다 빨라지는 지점을 의미합니다. 인덱스 손익분기점은 일반적으로 전체 레코드양의 5~20% 수준이지만 클러스터링 팩터에 따라 달라집니다. 클러스터링 팩터가 나쁘면 손익분기점은 5% 미만이 되기도 합니다. 반대로 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지 올라갑니다.
TABLE FULL SCAN의 속도상 이점은 다음과 같습니다. 인덱스 ROWID에 의한 테이블 액세스는 랜덤 엑세스인 반면, TABLE FULL SCAN은 시퀀셜 엑세스 방식입니다. 디스크 I/O 시, 인덱스 ROWID에 의한 테이블 엑세스는 SINGLE BLOFK READ 인 반면, TABLE FULL SCAN 은 MULTIBLOCK READ 방식입니다.
인덱스 스캔 손익분기점 향상 방안
손익분기점 원리에 따르면 선택도가 높은 칼럼으로 구성된 인덱스는 효용가치가 낮습니다. 그러나 TABLE FULL SCAN 또한 부담이 크기는 마찬가지입니다. 이럴 때 DBMS가 제공하는 기능을 잘 활용하면 인덱스 손익분기점 한계를 극복하는데 도움이 됩니다.
첫 번째, SQL SERVER의 클러스터형 인덱스 또는 ORACLE 이 제공하는 IOT 테이블로 인덱스와 동일한 구조의 테이블을 생성할 수 있습니다. 테이블 자체가 인덱스 구조이므로 항상 기준 칼럼으로 정렬된 상태를 유지합니다. 더불어 인덱스 리프블록이 곧 테이블 블록이기 때문에 인덱스를 수직 탐색한 다음 추가적인 랜덤 액세스가 불필요합니다.
두 번째, SQL SERVER의 INCLUDE INDEX를 활용할 수 있습니다. 인덱스 키 외에 미리 지정한 칼럼을 리프레벨에 함께 저장하는 기능입니다. 테이블 랜덤 엑세스 횟수를 줄여줍니다.
세 번째, ORACLE이 제공하는 클러스터 테이블 기능입니다. 키 값이 같은 레코드를 같은 블록에 저장하기 때문에 테이블 랜덤 엑세스가 키 값 별로 한 번씩만 발생합니다. 클러스터에 도달해서는 시퀀셜 리드 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없습니다.
네 번째, 파티셔닝 기능입니다. 수천만 건에 이르는 테이블을 FULL SCAN 하기 어렵다면 대량 범위검색 조건으로 자주 사용하는 칼럼을 기준으로 테이블을 파티셔닝 하면 일부 데이터만 읽고 멈출 수 있습니다. 클러스터는 기존 키 값이 같은 레코드를 블록 단위로 모아 저장하지만, 파티션은 세그먼트 단위로 저장합니다.
배치프로그램
배치 프로그램에서는 FULL SCAN과 해시조인의 활용성이 높습니다. 파티션과 병렬 처리의 효과도 높습니다. 온라인 프로그램은 보통 소량의 데이터만 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요합니다. 대량의 데이터를 읽고 갱신하는 배치 프로그램은 항상 전체범위 처리를 기준으로 튜닝해야 합니다. 처리대상 집합 중 일부를 빠르게 처리하는 것이 아닌 전체를 빠르게 처리하는 것을 목표로 삼아야 합니다. 추가적으로 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 FULL SCAN이 유리합니다
테이블 액세스 최소화 튜닝
인덱스 칼럼 추가
-- EMP_X01 : DEPTNO + JOB
SELECT /*+INDEX(EMP EMP_X01)*/
ENAME, JOB, SAL
FROM EMP
WHERE DEPTNO = 30
AND SAL >= 2000
위 조건을 모두 만족하는 레코드는 한 건뿐이지만 DEPTNO = 30 인 인덱스 레코드가 6건이기 때문에 6번의 테이블 액세스가 발생합니다. 이때 인덱스 구성 칼럼으로 SAL 칼럼을 추가한다면 큰 효과를 거둘 수 있습니다. 인덱스의 스캔량은 줄지 않지만 랜덤액세스 횟수가 감소하기 때문입니다.
-- 로밍렌탈_N2 인덱스 : 서비스번호 + 사용여부
SELECT 렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시
, 방문국가코드, 로밍승인번호, 자동로밍여부
FROM 로밍렌탈
WHERE 서비스번호 LIKE '010%'
AND 사용여부 = 'Y'
AND 자동로밍여부 = 'N'
-- rows row source operation
-- 1909 TABLE ACCESS BY INDEX ROWID 로밍렌탈 (CR=266968 PR = 27830)
-- 266476 INDEX RANGE SCAN 로밍렌탈_N2 (CR=1011 PR=900 TIME=893462)
인덱스 스캔을 통해 264,476 건이 선택됐고 그중 1011건의 버퍼캐시리드(CR)를 제외하고 265,957 개의 블록을 디스크에서 읽어왔습니다. TABLE FILTER 조건인 자동로밍여부 조건으로 인해 최종적으로 1909 개의 레코드가 결과집합이 됩니다. TABLE FILTER 조건인 자동로밍여부 칼럼이 인덱스에 추가되면 인덱스 스캔에서 1909 개만 선택되기 때문에 테이블 랜덤 액세스 비효율이 없습니다.
인덱스 칼럼을 추가하면 인덱스 사이즈가 증가합니다. 한 블록에 담을 수 있는 레코드의 수가 줄면 리프노드의 수가 많아지고 인덱스 깊이도 깊어집니다. 리프블록의 수가 많아지면 인덱스 블록 I/O 가 증가하기 때문에 인덱스 스캔 효율이 나빠집니다. 인덱스 뒤쪽에 칼럼을 추가하면 새로운 정렬을 해야 하므로 클러스터링팩터가 나빠질 수 있습니다.
인덱스 재구성은 테이블 액세스 최소화 방안이 아니다.
인덱스 재구성을 통해 인덱스 스캔 과정에서 발생하는 비효율을 줄일 수는 있지만 테이블 액세스 최소화와는 관계가 없습니다.
COVERED INDEX
테이블 랜덤 액세스가 아무리 많더라도 테이블 필터 조건에 의해 버려지는 레코드가 없다면 비효율은 없습니다. 이때는 아예 테이블 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있습니다. 이렇게 인덱스만 읽고 처리하는 쿼리를 COVERED 쿼리라고 합니다.
수동으로 클러스터링 팩터 높이기
테이블에는 데이터가 무자위로 입력되는 반면, 인덱스는 항상 key 순서로 정렬되기 때문에 클러스터링 팩터는 점차 나빠집니다. 클러스터링 팩터가 나쁜 인덱스를 이용해 대량 데이터를 읽어야 할 때 인덱스를 기준으로 테이블을 재생성해 인위적으로 클러스터링 팩터를 높이는 방법이 있습니다.
주의할 것은, 여러 상황에서 특정 인덱스를 기준으로 매번 테이블을 재정렬하면 다른 인덱스의 클러스터링 팩터가 나빠질 수도 있습니다. 따라서 가장 자주 사용되는 인덱스를 기준으로 삼고 다른 인덱스를 사용하는 중요한 쿼리에 영향을 주지 않는지 확인해야 합니다.
배치 I/O
인덱스 ROWID를 활용한 테이블 액세스는 고비용입니다. 대량의 데이터를 인덱스를 이용해 조회하면 디스크 I/O의 수가 증가해 성능이 나빠집니다. 배치 i/o 는 읽는 블록마다 건건히 RANDOM ACCESS가 발생하는 비효율을 줄이기 위해 고안된 기능입니다. 인덱스를 이용해 테이블을 액세스 하다가 버퍼 캐시에서 블록을 찾지 못하면 TABLE RANDOM ACCESS 합니다. 배치 I/O 기능이 작동하면 데이터 블록에 대한 디스크 입출력 호출을 미뤘다가 일정량 쌓이면 일괄 처리합니다.
배치 I/O 기능이 작동하면 인덱스를 이용해 출력하는 데이터의 정렬이 매번 달라질 수 있습니다. 테이블 블록을 모두 버퍼 캐시에서 찾을 때는 기존처럼 인덱스 키 값으로 데이터가 출력됩니다. 실제 배치 I/O가 작동할 때는 데이터 출력 순서가 인덱스 정렬 순서와 달라집니다.
주의할 점은 소트 연산을 생략할 수 있는 인덱스 구성임에도 배치 I/O가 동작하면 SORT ORDER BY 오퍼레이션이 나타난다는 점입니다. 배치 I/O 기능으로 인해 데이터 정렬 순서를 보장할 수 없기 때문입니다. 정렬된 결과를 얻기 위해선 ORDER BY를 항상 명시적으로 추가해야 합니다. 이 기능이 동작하더라도 SORT ORDER BY 오퍼레이션이 나타나지 않는 한 부분 처리는 가능합니다.
