꿈꾸는 개발자, DBA 커뮤니티 구루비

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

대부분의 경우 문제 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 튜닝은 해당 시스템 성능에 많은 영향을 미친다.