04. 테이블 Random 액세스 부하

대량의 데이터를 처리할때 Random 액세스가 가장 큰 부하의 요인으로 작용하는 원인을 설명

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

• 쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스'가 일어남
• Table Access By index ROWID

-- SQL> select * from 고객 where 지역 ='서울';
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는 흔히 '물리적 주소정보'라 일컬어짐
• 오브젝트 번호, 데이터파일 번호, 블록 번호 같은 물리적인 요소로 구성되어짐
  (IOT 내부식별자인 LOGICAL ROWID를 설명 하려는 것이 아니고 일반 heap Table 에서 사용되는 Physical ROWID가 내용적으로 논리적의미를 지니고 있다는 의미임)
• rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문에 보는 시각에 따라 '논리적 주소정보' 라고도 표현하기도 함

메인 메모리 DB와의 비교
메인 메모리 DB(MMDB)

• 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB
• 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼 캐시로 로딩하고 이어서 인덱스를 실시간으로 만듬.
  오라클 처럼 디스크상의 주소정보를 담는게 아니라, 인덱스는 메모리상의 주소정보, 즉포인터(Pointer)를 담음.
• 메모리상에서 포인터 만큼 빠른 방법은 없으며, 따라서 테이블을 액세스하는 데 대한 비용이 오라클과 비교할 수 없을 정도로 낮음.
• 오라클은 테이터블럭이 수시로 버퍼 캐시에서 밀려났다가 다시 캐싱되므로, 인덱스에서 직접 포인터를 연결할 수 없는 구조임
• 디스크 상의 블록 위치 정보를 해시 키 값으로 삼아 해싱 알고리즘을 통해 버퍼 블럭을 찾음.

rowid는 우편주소에 해당

• 예를 들면 오라클 rowid는 우편주소에 해당하며, 우편주소는 우체부 아저씨가 일일이 찾아다니는 구조
• 메인 메모리 DB는 전화번호에 해당하며, 물리적으로 연결되어 전화번호만 누르면 곧바로 상대를 찾을 수 있는 구조

인덱스 rowid에 의한 테이블 액세스 구조

• 오라클도 포인터로 빠르게 액세스하는 버퍼 Pinning 기법을 사용하지만 반복적으로 읽힐 가능성이 큰 블록에 대해서만 일부 적용.
• 인덱스 rowid는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통한 테이블 액세스는 고비용 구조.
• 모든 데이터가 메모리에 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA(Data Block Address)를 해싱하고 래치 획득 과정을 반복해야 함.

-고비용임을 강조하기 위한 설명 1권에서 이미 다룬 내용

(2) 인덱스 클러스터링 팩터
군집성 계수(= 데이터가 모여있는 정도)

• 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미함.
• 인덱스 클러스터링 팩터가 가장 좋은 상태는 인덱스 레코드 정렬 순서와 거기서 가르키는 테이블 레코드 정렬 순서가 100%일치하는 것임.
• 인덱스 클러스터링 팩터가 좋지 않은 상태는 인덱스 레코드 정렬 순화 테이블 레코드 정렬 순서가 전혀 일치 하지 않는 것임.

클러스터링 팩터 조회

-- 테이블 레코드가 object_id 순으로 입력되도록 함
create table t
as
select * from all_objects
order by object_id; ==> 테이블 레코드가 Object_id 순으로 입력되도록 함

create index t_object_id_idx on t(object_id);

create index t_object_name_idx on t(object_name);

-- 통계정보 수집
exec dbms_stats.gather_table_stats(user, 'T');

select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor
from   user_tables t, user_indexes i
where  t.table_name = 'T'
and    i.table_name = t.table_name;

INDEX_NAME                     TABLE_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------ ---------- -----------------
T_OBJECT_ID_IDX                         709      50093               689
T_OBJECT_NAME_IDX                       709      50093            25072

• clustering_factor 수치가 테이블 블록(table_blocks)에 가까울수록 데이터가 잘 정렬돼 있음을 의미함. CLUSTERING_FACTOR 수 만큼 Random I/O를 발생하면 전체 데이터를 액세스 할 수 있음
• 레코드 개수(num_rows)에 가까울수록 흩어져 있음을 의미함.
  {tip}
• 인덱스 통계를 수집할 때 clustering_factor 계산을 위해 오라클이 사용하는 로직
  1. counter 변수 하나 선언
  2. 인덱스 리프 블록을 처음부터 끝까지 스캔하면서 인덱스 rowid로부터 블록 번호를 취한다.
  3. 현재 읽고 있는 인덱스 레코드의 블록 번호가 바로 직전에 읽은 레코드의 블록 번호와 다를때마다 counter 변수 값을 1씩 증가
  4. 스캔을 완료하고서, 최종 counter 변수 값을 clustering_factor로서 인덱스 통계에 저장

• 측정된 CF 값은 옵티마이저가 (테이블 Full Scan과 비교해) 인덱스 Range Scan을 통한 테이블 액세스 비용을 평가하는 데에 사용

• blevel : 리프 블록에 도달하기 전 읽게 될 브랜치 블록 개수
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
• 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%) |

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

