05.테이블 Random 액세스 최소화 튜닝

(1) 인덱스 컬럼 추가

• EMP 테이블의 PK (emp_x01) : deptno + job

select /*+ index(emp emp_x01) */
from   emp
where  deptno = 30
and    sal>=2000

• 이와 같은 경우, 위의 조건을 만족하는 사원이 단 한 명 뿐인데도, 이를 찾기 위해 Table Access는 과도하게 발생한다.
• 인덱스 구성을 deptno + sal 순으로 바꾸면 좋겠으나 실 운경 환경에서는 인덱스 구성을 변경하기가 어렵다.
• 필요한 인덱스를 추가하다보면 인덱스 관리 비용이 증가함은 물론 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있다.
• 이럴 경우 기존 인덱스에 sal 컬럼을 추가한는 것만으로 큰 효과를 거둘 수 있다

*EMP_X01 : deptno + job + sal

• 인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수가 줄었다

h3.(2) PK 인덱스 컬럼 추가

• 단일 테이블을 PK로 액세스할 때는 단 한건만 조회하는 것이므로 테이블 Random 액세스도 단 1회 발생
• NL조인할 때 Inner쪽 (=right side)에서 액세스될 때는 Random 액세스 부하가 만만치 않다
• 특히 Outer 테이블에서 Inner 테이블 쪽으로 조인 액세스가 많은 상황에서 Inner쪽 필터 조건에 의해 버려지는 레코드가 많다면 그 비효율은 매우 심각

select /*+ ordered use_nl(d) */*
from   emp e,
       dept d
where  d.deptno = e.deptno
and    d.loc = 'NEW YORK'

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  NESTED LOOPS  (cr=25 pr=0 pw=0 time=198 us)
     14   TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=111 us)
      3   TABLE ACCESS BY INDEX ROWID DEPT (cr=17 pr=0 pw=0 time=223 us)
     14    INDEX UNIQUE SCAN PK_DEPT (cr=3 pr=0 pw=0 time=108 us)(object id 51150)

• Emp를 기준으로 NL조인하고, 조인에 성공한 14건 중 loc='NEW YORK'인 레코드만 취하므로 최종 결과 집합은 3건 뿐이다.
• DEPT_PK인덱스에 loc 컬럼을 추가하면 불필요한 11번의 Random 액세스를 없앨 수 있지만 PK 인덱스에는 컬럼을 추가 할 수 없다.
• PK컬럼 + 필터조건 컬럼 형태의 Non-Unique 인덱스를 추가
• 인덱스가 없다면 값이 입력될 때 마다 테이블 전체를 읽어 중복 값 존재 여부를 체크해야 하기 때문에 PK제약에는 중복 값 확인을 위한 인덱스가 반드시 필요하다.
• 중복체크를 위해 Non-Unique 인덱스를 이용하여 중복 여부를 체크하며 이때는 one-plus 스캔이 발생하는 약간의 비효율이 있을 수 있다.

SQL> alter table dept drop primary key cascade;
테이블이 변경되었습니다.

SQL> create index dept_x01 on dept(deptno,loc);
인덱스가 생성되었습니다.

SQL> alter table dept add
  2  constraint dept_pk primary key (deptno) using index dept_x01;
테이블이 변경되었습니다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         14          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.03          0         14          0           3

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=97 us)
     18   NESTED LOOPS  (cr=12 pr=0 pw=0 time=888 us)
     14    TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=66 us)
      3    INDEX RANGE SCAN DEPT_X01 (cr=4 pr=0 pw=0 time=46 us)(object id 52924)

• PK 제약을 위해 사용되는 인덱스는 PK제약 순서와 서로 일치하지 않아도 상관없다. 중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 된다.
• PK 구성 고객번호, 상품번호, 거래일자 - 1~3번 인덱스는 PK 제약을 위해 사용 가능, 4는 비허용
  1. 거래일자 + 고객번호 + 상품번호
  2. 상품번호 + 거래일자 + 고객번호 + 거래구분
  3. 고객번호 + 거래일자 + 상품번호 + 매체구분 + 거래구분
  4. 고객번호 + 상품번호 + 거래구분 + 거래일자 ->중복 값 유무는 가능하나 비효율적이기 때문에 허용하지 않음

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

• 인덱스에 컬럼을 추가함으로써 테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수 있다.

SQL> create table t
  2  as
  3  select * from all_objects
  4  order by object_type;
테이블이 생성되었습니다.

SQL> create index t_idx on t(object_type);
인덱스가 생성되었습니다.

SQL> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor

  2  from   user_tables t, user_indexes i
  3  where  t.table_name = 'T'
  4  and    i.table_name = t.table_name;

INDEX_NAME                     TABLE_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------ ---------- -----------------
T_IDX                               705      49788               685

• 클러스터링 팩터가 아주 좋음.

select /*+ index(t t_idx) */count(object_name),count(owner)
from   t
where  object_type >' '

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=824 pr=0 pw=0 time=70919 us)
  49788   TABLE ACCESS BY INDEX ROWID T (cr=824 pr=0 pw=0 time=199217 us)
  49788    INDEX RANGE SCAN T_IDX (cr=139 pr=0 pw=0 time=50232 us)(object id 52926)

