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

 

(1) 인덱스 컬럼 추가

• 테이블 액세스를 하고 필터조건에 의해 버려지는 레코드가 상당한 경우
  
  
• 사례
  - '서비스번호' 로 구성된 단일 컬럼 인덱스(로밍렌탈_N2)
  
  select 렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시,,방문국가코드1, 방문국가코드2, 방문국가코드3, 로밍승인번호, 자동로밍여부
  from 로밍렌탈
  where 서비스번호 like ‘010%’
  and 사용여부 = ‘Y’
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |    1909 | TABLE ACCESS BY INDEX ROWID 로밍렌탈 (cr=266968  pr=27830  pw=0  time= …..)     |
  |  266476 |   INDEX RANGE SCAN 로밍렌탈_N2 (cr=1011  pr=900  pw=0  time=1893462 us)OF ….    |
  -------------------------------------------------------------------------------------------
   
• 튜닝
  - 기존 인덱스 구성을 바꾸면?     
  : 실 운영 환경에서는 기존 인덱스를 사용하는 sql 이 있을 수 있기 때문에 바꾸기 쉽지 않음
  
  - 인덱스를 추가하면?
  : 인덱스가 많아지면 인덱스 관리 비용 증가, DML 부하에 따른 트랜잭션 성능 저하
  
  - 기존 인덱스에 컬럼 추가
  : 인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수 감소
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |    1909 | TABLE ACCESS BY INDEX ROWID 로밍렌탈 (cr=2902  pr=0  pw=0  time= …..)           |
  |    1909 |   INDEX RANGE SCAN 로밍렌탈_N2 (cr=1001  pr=0  pw=0  time=198557 us)OF ….       |
  ------------------------------------------------------------------------------------------- 

 

 

 

 

(2) PK 인덱스에 컬럼 추가

• NL 조인할 때
• 사례
  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=422 us)    
  |      14 |   TABLE ACCESS FULL EMP (cr=8  pr=0  pw=0  time=139 us)
  |       3 |   TABLE ACCESS BY INDEX ROWID DEPT (cr=17  pr=0  pw=0  time=568 us)    
  |      14 |     INDEX UNIQUE SCAN DEPT_PK (cr=3  pr=0  pw=0  time=255 us)   
  ------------------------------------------------------------------------------------------- 
  
  ※ 참고
  ONE PLUS SCAN
  RANGE LOOKUP의 경우 발생하는 연결시도 횟수만큼의 LOOKUP INDEX 스캔량을 의미한다. UNIQUE LOOKUP이 아닌 경우, 매 연결시도마다 한번 더 인덱스를 스캔해야 하므로 발생한다.
  
• 튜닝
  - [PK컬럼 + 필터조건 컬럼]형태의 새로운 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;
  
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |       3 | TABLE ACCESS BY INDEX ROWID DEPT (cr=14  pr=0  pw=0  time=302 us)    
  |      18 |   NESTED LOOPS (cr=12  pr=0  pw=0  time=1483 us)
  |      14 |     TABLE ACCESS FULL EMP (cr=8  pr=0  pw=0  time=145 us)    
  |       3 |     INDEX RANGE SCAN DEPT_X01 (cr=4  pr=0  pw=0  time=220 us)   
  ------------------------------------------------------------------------------------------- 
  
  ※ 참고
  PK제약을 위해 사용되는 인덱스는 PK 제약 순서와 일치하지 않아도 상관없다.
  중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 된다.

 

 

 

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

• 인덱스 컬럼 추가의 부작용
  테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수 있음.
  
  
• 사례
  create index t_idx on t(object_type);
  
  exec dbms_stats.gather_table_stats(user, 't');  -- 통계정보 수집
  
  -------------------------------------------------------------------------------------------
  |   INDEX_NAME  | CL_FACTOR  | TAB_BLKS  | TAB_ROWS   |
  -------------------------------------------------------------------------------------------
  |      T_IDX     |      689     |      709   |   50,137    |
  ------------------------------------------------------------------------------------------- 
  -  클러스터링 팩터가 매우 좋은 상태.
  
  select /*+ index(t t_idx) */ count(object_name), count(owner) from t
  where  object_type > ' ';
  
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |       1 | SORT ACCESGATE (cr=829  pr=0  pw=0  time=37213 us)    
  |  50137 |   TABLE ACCESS BY INDEX ROWID T (cr=829  pr=0  pw=0  time=752094 us)    
  |      14 |     INDEX RANGE SCAN T_IDX (cr=140  pr=0  pw=0  time=151867 us)   
  -------------------------------------------------------------------------------------------
  
  - 테이블 액세스는 50,137이지만 블록 I/O는 689 발생.
  
  
  
