권순용의 DB 이야기
문제 SQL 분석 2 0 1 4,099

by axiom 튜닝방법론 문제SQL분석 [2014.03.10]


데이터베이스를 관리하다 보면 많은 장애가 발생하게 된다. 데이터베이스의 장애는 물리적 이유로 발생하는 경우도 있지만 성능저하에 의해 발생하는 경우가 많다.

대부분의 경우 문제 SQL에 의해 성능 저하가 발생하는데, 이런 경우 문제 SQL을 수집,분석해 최적화 한다면 해당 시스템의 장애를 해결할 수 있다.

SQL의 수집과 분석에 대해서는 앞서 확인한 바 있다. 이번 글에서는 수집하고 분석한 SQL에 튜닝을 수행하는 방법을 알아보자.

튜닝 전 SQL

먼저, 지난 강의에 언급했던 튜닝 전 SQL을 확인해 보자. [리스트 1]은 목록 쿼리의 형식을 가지고 있는 SQL로 해당SQL은 [리스트 2]와 같이 실행 계획이 생성된다.

  • [리스트 1] 목록 쿼리의 형식을 가지고 있는 SQL
  • SELECT query_space, query_count
      FROM 
         ( SELECT query, query_count, query_space,
                  CEIL(rownum / 10) pagenum
            FROM
               (
                 SELECT /*+ INDEX(query_rank_stat_total query_rank_stat_total_pk ) */
                        query, SUM(query_count) query_count,
                        MIN(query_space) query_space
                   FROM rank_total
                  WHERE st_date BETWEEN TO_CHAR(SYSDATE-2,'YYYYMMDD') 
                                    AND TO_CHAR(SYSDATE, 'YYYYMMDD')
                    AND query_count > 0
                  GROUP BY query 
                  ORDER BY query_count DESC 
               )
           WHERE ROWNUM <= 10 * 1 
         )
     WHERE pagenum = 1  
    

  • [리스트 2] 실행 계획 생성
  • call    count  cpu      elapsed    disk       query      current rows
    ------- ------ -------- ---------- ---------- ---------- ------- ----
    Parse        1     0.00       0.00          0          0       0    0
    Execute      1     0.00       0.00          0          0       0    0
    Fetch        2    17.51      31.97      38844      40926       0   10
    ------- ------ -------- ---------- ---------- ---------- ------- ----
    total        4    17.51      31.98      38844      40926       0   10
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 5
    
    Rows    Row Source Operation
    ------- -------------------------------------------------
         10 VIEW
         10   COUNT STOPKEY
         10     VIEW
         10       SORT ORDER BY STOPKEY
      15999         SORT GROUP BY
      21327           FILTER
      21327             TABLE ACCESS BY INDEX ROWID RANK_TOTAL
      21327               INDEX RANGE SCAN RANK_DATE_TOTAL_IDX
    (object id 96638)
    

[리스트 2]의 SQL에 대한 튜닝을 확인해 보자.

튜닝 후 SQL

튜닝 후 SQL은 [리스트 3]와 같이 될 것이다.

  • [리스트 3] 튜닝 후 SQL
  • SELECT query, query_count,
         ( SELECT MIN(query_space) 
             FROM talkro.rank_total b
            WHERE c.query = b.query
         ) query_space, pagenum
      FROM
         ( SELECT query, query_count, CEIL(ROWNUM / 10) pagenum
             FROM 
                ( SELECT /*+ INDEX(A (ST_DATE,QUERY_COUNT,QUERY)) */
                         query, SUM(query_count) query_count
                    FROM talkro.rank_total a
                   WHERE st_date BETWEEN TO_CHAR(SYSDATE-2, 'YYYYMMDD') 
                                     AND TO_CHAR(SYSDATE, 'YYYYMMDD')
                     AND query_count > 0
                   GROUP BY query
                  ORDER BY query_count DESC
               )
           WHERE ROWNUM <= 10
         ) C
     WHERE pagenum = 1
    

[리스트 3]와 같이 변경한 SQL의 실행 성능 데이터는 [리스트 4] 와 같다.

  • [리스트 4] 변경된 성능 테이터
  • call    count  cpu      elapsed    disk       query      current rows
    ------- ------ -------- ---------- ---------- ---------- ------- ----
    Parse        1     0.00       0.00          0          0       0    0
    Execute      1     0.00       0.00          0          0       0    0
    Fetch        2     0.77       0.77          0       1311       0   10
    ------- ------ -------- ---------- ---------- ---------- ------- ----
    total        4     0.77       0.77          0       1311       0   10
    
    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 5
    
    Rows    Row Source Operation
    ------- -------------------------------------------------
         10 SORT AGGREGATE
         10   FIRST ROW
         10     INDEX RANGE SCAN (MIN/MAX) 111_IDX2 (object id 182064)
         10 VIEW
         10   COUNT STOPKEY
         10     VIEW
         10       SORT ORDER BY STOPKEY
     103890         SORT GROUP BY
     268497           INDEX RANGE SCAN 0111_IDX1 (object id 182063)
    

[리스트 4]에서 확인할 수 있듯이 튜닝 전 SQL은 31.98초였지만 튜닝 후에는 0.77초에 해당하게 된다. 튜닝에 적용한 방법은 다음과 같다.

  • - 전체 데이터 처리 방식과 랜덤 액세스를 통한 성능 저하를 인덱스 스캔만을 이용해 테이블 액세스인 랜덤 액세스 최소화
  • - 인덱스 생성 : 랜덤 액세스 최소화를 위해 인덱스 생성
  • CREATE INDEX RANK_QUERY_TOTAL_IDX ON RANK_TOTAL (QUERY,QUERY_SPACE); 및
  • CREATE INDEX RANK_TOTAL_IDX1 ON RANK_TOTAL (ST_DATE,QUERY_COUNT,QUERY,QUERY_SPACE);

이런 튜닝 방법을 통해 40배 정도 성능 향상을 기대할 수 있는 것처럼 SQL 튜닝은 해당 시스템 성능에 많은 영향을 미친다.

- 강좌 URL : http://www.gurubee.net/lecture/2703

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 신화씨앤씨 [2014.09.12 14:34:12]

감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입