• Object_name과 owner는 null허용 컬럼 이므로 이를 읽으려고 테이블을 49,788번 방문하지만 블록 I/O는 685(=824-139)회만 발생하였다. 인덱스 클러스터링 팩터가 좋기 때문이다.

SQL> drop index t_idx;
인덱스가 삭제되었습니다.

SQL> create index t_idx on t(object_type, object_name);
인덱스가 생성되었습니다.

SQL> exec dbms_stats.gather_index_stats(user, 't_idx');
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor

  2  from   user_tables t, user_indexes i
  3  where  t.table_name = 'T'
  4  and    i.table_name = t.table_name;

INDEX_NAME                     TABLE_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------ ---------- -----------------
T_IDX                                   705      49788             33572

• Object_type과 object_name 두 조건으로 조회하는 또 다른 쿼리가 있어 성능 향상을 위해 object_name을 추가 하였다.
• 인덱스 내에서 키 값이 같은 레코드는 rowed 순으로 정렬되므로, 여기에 변별력이 좋은 object_name 같은 컬럼을 추가하면 rowed 이전에 object_name 순으로 정렬되므로 클러스터링 factor가 33,572로 나빠졌다.

select /*+ index(t t_idx) */ count(object_name), count(owner) from t
where  object_type >= ' '

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=33880 pr=0 pw=0 time=125177 us)
  49788   TABLE ACCESS BY INDEX ROWID T (cr=33880 pr=0 pw=0 time=249000 us)
  49788    INDEX RANGE SCAN T_IDX (cr=308 pr=0 pw=0 time=50251 us)(object id 52927)

• 인덱스 스캔 단계에서 블록 I/O횟수가 139에서 308로 증가하였고, 테이블 Random Acess에서의 블록 I/O 횟수는 685(=824-139) 에서 33,672(33880-308) 으로 증가
  -> 인덱스에 컬럼을 추가 했더니 기존에 사용하던 쿼리 성능이 매우 나빠졌다
• 인덱스 내에서 키 값이 같은 레코드는 rowid 순으로 정렬되는데, 변별력이 좋은 object_name 같은 컬럼을 추가하면 rowid 이전에 object_name 순으로 정렬되므로 클러스터링 팩터가 나빠짐
• Object_type 처럼 변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의를 기울여야 한다.

(4) 인덱스만 읽고 처리

• Random액세스가 아무리 많더라도 필터조건에 의해 버려지는 레코드가 없는 경우에는 아예 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함 시키는 방법을 고려할 수 있다
• 이것을 'Covered Index'라고 부르고, 인덱스만 읽고 처리하는 쿼리를 'Covered 쿼리'라고 한다.

(5) 버퍼 pinning 효과 활용

• 오라클의 경우, 한번 입력된 테이블 레코드는 절대 rowid가 바뀌지 않는다. 즉, 레코드 이동이 발생하지 않는다.

• 인라인 뷰에서 읽은 rowid 값을 이용해 테이블을 액세스하는 것도 가능

• emp_pk 인덱스 전체를 스캔해 얻은 레코드를 rowid 순으로 정렬한 다음(> 이 중간집합의 CF는 가장 완벽하게 좋은 상태가 됨) 한 건씩 순차적으로 (> NL조인 방식) emp 테이블을 액세스가 일어 난다.
• Buffer Pinning 효과까지 나타난다면 한 번 액세스로 블록 안에 있는 모든 레코드를 읽어들이는 셈
• 10g에서는 발생하지 않지만, 11g에서는 Buffer Pinning 효과가 나타난다

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

• 테이블 데이터가 무작위로 입력되는 반면, 인덱스는 정해진 키(Key)순으로 정렬되기 때문에 대개 CF가 좋지 않음
• CF가 나쁜 인덱스일 경우, 해당 인덱스 기준으로 테이블을 재생성함으로써 CF를 인위적으로 좋게 만드는 방법 고려 가능, 그 효과는 매우 극적
• 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬 하면 다른 인덱스의 CF가 나빠질 수 있음
  -> CF를 높일 목적으로 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준 / 다른 인덱스를 사용하는 중요한 쿼리 성능 영향 반드시 체크

차세대 시스템 구축 시 주의사항

• 기존보다 성능이 못한 경우가 의외로 많음
• 과거 시스템으로부터 데이터를 이관하는 과정에서 CF가 오히려 나빠지는 경우
• 기존 운영시에는 트랜잭션이 발생하는 순서대로 데이터가 입력, 이관할 때는 병렬쿼리를 많이 활용하여 데이터를 무작위로 흩어놓는 경향
• 애플리케이션 전반에 걸쳐 테이블 Random I/O 횟수를 증가, 디스크 I/O 발생량과 경합 증가요인
  --> 데이터 이관 시에는 ASIS 대비 TOBE 시스템의 CF가 나빠지지 않았는지 조사하고 결과에 따라 적절한 조치 필요!