안녕하세요. 튜닝전략 4번째 ’저장형 함수를 사용할 때 튜닝방법’ 퀴즈를 냈던 강정식입니다.

문제의 내용을 다시한번 살펴보면 다음과 같습니다.

-- 1. 수행쿼리 
SELECT PRODUCT_CD,
       PRODUCT_NAME,
       SUM(GET_AVG_STOCK(PRODUCT_CD, :B1, :B2)) AVG_STOCK
FROM   PRODUCT
WHERE  CATEGORY_CD = ’20’
GROUP BY PRODUCT_CD,
         PRODUCT_NAME
;

-- 2. ’GET_AVG_STOCK’ Stored_Function
CREATE OR REPLACE FUNCTION GET_AVG_STOCK(p_start_date IN DATE,
                                         p_end_date   IN DATE,
                                         p_product_cd IN VARCHAR2) RETURN  NUMBER 
AS
    v_ret_val NUMBER;
    
    BEGIN
        SELECT SUM(STOCK_QTY) / (V_START_DATE . V_END_DATE))
        INTO   v_ret_val
        FROM   PROD_STOCK
        WHERE  PRODUCT_CD = p_product_cd
        AND    STOCK_DATE BETWEEN p_start_date 
                          AND     p_end_date;                          
    EXCEPTION 
        WHEN OTHERS THEN
            v_ret_val := 0;
    END GET_AVG_STOCK;
    
    RETURN RET_VAL;
END GET_AVG_STOCK;

대략 1,000만건의 데이터가 들어있는 PRODUCT 테이블에서 CATEGORY_CD가 20인 10만건의 데이터를 GROUP BY하여
SUM값을 가져올 때 내부적으로 GET_AVG_STOCK 펑션을 10만번 Call하기 때문에 현재 응답속도가 느린데 이를
어떻게 개선할지에 대해 퀴즈를 드렸습니다.

아래 리플에 여러 답변을 달아주셨고 모두 정답에 근접하게 올려주셨습니다.

일단 정답이라는 표현보다는 솔루션으로 대체를 하겠습니다.

아래 ’손님’께서 올려주신 내용에 여러 솔루션을 올려주셨는데 그 중 제가 말씀드릴려고 했던 것은 
’Function을 Scalar Subquery화 하기’ 였습니다. 

이에 대한 내용을 먼저 정리해 드리자면 다음과 같습니다.

1) Function을 Scalar Subquery화 하기
    - Function을 Scalar Subquery로 사용하면 동일한 OUTPUT을 One-Buffer가 아닌 Multi-Buffer에 저장함
    
2) Deterministic Function 사용하기   
    - Oracle 10.2 버전부터 사용 가능 
    - Function을 Deterministic 으로 선언하면 같은 INPUT값에 대해서는 OUTPUT도 동일한 결과로 인식하여 
      FUNCTION Call을 안함 
    
여기서 중요하게 봐야할 포인트가 바로 스칼라 서브쿼리나 DETERMINISTIC Function 모두 Multi-Buffer에 저장을 하여
같은 값들에 대해서는 ’Function Call’을 안한다는 것입니다.

9i까지만 해도 실행계획에서 FILTER처리가 되면 One-Buffer 효과를 볼 수 있어서 재사용을 할 수 있었는데요.
테스트 결과 10g R2에서는 One-Buffer를 지원안하고 Multi-Buffer를 지원하는 것으로 확인되었습니다.

여기서 One-Buffer에 대해 간략하게 그림으로 설명드리겠습니다.
(참조 - 대용량 데이터베이스 솔루션II(Created_By 이현숙))

그림에서 보시는것처럼 같은 FILTER처리를 하면 이전값과 다음값이 같을 경우 One-Buffer를 이용하여 액세스를 
안하고 처리를 합니다.
여기서 중요한것은 One-Buffer만 사용할 수 있기 때문에 이전값과 다음값의 비교만 가능해서 만약 FILTER 처리를 
하는 값이 정렬이 안되어 있을경우 효과를 크게 못본다는 것입니다.

이 개념은 동일하게 9i버전까지 패키지콜에서도 동일하게 사용된 개념인데요.
이것이 ’Oracle 10g R2’에서부터는 Multi-Buffer를 지원하는 것으로 확인되었습니다.

또 하나 ’Oracle 10g’에서 새롭게 바뀐점은 바로 ’Fast Dual’ Optimizer Plan을 사용한다는 것입니다.

이 내용 또한 스크랩을 해서 소개해 드리겠습니다.

이제 스칼라 서브쿼리를 적용하여 문제의 쿼리를 접근해 보겠습니다.

-- 1. 수정 전
SELECT PRODUCT_CD,
       PRODUCT_NAME,
       SUM(GET_AVG_STOCK(PRODUCT_CD, :B1, :B2)) AVG_STOCK
FROM   PRODUCT
WHERE  CATEGORY_CD = ’20’
GROUP BY PRODUCT_CD,
         PRODUCT_NAME


-- 2. 수정 후(스칼라 서브쿼리 적용)
SELECT PRODUCT_CD,
       PRODUCT_NAME,
       SUM((SELECT GET_AVG_STOCK(PRODUCT_CD, :B1, :B2)
            FROM   DUAL)) AVG_STOCK
FROM   PRODUCT
WHERE  CATEGORY_CD = ’20’
GROUP BY PRODUCT_CD,
         PRODUCT_NAME
;

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS  
   1    0   FAST DUAL 
   2    0   SORT (GROUP BY)
   3    2     TABLE ACCESS (BY INDEX ROWID) OF ’PRODUCT’ (TABLE)  
   4    3       INDEX (RANGE SCAN) OF ’PRODUCT_N1’ (INDEX)    
   
보시는것처럼 패키지부분을 스칼라 서브쿼리로 바꾼 결과 실행계획에 ’FAST DUAL’이 나온것을 보실 수 있습니다.
즉, 2가지가 변경이 되었는데요. 

1) 패키지의 OUTPUT내용을 Multi-Buffer에 저장하여 같은값들에 대해서는 ’Function Call’을 안함
2) ’Function’에 대해서 ’FAST DUAL’로 연산하기 때문에 빠른 응답속도를 보임

실제 ’Function’에서 내부적으로 돌아가는 부분을 Trace보면 다음과 같습니다.

-- 1. 수정 전
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute 100000   18.000       35.000          0          0          0          0
Fetch   100000   98.000      195.000       2317    8295211         40        500
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total   400001  116.000      230.000       2317    8295211         40        500

-- 2. 수정 후
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute   1000    4.000        5.000          0          0          0          0
Fetch     1000   17.000       18.000        250     480000          0        500
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2001   21.000       23.000        250     480000          0        500

여기선 가상으로 Count를 1000으로 잡았는데요. 실제 업무에서도 같은 값이 계속 발생하면 위에서 보신것처럼
Count를 줄여서 응답시간을 획기적으로 줄일 수 있습니다.

이 글을 읽으시는 개발자 분들 중에서도 현재 사용하는 오라클 버전이 ’10g’이시면 스칼라 서브쿼리를 사용하셔서
응답시간을 줄이실 수 있을거라 생각합니다.

이상 글을 마치며 바로 위에서 Multi-Buffer에 대해 테스트 한 내용으로 글을 남겨드리겠습니다.

감사합니다.