• 컬럼 추가 클러스터링 팩터 변화
  
  drop index t_idx;
  
  create index t_idx on t(object_type, object_name);
  
  exec dbms_stats.gather_index_stats(user, 't_idx');
  
  -------------------------------------------------------------------------------------------
  |   INDEX_NAME  | CL_FACTOR  | TAB_BLKS  | TAB_ROWS   |
  -------------------------------------------------------------------------------------------
  |      T_IDX     |      33,843     |      709   |   50,137    |
  ------------------------------------------------------------------------------------------- 
  
  - 컬럼 추가로 인덱스 클러스터링 팩터가 689에서 33,843로 나빠짐.
  - 변별력이 좋은 object_name 같은 컬럼을 추가하면 rowid 이전에 object_name 순으로 정렬되므로 클러스터링 팩터를 나쁘게 만드는 요인으로 작용.
    
• 결론
  변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의.
  
  

 

 

 

 

(4) 인덱스만 읽고 처리

• 테이블 액세스가 아무리 많더라도 필터조건에 의해 버려지는 레코드가 거의 없는 경우
  
  
• 결론   :  테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함
  
  
• 사례
  - 인덱스 구성
  서비스번호_N4  : 식별번호 + 번호상태코드 + 번호사용코드 + 라인번호 + 선호번호여부 + 번호구분코드
  
  
  select *
  from (select 식별번호 || 국번호 || 라인번호 as 서비스번호, 번호사용코드, 번호구분코드, 관리조직id,
     사용자id, decode(선호번호여부, ‘Y’, ‘예’, ‘N’, ‘아니오’, ‘ ‘) 선호번호여부
     from 서비스번호
     where 식별번호 = :식별번호
     and 라인번호 between :라인시작번호 and :라인종료번호
     and 번호상태코드 = ‘AV’
     and 번호사용코드 = :번호사용코드
     and 번호구분코드 = :번호구분코드
     and 선호번호여부 = :선호번호여부
     order by 서비스 번호)
  where rownum <= 500
  
  -------------------------------------------------------------------------------------------
  |   Call  |  Count  |  CPU Time  |  Elapsed Time |   Disk   |  Query  |  Current  |  Rows  |
  -------------------------------------------------------------------------------------------
  |  Parse  |     1    |    0.010   |      0.011     |     0     |     0    |     0     |    0    |
  | Execute |     1    |    0.020   |      0.018     |     0     |     2    |     0     |    0    |
  |  Fetch  |    21    |   18.780   |     96.381    |   11948   |  692668   |    0    |   500   |
  -------------------------------------------------------------------------------------------
     Total       23         18.810         96.410        11948       692668        0         500
  
  
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |       0 | STATEMENT
  |     500 |   COUNT STOPKEY (cr=692666  pr=11948  pw=0  time=96380650 us)
  |     500 |     VIEW (cr=692666  pr=11948  pw=0  time=96380140 us)
  |     500 |       SORT ORDER BY STOPKEY (cr=692666  pr=11948  pw=0  time=96380140 us)
  | 691237 |         FILTER (cr=692666  pr=11948  pw=0  time=65667547 us)
  | 691237 |           TABLE ACCESS BY INDEX ROWID 서비스번호 (cr=692666  pr=11948  pw=…)    
  | 691237 |             INDEX RANGE SCAN 서비스번호_N4 (cr=7223  pr=3731  pw=0  time=…)   
  -------------------------------------------------------------------------------------------
  
  