• "인덱스 CF가 좋다"고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것.
• 궁극적으로 물리적 디스크 I/O를 감소시키는 효과 발생.
• I/O는 블록 단위로 이루어지므로 인덱스를 통해 하나의 레코드를 읽으면 같은 블록에 속한 다른 레코드들도 함께 캐싱되는 결과를 가져올 것이고, CF가 좋은 인덱스 였다면 그 레코드들도 가까운 시점에 읽힐 가능성이 높음.
• 인덱스를 스캔하면서 읽은 테이블 블록들의 캐시 히트율이 높아지므로 물리적인 디스크 I/O 횟수가 감소.
• 값이 같은 레코드들이 서로 멀리 떨어져 있다면 논리적으로 더 많은 블록을 읽어야 하므로 물리적인 디스크 I/O 횟수도 같이 증가.
  또 앞서 읽었던 테이블 블록을 다시 방문하고자 할 때 이미 캐시에서 밀려나고 없다면 같은 블록을 디스크에서 여러 번 읽게 되므로 I/O 효율은 더 나빠질 수 있음.

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

• 인덱스 CF는 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수.
  {tip}
• 옵티마이저가 사용하는 비용 계산식은 기본적으로 물리적인 I/O만을 고려(논리적 I/O가 모두 물리적 I/O를 수반한다고 가정)하므로 CF도 궁긍적으로 물리적 I/O 비용을 평가하기 위한 수단.
• 앞서 읽었던 테이블 블록을 다시 읽을 때 실제로는 캐싱된 블록을 읽을 가능성이 훨씬 높아 비현실적.
• 캐싱 효과를 예측하기가 매우 어렵기 때문에 옵티마이저는 CF를 통해 계산된 논리적 I/O 횟수를 그대로 물리적 I/O 횟수로 인정하고
  인덱스 비용을 평가.
• 인덱스 통계의 clustering_factor는 인덱스를 통해 테이블을 액세스할 때 예상되는 논리적 I/O 개수를 더 정확히 표현.

• CF가 좋은 경우

select /*+ index(t t_object_id_idx) */ count(*) from t
where  object_name >= ' '
and    object_id >= 0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=801 pr=0 pw=0 time=115529 us)
  50093    TABLE ACCESS BY INDEX ROWID T (cr=801 pr=0 pw=0 time=1102123 us)
  50093   INDEX RANGE SCAN T_OBJECT_ID_IDX (cr=112 pr=0 pw=0 time=252664 us)

• 테이블 액세스하는 단계에서 읽을 블록수 689(801 - 112)

*CF가 좋지 않은 경우

select /*+ index(t t_object_name_idx ) */ count(*) from t
where  object_name >= ' '
and    object_id >= 0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=25183 pr=0 pw=0 time=153224 us)
  50093    TABLE ACCESS BY INDEX ROWID T (cr=25183 pr=0 pw=0 time=1102085 us)
  50093   INDEX RANGE SCAN T_OBJECT_NAME_IDX (cr=247 pr=0 pw=0 time=252447 us)

• 테이블 액세스하는 단계에서 읽을 블록수 24963(25183 - 247)

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

• 똑 같은 개수의 레코드를 읽는데 인덱스 CF에 따라 논리적인 블록 I/O 개수가 차이 나는 이유는 인덱스를 통해 액세스되는 하나의 테이블 버퍼 블록을 Pinning 하기 때문임.
• p.73 그림 1-18
• 굵은 실선은 해시 체인 래치를 획득하고서 블록을 액세스.
• 첫 번째 인덱스를 통해 테이블 블록을 Pin한 상태에서 두번째 인덱스가 동일한 블럭을 액세스 할때 논리적 I/O가 일어나지 않음.
• 흐린 점선은 래치 획득 없이 버퍼를 Pin한 상태에서 액세스.
• p.74 그림 1-19
• CF가 나쁠 경우 12개의 레코드를 읽기 위해 12 번의 Random block I/O가 발생

(3) 인덱스 손익분기점

• Index Rang Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 인덱스 손익 분기점이라함.
• 인덱스에 의한 액세스가 Full Table Scan 보다 더 느려지게 만드는 가장 핵심적이 두가지 요인
  • 1.인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면 Full Table Scan은 Sequential 액세스
  • 2.디스크 I/O시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용

• 일반적으로 5 ~ 20% 수준에서 결정 되지만 CF에 따라 크게 달라짐.
• 인덱스 CF가 나쁘면 같은 테이블 블록을 여러 번 반복 액세스하면서 논리적I/O 개수가 증가하고 물리적 I/O발생량도 증가하기 때문임.
• 75~77테스트는 CF에 따른 인덱스 스캔과 테이블 풀 스캔의 손익 분기점을 도식화한 그래프임.
• CF가 좋은 경우, CF가 나쁜경우, Full Table Scan 세 가지 상황 기준으로 설명
• 인덱스 손익 분기점 데이터는 상화에 따라 크게 달라지므로 정해진 수치 또는 범위를 정해 놓고 인덱스의 효용성을 판단하면 틀리기 쉬움.
• 테이블 Reorg로의 CF 향상은 최후의 수단으로 사용해야 함.

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

• 1.IOT(Index-Organized Table) : 테이블을 인덱스 구조로 생성하는 것을 말함.
  • 항상 정렬된 상태 유지함.
  • 테이블 레코드를 읽기 위한 추가적인 Random 액세스 불필요함.

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

• 3.파티션닝 : 대량 범위 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝 한다면, Full Table Scan 하더라도 일부 파티션만 읽고 멈추도록 할 수 있음
• 클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 놓지만, 파티셔닝은 세그먼트 단위로 모아 놓는 점이 다름.

문서에 대하여

• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*