4. 테이블 Random 액세스 부하

1. 인덱스 ROWID에 의한 테이블 액세스

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스' 가 일어난다.
실행계획에서는 'TABLE ACCESS (BY INDEX ROWID) 라고 표시된다.

인덱스에 저장돼 있는 rowid는 '물리적 주소정보'라고 보지만, 보는 시각에 따라서는 '논리적 주소정보'라고도 표현한다.
rowid가 물리적 위치정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다.

인덱스 rowid는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통해 테이블 액세스는 고비용 구조이다.
만약 모든 데이터가 메모리에 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA를 해싱하고 래치 획득 과정을 반복해야 하기 때문이며, 동시 액세스가 심할 때는 래치와 버퍼 Lock에 대한 경합까지 발생한다.

2. 인덱스 클러스터링 팩터

클러스터링 팩터(CF)는 '군집성 계수' 쯤으로 번역될 수 있는 용어로서, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋은데, 데이터가 물리적으로 근접해 있다면 흩어져 있을 때보다 데이터를 찾는 속도가 빨라지기 때문이다.

클러스터링 팩터 조회

통계정보를 생성하고 나면 오라클이 계산한 인덱스 CF를 뷰에서 확인할 수 있다.

clustering_factor 수치가 테이블 블록에 가까울수록 데이터가 잘 정렬돼 있음을 의미하고, 레코드 개수에 가까울수록 흩어져 있음을 의미한다.

이런 식으로 측정된 CF 값은 옵티마이저가 Table Full Scan과 비교해 Index Range Scan을 통한 테이블 액세스 비용을 평가하는 데에 사용된다.

인덱스를 이용한 테이블 액세스 비용 계산

비용 = (blevel) + => 인덱스 수직적 탐색 비용
(리프 블록수 * 유효 인덱스 선택도) + => 인덱스 수평적 탐색 비용
(클러스터링 팩터 * 유효 테이블 선택도) => 테이블 Random 액세스 비용

• blevel : 리프 블록에 도달하기 전 읽게 될 브랜치 블록 개수

• 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)

• 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

클러스터링 팩터와 물리적 I/O

오라클에서 I/O는 블록 단위로 이루어지므로 인덱스를 통해 하나의 레코드를 읽으면 같은 블록에 속한 다른 레코드들도 함께 캐싱되는 결과를 가져올 것이고, CF가 좋은 인덱스라면 그 레코드들도 가까운 시점에 읽힐 가능성이 높다.

따라서, 인덱스를 스캔하면서 읽은 테이블 블록들의 캐시 히트율이 높아지므로 물리적인 디스크 I/O 횟수가 감소하게 된다.

반대로 값이 같은 레코드들이 서로 멀리 떨어져 있다면 논리적으로 더 많은 블록을 읽어야 하므로 물리적인 디스크 I/O 횟수도 같이 증가하게된다.

클러스터링 팩터와 논리적 I/O

인덱스 CF는 단적으로 말해, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미한다.

버퍼 Pinning에 의해 논리적 I/O 감소 원리

방금 액세스한 버퍼에 대한 Pin을 즉각 해제하지 않고 데이터베이스 Call 내에서 계속 유지해, 연속된 인덱스 레코드가 같은 블록을 가리킨다면, 래치 획득 과정을 생략하고 버퍼를 Pin한 상태에서 읽기 때문에 논리적인 블록 읽기 횟수가 증가하지 않는다.

3. 인덱스 손익분기점

Index Range Scan 에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 '인덱스 손익분기점' 이라고 부른다.

인덱스에 의한 액세스가 Full Table Scan 보다 느려지는 요인

• 인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan은 Sequential 액세스 방식으로 이루어진다.

• 디스크 I/O 시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용한다.

이런 요인에 의해 인덱스 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정되지만 CF에 따라 크게 달라진다.

인덱스의 CF가 나쁘면 같은 테이블을 여러 번 반복 액세스하면서 논리적 I/O 개수가 증가하고 물리적 I/O 발생량도 증가하기 때문이다.

따라서 CF가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는 1% 미만으로 떨어진다.

인덱스 손익분기점은 인덱스가 항상 좋지 않음을 설명하려는 것이다.
즉 테이블 스캔이 항상 나쁜 것은 아니며, 인덱스 스캔이 항상 좋은 것도 아니다.

손익분기점을 극복하기 위한 기능들

1. IOT (Index-Organized Table)

