Oracle SQL 튜닝 예제 #2 - PL/SQL 제거

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

 

  비효율적인 PL/SQL(Function)을 제거하여 성능을 높인 쿼리 예제를 살펴보도록 하겠습니다.
  이 포스트에 사용된 예제는 부하를 유발하는 PL/SQL를 보여주기 위해 임의로 작성된 쿼리입니다. 최대한 가독성을 높이기 위해 짧게 작성하고자 노력했으며, 이로 인하여 조금은 억지스러운 면도 있지만 설명을 위한 예제이니 참고 바랍니다.

[ 튜닝 전 ]

 

  먼저 튜닝 전 쿼리를 살펴보도록 하겠습니다.

SELECT SALE_DT
     , PROD_NO
     , FNC_GET_PROD_NM(PROD_NO) AS PROD_NM
  FROM PROD_SALE
 WHERE SALE_DT = '20191201';

  PROD_SALE 테이블 데이터 중 SALE_DT 값이 '20191201'인 데이터의 SALE_DT, PROD_NO, PROD_NM 값을 추출하고 있습니다. 이 중 PROD_NM 값은 FNC_GET_PROD_NM 함수를 호출한 결과 값을 출력합니다.

  위 쿼리에서 사용된 함수 FNC_GET_PROD_NM의 내용은 아래와 같습니다.

CREATE OR REPLACE FUNCTION FNC_GET_PROD_NM (V_PRODNO IN NUMBER) 
          RETURN VARCHAR2
IS
V_PROD_NM VARCHAR2(100);
V_PROD_STTS_CD VARCHAR2(10);
V_RES_NM VARCHAR2(100);
BEGIN
SELECT PROD_NM, PROD_STTS_CD
    INTO V_PROD_NM, V_PROD_STTS_CD
     FROM PROD 
    WHERE PROD_NO = V_PRODNO ;

IF V_PROD_STTS_CD = '10' THEN
  V_RES_NM := '[판매중] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('30','40') THEN
 V_RES_NM := '[판매종료] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('50','60') THEN
 V_RES_NM := '[판매예정] ' || V_PROD_NM;
ELSE
 V_RES_NM := '[기타] ' || V_PROD_NM;
END IF;

RETURN V_RES_NM; 
END; 

  FNC_GET_PROD_NM 함수는 입력받은 인자 값 V_PRODNO에 매칭 되는 상품명(더 정확하는 상품 상태 값과 상품명)을 반환하는 함수입니다. 
  PROD 테이블에서 PROD_NO 컬럼의 값이 입력받은 인자 값 V_PRODNO와 일치되는 데이터의 PROD_NM과 PROD_STTS_CD 값을 추출하여 V_PROD_NM과 V_PROD_STTS_CD 변수에 각각 입력합니다. IF문을 이용해 V_PROD_STTS_CD 값에 따라 상품상태값과 상품명(V_PROD_NM)을 합친 문자열을 V_RES_NM에 대입하고, 마지막에 V_RES_NM 값을 반환하게 됩니다.

  위 쿼리의 AUTOTRACE 결과를 살펴보겠습니다.

  AUTOTRACE의 Statistics를 보면 recursive calls이 과도하게 발생한 것을 확인할 수 있습니다. recursive calls는 오라클 내부적으로 필요한 작업이나 SQL문을 수행할 때 발생합니다. 예를 들어, 데이터를 Insert시 공간이 부족하면 recursive calls로 공간을 동적으로 할당하는 작업을 하게 됩니다. 여기서는 PL/SQL 내의 SQL문을 수행을 위해 recursive calls를 호출하였습니다.

  SELECT절에 사용된 PL/SQL의 실행 횟수는 SELECT 되는 데이터의 건수와 비슷합니다. SELECT되는 각 로우의 값을 입력 인자 값으로 해서 PL/SQL을 수행하게 됩니다. PL/SQL 결과가 캐시에 저장되어 있다면 PL/SQL 수행 없이 캐쉬 내 결과 값을 반환하게 되므로 항상 SELECT 되는 데이터 건수와 동일한 것은 아닙니다.

  PROD_SALE 테이블에서 SALE_DT 값이 '20191201'인 데이터는 251,896건입니다. 251,896건의 recursive calls이 발생했으니 데이터 건수만큼 함수가 호출되었음을 확인할 수 있습니다.
  여기서 가장 큰 문제는 FNC_GET_PROD_NM 함수를 호출할 때마다 함수 내부의 쿼리문을 반복해서 실행하게 된다는 것입니다. SQL 최적화를 위해서는 블럭 액세스를 줄여야 하는데, 함수내 쿼리문을 실행하기 위해 PROD 테이블을 여러 번 액세스하게되어 블럭 액세스(consistent gets)가 과도하게 증가하였습니다.

  위 쿼리를 최적화하기 위해서는 과도한 블럭 액세스를 유발하는 함수를 제거해야 합니다.

 

 

[ 튜닝 후 ]

 

  아래는 위 튜닝 전 쿼리에서 함수 제거를 위해 함수 내 PROD 테이블 조회를 조인으로 풀어낸 쿼리입니다.

SELECT S.SALE_DT
     , S.PROD_NO
     , CASE WHEN PROD_STTS_CD = '10' THEN '[판매중] ' || P.PROD_NM
            WHEN PROD_STTS_CD IN ('30','40') THEN '[판매중] ' || P.PROD_NM
            WHEN PROD_STTS_CD IN ('50','60') THEN '[판매중] ' || P.PROD_NM
            ELSE '[기타] ' || P.PROD_NM END  AS PROD_NM
  FROM PROD_SALE S
     , PROD P
 WHERE S.PROD_NO = P.PROD_NO
   AND SALE_DT = '20191201'
;

  PROD_SALE 테이블의 데이터는 튜닝 전 쿼리처럼 SALE_DT 값이 '20191201'인 데이터만 추출합니다. 그리고 FNC_GET_PROD_NM 함수 대신에 ROD_SALE 테이블과 PROD 테이블을 PROD_NO 키를 이용해 조인을 하고 있습니다. SELECT절의 PROD_NM 항목은 함수 내 연산처럼 PROD_STTS_CD 값에 따라 상태 값을 CASE문을 이용해 분기하여 PROD_STTS_CD에 따른 상품상태값과 상품명(PROD_NM)을 합친 결과로 출력됩니다.

  위 쿼리의 AUTOTRACE 결과 값은 아래와 같습니다.

  PROD_SALE테이블과 PROD테이블을 조인하는 형태로 쿼리를 수정하여 블럭 액세스가 5.3%로(797,850블럭에서 42,664블럭) 감소되었습니다.

  실행계획을 보면 PROD_SALE 테이블과 PROD 테이블을 FULL TABLE SCAN 하여 HASH 조인했음을 알 수 있습니다. 두 테이블 중 크기가 상대적으로 작은 PROD 테이블이 BUILD 테이블이 되어 해쉬 테이블을 만들고 PROD_SALE 테이블이 PROBE 테이블이 되어 해시 조인했음을 확인할 수 있습니다. 

 

  해시 조인을 수행하였기 때문에 튜닝 후 쿼리에서는 PROD 테이블을 한 번만 액세스 하였습니다.
  튜닝 전 쿼리에서는 함수 실행을 위해 SELECT 건수만큼 PROD 테이블을 액세스 하여 과도한 블럭 액세스를 유발하였는데, 쿼리를 조인으로 풀어 이러한 과도한 블럭 액세스를 제거한 것입니다.