-- 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하기 때문에 현재
응답속도가 느린데 이를 어떻게 개선할지.....
내용을 먼저 정리해 드리자면 다음과 같습니다.
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에 대해 간략하게 그림으로 설명드리겠습니다.
그림에서 보시는것처럼 같은 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’이시면
스칼라 서브쿼리를 사용하셔서 응답시간을 줄이실 수 있을거라 생각합니다.
출처 : http://www.gurubee.net/article/14081
댓글 없음:
댓글 쓰기