05-01 SQL튜닝 공부내용 (테이블 액세스 최소화_1)

3.1 테이블 액세스 최소화

 

- 테이블 랜덤 액세스 

 

인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는데 있다.

(ROWID는 물리적 주소보다 논리적 주소에 가깝다고 볼수있다고 한다. 추가적으로 정리하자면, 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담기 때문..)

 

_ 메인 메모리DB와 비교

디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽음. (메인 메모리 DB만큼 빠르지는 않다.)

인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 메모리상의 주소정보, 즉 포인터를 갖는다. 따라서 인덱스를 경유해 테이블을 액세스하는 비용이 오라클과 비교할수 없을정도로 낮다고 한다.

 

_ I/O 매커니즘복습

DBA(= 데이터파일번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보이다.

I/O 성능을 높이려면 버퍼캐시를 활용해야 한다. 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾는다.

 인덱스로 테이블 블록을 액세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA정보를 얻고, 테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. 

 

* 인덱스 ROWID는 포인터가 아니다! ( 디스크 상에서 테이블 레코드를 찾아가기 위한 논리적인 주소 정보!!!)

 

_ 인덱스 ROWID는 우편주소

 

- 인덱스 클러스터링 팩터

 클러스터링 팩터(Clustering Factor, 이하 'CF')는 군집성 계수로 변역할 수 있는 용어로 [ 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미함.]

_CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.

 

// 인덱스 클러스터링 팩터 효과

CF가 좋은 컬럼에 생성한 인덱스가 검색 효율이 좋다는것은, 테이블 액세스량에 비해 블록I/O가 적게 발생함을 의미한다. (추후 설명보충)

 

- 인덱스 손익분기점

인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조. 따라서, 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는(Full Scan)보다 오히려 더 느려진다. 이 느려지는 지점을 흔히 '인덱스 손익분기점' 이라고 한다.

 

인덱스를 이용한 테이블 액세스가 Table Full Scan보다 느려지게 만드는 가장 핵심적인 두가지 요인은 아래와 같다.

1. Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식

2. Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O방식.

 

위 와 같은 요인에 의해 인덱스 손익분기점은 보통 5~20% 낮은 수준에서 결정된다. CF가 나쁘면 손익 분기점은 5% 미만에서 결정되며, 심할때는 (BCHR이 매우 안 좋을때) 1% 미만으로 낮아진다. 반대로 CF가 아주 좋을때는 손익분기점이 90% 수준까지 올라가기도 한다.

 

* 인덱스 손익분기점과 버퍼캐시 히트율

인덱스를 스캔하면서 테이블을 액세스하다 보면 어느 순간부터 대부분 테이블 블록을 캐시에서 찾게된다. 

만 건만 넘어도 시퀀셜 액세스와 Multiblock I/O방식, 즉 Table Full Scan 방식으로 읽는게 빠를 수 있다.

 

주의) 저자가 말하길 인덱스가 항상 좋을수 없음을 설명하려고 손익분기점이란 개념을 사용했을뿐, 이를 높이기 위해 어떤 조치를 해야 한다는 뜻으로 오해하지 말길 바라며, 테이블 스캔이 항상 나쁜것은 아니며, 반대로 인덱스 스캔이 항상 좋은 것도 아니라는 사시을 설명하는데 목적이 있다고 한다.