• 튜닝
  - 인덱스 구성
  서비스번호_N4  : 식별번호 + 번호상태코드 + 번호사용코드 + 라인번호 + 선호번호여부 + 번호구분코드
                   + 국번호 + 관리조직ID + 사용자ID
  
  -------------------------------------------------------------------------------------------
  |   Call  |  Count  |  CPU Time  |  Elapsed Time |   Disk   |  Query  |  Current  |  Rows  |
  -------------------------------------------------------------------------------------------
  |  Parse  |     1    |    0.000   |      0.000     |     0     |     0    |     0     |    0    |
  | Execute |     1    |    0.000   |      0.000     |     0     |     0    |     0     |    0    |
  |  Fetch  |    21    |    1.700   |      1.728     |     0     |   7223   |     0     |   500   |
  -------------------------------------------------------------------------------------------
     Total       21          1.700          1.728           0          7223         0         500
  
  
  -------------------------------------------------------------------------------------------
  |   Rows  | Row Source Operation                                                          |
  -------------------------------------------------------------------------------------------
  |       0 | STATEMENT
  |     500 |   COUNT STOPKEY (cr=7223  pr=11948  pw=0  time=1727339 us)
  |     500 |     VIEW (cr=7223  pr=0  pw=0  time=1726826 us)
  |     500 |       SORT ORDER BY STOPKEY (cr=7223  pr=0  pw=0  time=1726816 us)
  | 691237 |         FILTER (cr=7223  pr=0  pw=0  time=1381986 us)
  | 691237 |           INDEX RANGE SCAN 서비스번호_N4 (cr=7223  pr=0  pw=0  time=691026 us)   
  -------------------------------------------------------------------------------------------
  
  - 인덱스 컬럼이 많아지면 그만큼 DML 속도가 느려지는 측면이 있지만 쿼리의 사용빈도를 감안한다면 얻는 것이 많다.

 

 

(5) 버퍼 Pinning 효과 활용

• 인덱스를 통해 아무리 많은 테이블 레코드를 액세스하더라도 Random 액세스에 의한
  (I/0측면에서의) 비효율은 거의 존재하지 않게 되고, SQL 튜닝 시 가장 자주 사용되는 기법 중 하나가 됨.
  
  ※ 참고
  BUFFER PINNING
  과다한 체인 탐색에 의한 래치 경합을 줄이기 위해 사용. 특정 세션의 동일 SQL 콜(Call)내에서 2번 이상 스캔된 버퍼는 Pinned 상태로 변환된다.
  
  
  
• 사례
  select /*+ ordered use_nl(b) rowid(b) */  b.*
  from (select /*+ index(emp emp_pk) no_merge */  rowid rid
        from emp
        order by rowid) a, emp b
  where b.rowid = a.rid
  
  -------------------------------------------------------------------------------------------
  | Row Source Operation                                                                   |
  -------------------------------------------------------------------------------------------
  0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=14 Bytes=686)
  1   0      NESTED LOOPS (Cost=16 Card=14 Bytes=686)
  2   1        VIEW (Cost=2 Card=14 Bytes=168)
  3   2          SORT (ORDER BY) (Cost=2 Card=14 Bytes=168)
  4   3            INDEX (FULL SCAN) OF ‘EMP_PK’ (INDEX (UNIQUE)) (Cost=1 …)
  5   4       TABLE ACCESS (BY USER ROWID) OF ‘EMP’ (TABLE) (Cost=1 Card=1 …)
  -------------------------------------------------------------------------------------------
  
  
• 10g 에서는 버퍼 Pinning 효과 나타나지 않음.
• 11g 에서는 버퍼 Pinning 효과 나타남.

 

 

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

• 해당 인덱스 기준으로 테이블을 재생성함으로써 CF를 인위적으로 좋게 만드는 방법을 말함.
  
  

- 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 CF가 나빠질 수 있다.  
- 가장 자주 사용되는 인덱스를 기준으로 삼는다.
- 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크한다.
- 테이블과 인덱스를 Rebuild하는 부담이 적고 효과가 확실할 때만 사용하는 것이 바람직하다.

• 사례
  회사에서 가장 많이 조회되고 입력되는 상품거래 테이블이 있다.
  입력은 거래가 일어나는 순서로 데이터가 입력되고 대부분의 쿼리들은 상품번호를 기준으로 조회한다면
  테이블 Random 액세스 부하가 심각하게 된다.
  
  
  
• 튜닝
  상품번호를 선두 키로 갖는 PK인덱스 컬럼 순으로 테이블을 정렬한다면 Random I/O 발생량을 상당히 줄일 수 있다.
  PK인덱스와 상관관계가 높은 인덱스가 있다면 함께 효과를 볼 수 있다.

 

 

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 이주영 (suspace)
  
• 최초작성일: 2011년 02월 27 일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^