문제 SQL 분류

문제 SQL을 수집했다면 해당 SQL을 분석하는 것이 필요하다. 문제 SQL을 분석하는 것은 여러 가지 요소를 확인하게 될 것이다. 그 중에 첫 번째는 다음과 같은 항목이다.

• - Online SQL : Online SQL은 업무 중에 실시간으로 수행되는 SQL을 의미한다. 그렇기 때문에 Buffer Gets는 많지 않을 가능성이 높고 수행 횟수는 매우 클 가능성이 높다.
• - Batch SQL : Batch SQL은 업무 중에 실시간이 아닌 어떤 주기적으로 수행하는 SQL을 의미하며 일반적으로 대용량 데이터를 처리하게 되므로 Buffer Gets는 높으며 수행횟수는 높지 않을 것이다.

Online SQL인지 아니면 Batch SQL인지를 수집한 SQL에서 어떻게 판단해야 할 것인가?

SQL 분석에 있어서 가장 중요한 부분은 이 둘을 구분하는 것이며, 이는 가장 먼저 해야 할 일일 것이다. 두 가지 종류의 SQL을 분류하는 수단의 하나는 수행 횟수다.

시간당 수행횟수가 많은 SQL은 Online SQL이 될 가능성이 높을 것이다. 물론 Batch SQL의 경우도 그런 경우가 발생할 수도 있지만 수행횟수가 많은 SQL은 시스템 전반적인 성능에 문제를 발생시킬 수 있게 된다.

이와 같이 SQL에 대해 분류됐다면 우선적으로는 Online SQL을 분석하는 것이 필요하다. 추후로 중요한 것은 자주 수행되는 중요 Batch SQL이 될 것이다.

물론 시간이 많다면 모든 SQL을 분석하는 것도 바람직하다. 하지만 항상 시간이 부족하기 때문에 우선순위를 정하는 것이 매우 중요하다.

문제 SQL 분석

제 SQL을 분류했다면 이제는 본격적으로 분석을 수행해야 할 것이다. 그렇다면 어떤 것을 가지고 SQL을 분석할 것인가? 이에 대해 다음과 같은 항목을 분석해야 할 것이다.

• - 인덱스 사용 유무
• - 사용하는 조인 방식
• - 테이블 액세스 방식
• - SQL 작성 방식
• - 데이터의 흐름

이와 같은 부분을 정확히 분석해야만 해당 SQL에 대한 정확한 분석이 이뤄지고 성능을 향상시킬 수 있게 된다. [리스트 1]의 예제를 확인해 보자.

• [리스트 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  
  

[리스트 1]은 목록 쿼리의 형식을 가지고 있는 SQL로, 해당 SQL은 [리스트 2]와 같이 실행 계획이 생성된다.

• [리스트 2] 실행 계획 생성

• 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)
  

해당 SQL을 분석해보면 어떤 부분이 문제인지 확인할 수 있겠는가? 앞서 언급한 항목을 하나하나 확인해 보자.

• - 인덱스 사용 유무 : 현재 RANK_TOTAL 테이블을 액세스함에 있어서 인덱스를 이용하고 있으며 인덱스는 QUERY+ST_DATE로 구성돼 있는 인덱스를 이용하고 있다.
• - 사용하는 조인 방식 : 현재 조인은 발생하지 않고 있다.
• - 테이블 액세스 방식 : 인덱스 액세스 후 테이블을 액세스하는 전형적인 방식이다.
• - SQL 작성 방식 : SQL 작성은 현재 단계에서는 중복 테이블 액세스 등이 없다.
• - 데이터의 흐름 : 계속 감소하는 형식의 데이터 흐름이다.

그렇다면 해당 SQL은 분석 항목 확인에서 그렇게 큰 문제가 발생하지 않는 것처럼 보인다. 하지만 가장 큰 문제는 테이블 액세스 방식에 있다. 물론 데이터 액세스에는 이상이 없지만 해당 SQL은 Online SQL이며 자주 수행되는 SQL이기 때문에 어떻게 해서라도 더 최적화해야 하기 때문이다.

해당 SQL의 성능 데이터는 [리스트 3]과 같다.

• [리스트 3] 성능 데이터

• 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
  

해당 SQL은 문제가 없다면 1초 미만으로 결과가 추출될 것이다. 하지만 해당 SQL은 문제가 존재해 31초 정도의 수행시간이 소요됐다.

우선적으로 이는 앞서 언급한 인덱스 액세스 후 테이블을 액세스하는 테이블 액세스 방식에 문제가 존재한다.

해당 SQL에 대한 분석은 이와 같으며 해당 SQL에 대한 최적화는 다음 시간에 살펴본다.