3.1 테이블 액세스 최소화
인덱스 ROWID는 물리적 주소인가 논리적 주소인가?
아래는 인덱스를 이용해 테이블을 액세스하는 SQL 실행계획이다.
TABLE ACCESS BY INDEX ROWID 라고 표시된 부분이 여기에 해당한다.
Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 TABLE ACCESS BY INDEX ROWID OF '고객' (TABLE)
2 1 INDEX RANGE SCAN OF '고객_지역_IDX' (INDEX)인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는 데 있다.
인덱스 ROWID는 물리적 주소보다 논리적 주소에 가깝다.
물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문이다.
ROWID와 포인터(Pointer)
프로그래밍 언어에서 포인터는 메모리 주소값을 담는 변수를 말한다.
메모리상에서 데이터를 찾아가는 데 있어 포인터만큼 빠른 방법은 없으며, 그 비용이 0에 가깝다.
인덱스 ROWID는 포인터가 아니다.
인덱스 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.
프로그래밍에서 말하는 포인터와는 다르며, 테이블 레코드와 물리적으로 연결된 구조는 더더욱 아니다.
메인 메모리 DB와 비교
메인 메모리 DB란 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB라고 할 수 있다.
오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다. 메모리 주소 정보(포인터)가 아닌 디스크 주소정보를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.
I/O 메커니즘
I/O 성능을 높이려면 버퍼캐시를 활용해야 한다. 블록을 읽을 때는 디스크로 가기 전에 버퍼캐시부터 찾아본다.
읽고자 하는 DBA를 해시 함수에 입력해서 해시체인을 찾고 거기서 버퍼 헤더를 찾는다.
해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아간다.
인덱스로 테이블 블록을 엑세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻고,
테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.
인덱스를 이용해 테이블 블록을 찾아가는 과정
ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 때만 디스크에서 블록을 읽는다. 버퍼캐시에 적재한 후에 읽는다.
모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다.
인덱스 클러스터링 팩터(Index Clustering Factor)
클러스터링 팩터는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.
인덱스 손익 분기점
읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.
Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 인덱스 손익 분기점 이라고 부른다.
Table Full Scan은 성능이 일정하다. 전체 1000만건 중 한 건을 조회하든, 10만 건을 조회하든,
1000만 건을 다 조회하든 차이가 거의 없다.
인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 두가지 요인은 아래와 같다.
- Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 엑세스 방식이다.
- Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.
인덱스 손익분기점은 보통 5% ~ 20%의 낮은 수준에서 결정된다.
CF에 따라 크게 달라진다. 인덱스 CF가 나쁘면 같은 테이블 블록을 여러번 반복 액세스 하면서 논리적 I/O 횟수가 늘고 , 물리적 I/O 횟수도 늘기 때문이다.
액세스할 데이터 양이 만 건만 넘어도 시퀀셜 액세스와 Multiblock I/O 방식, 즉 Table Full Scan 방식으로 읽는 게 빠를 수 있다.
출처 : 친절한 SQL 튜닝 : (디비안, 조시형 저)