테이블을 인덱스 구조로 생성하는 것을 말한다.
테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지한다.
그리고 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스를 수직 탐색한 다음에 테이블 레코드를 읽기 위해 추가적인 Random 액세스가 불필요하다.

2. 클러스터 테이블(Clustered Table)

클러스터 테이블은 키 값이 같은 레코드는 같은 블록에 모이도록 저장하기 때문에 테이블 Random 액세스가 키 값별로 한 번씩만 발생한다.
클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다.

3. 파티셔닝 (Partitioning)

읽고자 하는 데이터가 많을 때 인덱스를 사용하지 않는 편이 낫지만, 대용량 테이블을 Full Scan 하는 것도 부담스럽다.
이럴 때, 대량 범위 조건으로 자주 사용되는 커럼 기준으로 케이블을 파티셔닝한다면 Full Table Scan 하더라도 일부 파티션만 읽고 멈추도록 할 수 있다.

클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 놓지만 파티셔닝은 세그먼트 단위로 모아 놓는 점이 다르다.

---

5. 테이블 Random 액세스 최소화

1. 인덱스 컬럼 추가

2. PK 인덱스에 컬럼 추가

PK 인덱스에 컬럼을 추가하면 불필요한 액세스를 줄일 수 있지만 PK 인덱스에는 컬럼을 추가할 수 없다.
그러나보니 [PK컬럼 + 필터조건 컬럼] 형태의 새로운 Non-Unique 인덱스를 추가하는 경우가 많다.
그럴 때 Non-Unique 인덱스를 이용해 PK 제약을 설정한다면 인덱스 개수를 줄일 수 있다.

PK 제약에는 중복 값 확인을 위한 인덱스가 반드시 필요하다.
인덱스가 없다면 값이 입력될 때마다 테이블 전체를 읽어 중복 값 존재 여부를 체크해야 하기 때문이다.
중복 체크를 위해서는 Unique 인덱스가 필요하지만, Non_Unique 인덱스로도 가능하다.

alter table dept drop primary key;

create index dept_x01 on dept(deptno, loc);

alter table dept add constraint dept_pk primary key(deptno) using index dept_x01;

위 쿼리는 기존 PK 인덱스를 삭제하고 dept_x01 인덱스로 PK 제약을 사용하는 것이다.
PK 제약을 위해 사용되는 인덱스는 PK 제약 순서와 서로 일치하지 않아도 상관없다.
중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 된다.

3. 컬럼 추가에 따른 클러스터링 팩터 변화

인덱스 내에서 키 값이 같은 레코드는 rowid 순으로 정랼된다.
그런데 인덱스에 변별력이 좋은 컬럼을 추가하면 rowid 이전에 추가한 컬럼 순으로 정렬되므로 클러스터링 팩터를 나쁘게 만드는 요인으로 작용한다.
결론적으로 변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의를 기울여야 한다.

4. 인덱스만 읽고 처리

테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 어떻게 튜닝해야 할까?

이때는, 아예 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다.
MS-SQL 에서는 이런 인덱스를 'Covered 인덱스', 이런 쿼리를 'Covered 쿼리' 라고 부른다.

5. 버퍼 Pinning 효과 활용

오라클은 한번 입력된 테이블 레코드는 절대 rowid가 바뀌지 않고 레코드 이동이 발생하지 않는다.
따라서 미리 알고 있던 테이블 rowid 값을 이용해 레코드를 조회하는 것이 가능하다.
SQL에 사용자가 직접 rowid값을 조건절에 사용하면 실행계획 상 Table Access (BY USER ROWID) 라고 표시된다.

만약 USER ROWID에 의한 테이블 액세스시에도 버퍼 Pinning 효과가 나타난다면 어떨까?

버퍼 Pinning 효과까지 나타난다면 한 번 액세스로 블록 안에 있는 모든 레코드를 다 읽어 들이는 셈이다.
따라서 인덱스를 통해 아무리 많은 테이블 레코드를 액세스하더라도 Random 액세스에 의한 비효율은 거의 존재하지 않게 된다.

6. 수동으로 클러스터링 팩터 높이기

CF가 나쁜 인덱스를 이용해 많은 양의 데이터를 읽어야 할 때, 가장 튜닝이 어렵다.
그럴 때는, 해당 인덱스 기준으로 테이블을 재생성함으로써 CF를 인위적으로 좋게 만드는 방법이 있다.
인위적으로 CF를 높일 목적으로 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼고, 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해야 된다.