-- 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_FunctionCREATE 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을 스칼라 서브쿼리로 하는 것입니다. ( Oracle 10g 이상 )
1. - Function을 Scalar Subquery로 사용하면 동일한 OUTPUT을 One-Buffer가 아닌 Multi-Buffer에 저장함.
2. - Oracle 10.2 버전부터 사용 가능
- Function을 Deterministic 으로 선언하면 같은 INPUT값에 대해서는 OUTPUT도 동일한 결과로 인식하여 FUNCTION Call을 안함
3. - Scalar Subquery를 이용하거나, Deterministc형태로 함수를 생성해준다면, 사용자 정의 함수에 대한 입력값이 적을 시에 아주 뛰어난 효율을 보이게 된다.
* 여기서 중요하게 봐야할 포인트가 바로 스칼라 서브쿼리나 DETERMINISTIC Function 모두 Multi-Buffer에 저장을 하여 같은 값들에 대해서는 ’Function Call’을 안한다는 것입니다.
9i까지만 해도 실행계획에서 FILTER처리가 되면 One-Buffer 효과를 볼 수 있어서 재사용을 할 수 있었는데 10g R2에서는 One-Buffer를 지원안하고 Multi-Buffer를 지원하는 것으로 확인되었습니다.
’Oracle 10g’에서 새롭게 바뀐점은 바로 ’Fast Dual’ Optimizer Plan을 사용한다는 것입니다
SELECT TEST INTO FROM DUAL;
DUAL은 호출될 때마다 새로운 논리적 I/O(Buffer I/O)를 생성합니다. 이 기능은 매우 유용한게 활용욉니다. DUAL은 자주 사용되지만 오라클 코드는 DUAL을 특수한 형태의 테이블로서 취급하며, 일반적인 튜닝 방법은 적용할 수 없다는 문제가 있습니다.
Oracle Database 10g 에서라면 이에 대한 걱정을 할 필요가 없습니다. DUAL이 특수한 테이블이기 때문에, 논리적 I/O를 나타내는 consistent gets의 값도 줄이들며, optimization plan도 다른 형태로 나타납니다.
Oracle 9i에서 사용되는 DUAL의 FAST TABLE SCAN 대신, 10g 에서는 FAST DUAL optimization plan을 사용하고 있다는 점을 주목하시기 바랍니다. 이러한 기능 개선을 통해 DUAL 테이블을 자주 사용하는 애플리케이션의 연속적인 읽기 작업 성능이 대폭적으로 향상되었습니다.
-- 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)
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를 줄여서 응답시간을 획기적으로 줄일 수 있습니다.
댓글 없음:
댓글 쓰기