문제 SQL 수집

문제 SQL은 무엇인가? 문제 SQL이라는 것은 시스템에서 자 원 사용이 많은 SQL을 의미하며 그렇기 때문에 전체적으로 영 향을 미치는 SQL이다.

이와 같은 SQL을 빠른 시간에 추출해 튜 닝을 수행해야 할 것이다. 만일 운영 중에 이런 문제가 발생했다 면 시간이 촉박하기 때문에 빠른 시간 내에 처리하는 것이 유리 하다.

이와 같은 문제 SQL을 수집하기 위해서는 몇 가지 기준이 있어야 한다. 이와 같은 기준이 없다면 SQL을 튜닝하고 적용해 도 큰 효과를 기대할 수 없을 것이다.

그렇다면 문제 SQL을 구 분하는 기준은 무엇일까? 문제 SQL을 구분하는 기준이 될 수 있 는 항목은 다음과 같다.

• - Buffer Gets : 해당 SQL이 수행되면서 액세스한 블록의 수이며 V$SQL 등에서는 Executions가 곱해져서 보여진다.
• - Executions : 해당 SQL이 수행된 횟수를 의미하며 변수 처리를 바인 드 변수 처리로 수행하지 않으면 다른 SQL로 집계된다.
• - Elapsed Time : 해당 SQL이 수행된 수행 시간이며 V$SQL에서는 1,000,000으로 나눠야 초가 된다. 물론 Executions로 곱해져 있기 때 문에 Executions로도 나눠야 1회 수행 시 수행 시간이 된다.

이와 같이 세 가지 요소를 기준으로 문제 SQL을 수집하게 된 다. 이 중에서 가장 중요한 항목은 무엇일까?

우리가 문제 SQL 을 수집할 때 항상 고민하는 항목이다. Buffer Gets에서 문제 SQL을 추출해야 할 것인지 아니면 Executions로 수집할 것인지 아니면 Elapsed Time으로 수집해야 할 것인지에 대해 고민하게 된다.

위에서 언급한 모든 항목들은 성능에 많은 영향을 미치게 된 다. 그중 어떤 Factor가 성능에 더 많은 영향을 미치는가를 고려 해야 하며 우리는 한 가지를 선택해야 할 것이다.

이와 같은 기준 이 없다면 문제 SQL을 정확히 찾지 못할 것이다.

그렇다면 더욱 정확하게 문제 SQL을 추출하기 위해 어떤 기 준을 정해야 할까? 많은 사람들은 응답 시간(Elapsed Time)을 문제 SQL의 기준으로 생각한다.

하지만 실제는 실행 횟수가 더 중요하다. 예를 들어 1,000 Block을 액세스하는 쿼리가 1,000,000번 수 행되는 SQL과 100,000 Block을 액세스하는 SQL이 하루 평균 100번 수행된다고 가정하자.

물론 두 SQL은 모두 튜닝 대상이 될 것이다. 또한 2개의 쿼리 중 100,000 Block을 액세스하는 SQL이 응답 속도는 더 많이 소요될 것이다. 하지만 두 SQL의 비교는 다음과 같은 방법이 더 타당성 있게 된다.

• - 1번 SQL : 1,000(Block 액세스양) × 1,000,000(수행횟수) × 8(Block Size - KB) = 8,000,000 MB = 8,000GB = 8TB
• - 2번 SQL : 100,000(Block 액세스량) × 100(수행횟수) × 8(Block Size - KB) = 80000 MB = 80 GB

각각 SQL의 액세스 양은 위와 같이 계산할 수 있다. 이와 같기 때문에 1번 SQL이 2번 SQL보다 더 문제를 발생시키게 된다. 따라서 2개의 SQL 중에 먼저 튜닝해야 되는 SQL은 1번 SQL이 된다.

문제 SQL의 수집은 이와 같이 수행횟수와 Block 액세스의 곱 에 의해 결정된다. 물론 응답시간을 고려하지 않을 수 없다.

하지 만 응답 시간은 시스템의 상황에 따라 그리고 Physical Disk I/O 에 따라 차이가 많이 발생하게 된다. 하지만 다음과 같은 경우에 는 Block 액세스가 정확히 수집되지 않는다.

• - 병렬 프로세싱(Parallel Processing)
• - 해시 조인(Hash Join)

위와 같은 경우에는 Block 액세스량이 정확하지 않기 때문에 문제 SQL로 수집되지 않을 수 있다. 최종적으로 문제 SQL은 Block 액세스와 실행 횟수에 의해 수 집하는 것이 중요하며 또한 Block 액세스가 정확하게 수집되지 않는 SQL에 대해 수집하기 위해 응답 시간도 중요하게 된다.

[리스트1]에서 문제 SQL을 수집하는 SQL을 확인해 보자.

• [리스트1] 문제SQL을 수집하는 SQL

• SELECT /*+ NO_MERGE(v) */
         s.HASH_VALUE
       , MODULE
       , 
       ( SELECT username 
           FROM DBA_USERS 
          WHERE user_id =s.parsing_user_id
        ) username
        , executions
        , ROUND(elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000),1) elapsed_t
        , ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) buffer_gets_per_exec
        , rows_processed
        , sql_text
    FROM 
       ( SELECT /*+ NO_MERGE(V1) */
                MAX(HASH_VALUE) HASH_VALUE
           FROM 
              (
                SELECT HASH_VALUE, SQL_TEXT
                  FROM V$SQL S
                 WHERE 1=1
                   AND ( ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) > 10000
                         OR elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000) >1 )
                   AND (module IS NULL 
                        OR (module NOT LIKE 'TOAD%'
                       AND module NOT LIKE 'Orange%'
                       AND module NOT LIKE 'Golden32.exe%'
                       AND module NOT LIKE 'PL/SQL Developer%'
                       AND module NOT LIKE 'T.O.A.D%'
                       AND UPPER(module) NOT LIKE 'SQL*PLUS%')
                       )
                   AND PARSING_USER_ID IN 
                  ( SELECT user_id
                      FROM DBA_USERS
                     WHERE username NOT IN ('SYS','SYSTEM')
                  )
               ) V1
          GROUP BY SUBSTR(SQL_TEXT,1,150)
       ) v,
         V$SQLAREA s
   WHERE v.hash_value=s.hash_value
    AND parsing_user_id IN 
      ( SELECT user_id
          FROM DBA_USERS
         WHERE username NOT IN ('SYS','SYSTEM')
      )
    AND module NOT LIKE 'SmartSQL%'
  ORDER BY buffer_gets;
  

[리스트 1]에서 중요한 부분은 다음과 같다.

• - (ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,NULL,1, EXECUTIONS),1) > 10000 - 10,000 Block 이상 액세스한 SQL
• - ELAPSED_TIME/(DECODE(EXECUTIONS,0,1,NULL,1,EXECUT IONS)*1000000) >1- 수행시간 1초 이상
• - order by BUFFER_GETS; - Block 액세스에 수행 횟수를 곱한 순으로 결과 추출

이와 같이 응답시간과 액세스한 Block 수 그리고 수행 횟수를 고려해 문제 SQL을 수집해야 할 것이다.