권순용의 DB 이야기
문제SQL 수집 1 3 6,564

by axiom 튜닝방법론 V$SQL Elapsed Time Buffer Gets [2014.03.09]


데이터베이스를 운영하다 보면 항상 SQL 성능에 문제가 발생하 게 된다. 이와 같은 SQL 성능 문제가 발생한다면 우리는 SQL 튜닝을 수행할 것이다. SQL 튜닝이라는 것이 어려운 것은 아니다.

하지만 시스템에 문제가 발생했을 때 어떤 SQL을 수집해서 어떻게 튜닝할 것인가는 고민해야 할 문제일 수 있다.

이번 강의에서는 SQL 튜닝에 앞서 SQL 튜닝을 위해 어떤 SQL을 수집할 것 인가에 대해 확인해 보자. 또한 수집한 SQL을 어떻게 튜닝하는 지에 대해서도 확인해 보자.

문제 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을 수집해야 할 것이다.

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

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

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

by 아발란체 [2014.03.11 13:43:45]

얼.. 이런 보물 같은.. SQL 까지...
개발자라 실무에서 쓸 일이 없을 것 같은데... 로컬에서 써야겠네요..
분석하면 개발하는데 많이 도움이 될 것 같습니다.
좋은 강좌 감사합니다.


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

감사합니다


by 권훈 [2018.05.30 11:06:39]

잘 보고 갑니다.

감사합니다.

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