메뉴 건너뛰기

bysql.net

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

2011.02.25 06:58

suspace 조회 수:6970

 

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  |        |    0.010   |      0.011     |        |        |         |      |
      | Execute |       |    0.020   |      0.018     |        |        |         |      |
      |  Fetch  |    21    |   18.780   |     96.381    |   11948   |  692668   |       |   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  |        |    0.000   |      0.000     |        |        |         |      |
      | Execute |        |    0.000   |      0.000     |        |        |         |      |
      |  Fetch  |    21    |    1.700   |      1.728     |        |   7223   |         |   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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^