Oracle SQL 튜닝 예제 #1 - 집합적 사고 접근법

* 성능 개선율 : (튜닝 전 - 튜닝 후) / 튜닝 후

 

 

[ 튜닝 전 ]

 

  아래 쿼리를 살펴보도록 하겠습니다.

SELECT CNT1, CNT2, CNT3, CNT4, CNT5, CNT6, CNT7, CNT8 
 FROM (
       SELECT (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='0' AND LOG_LVL='1') CNT1
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='1' AND LOG_LVL='1') CNT2
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='2' AND LOG_LVL='1') CNT3
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='3' AND LOG_LVL='1') CNT4
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='4' AND LOG_LVL='1') CNT5
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='5' AND LOG_LVL='1') CNT6
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='6' AND LOG_LVL='1') CNT7
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='7' AND LOG_LVL='1') CNT8
         FROM DUAL
    ) A;

  CHG_LOGS 테이블의 조건별 건수를 출력하는 쿼리입니다.
  ST_CD 값 별 데이터 건수를 계산하기 위해 스칼라 서브 쿼리 안에서 CHG_LOGS 테이블을 액세스하고 있습니다. 문제는 유사한 스칼라 서브 쿼리가 총 8개가 존재한다는 점입니다. 다시 말해서, CHG_LOGS 테이블을 8번 중복해서 액세스 한다는 뜻입니다.

 

  위 쿼리의 AUTOTRACE 결과를 확인해보겠습니다.

  위 쿼리의 실행계획을 살펴보면 아래와 같은 패턴(인덱스 스캔 및 테이블 랜덤 액세스)을 8번 반복해서 수행한 것을 확인할 수 있습니다.

  위와 같은 반복적인 액세스 작업 때문에 과도한 블럭 읽기(109,718블럭)가 발생하고 이로 인하여 쿼리 수행 시간도 길어지게 되었습니다.

  그럼, 어떤 방식으로 중복 액세스를 제거하여 쿼리 성능을 높일 수 있는지 확인해보도록 하겠습니다.

 

 

[ 튜닝 후 ]

 

  아래는 튜닝 후 쿼리입니다.

 

SELECT NVL( SUM( CASE WHEN ST_CD='0' THEN 1 ELSE 0 END), 0) AS CNT1
     , NVL( SUM( CASE WHEN ST_CD='1' THEN 1 ELSE 0 END), 0) AS CNT2
     , NVL( SUM( CASE WHEN ST_CD='2' THEN 1 ELSE 0 END), 0) AS CNT3
     , NVL( SUM( CASE WHEN ST_CD='3' THEN 1 ELSE 0 END), 0) AS CNT4
     , NVL( SUM( CASE WHEN ST_CD='4' THEN 1 ELSE 0 END), 0) AS CNT5
     , NVL( SUM( CASE WHEN ST_CD='5' THEN 1 ELSE 0 END), 0) AS CNT6
     , NVL( SUM( CASE WHEN ST_CD='6' THEN 1 ELSE 0 END), 0) AS CNT7
     , NVL( SUM( CASE WHEN ST_CD='7' THEN 1 ELSE 0 END), 0) AS CNT8
  FROM CHG_LOGS A
 WHERE REG_DT=:B1
   AND LOG_LVL='1';

  중복 액세스를 피하기 위해 스칼라 서브 쿼리를 제거하였습니다. CHG_LOGS 테이블은 한 번만 액세스 하도록 변경하였으며, WHERE 절에는 각 스칼라 서브 쿼리에서 동일하게 적용되었던 조건(REG_DT=:B1 AND LOG_LVL='1')을 입력하였습니다.
  그리고 CASE문을 이용하여 ST_CD 별 데이터 건수를 계산하였습니다.

 

  위와 같이 변경된 쿼리의 AUTOTRACE 결과는 아래와 같습니다.

 

  실행계획을 살펴보면 IX_CHG_LOG 인덱스와 CHG_LOGS 테이블을 한번씩 액세스하고 있는 것을 확인하실 수 있습니다. 액세스 중복이 제거되어 실행시간(0.02초)과 블럭 읽기(13,714블럭)가 감소된 것을 확인하실 수 있을 것입니다. 물론, 중복 제거의 방법으로 함수를 사용하여 CPU의 사용량이 증가하게 됩니다. 하지만, 쿼리에서 가장 큰 부하는 블럭 읽기이기 때문에 부하 측면에서 CPU 사용량은 상대적으로 낮은 비용이라고 판단할 수 있습니다.

 

  지금까지 살펴봤던 SQL 튜닝 방법이 집합적 사고의 한 예라고 볼 수 있습니다. 데이터 블럭 하나하나가 퍼즐 조각이며, 각 퍼즐 조각을 최대한 적게 활용하여 멋진 작품인 결과물을 만들어내는 게 집합적 사고에 의한 쿼리문 작성인 거죠. 많은 분들이 시간적 여유를 갖고 적은 퍼즐 조각으로 멋진 작품을 탄생시키는 기쁨을 느껴보셨으면 좋겠습니다.