SQL 튜닝의 시작 (2013년)
FUNCTION 수행과 SQL 성능문제
1. FUNCTION은 최종 추출 결과만큼만 수행하자
[Function 사용 위치 변경전 ]
select *
from
(
SELECT
t1.c1, t1.c2,t2.c3,FN_C2_CODENM(t2.c4) c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.C1
AND t2.c4 IN (2, 4)
order by t2.c3
) z
where rownum < 5
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.005 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.484 0.574 0 3021 0 4
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.484 0.579 0 3021 0 4
Rows Row Source Operation
------- -----------------------------------------------------------------------
4 COUNT STOPKEY (cr=3021 pr=0 pw=0 time=573754 us)
4 VIEW (cr=3021 pr=0 pw=0 time=573749 us)
4 SORT ORDER BY STOPKEY (cr=3021 pr=0 pw=0 time=573746 us)
1538 HASH JOIN (cr=714 pr=0 pw=0 time=71036 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=7748 us)
40000 TABLE ACCESS FULL C1_CODE_NM (cr=469 pr=0 pw=0 time=40046 us)
********************************************************************************
-총 추출건수 : 4건
SELECT C2 FROM C2_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1538 0.016 0.028 0 0 0 0
Fetch 1538 0.000 0.012 0 2307 0 769
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3077 0.016 0.041 0 2307 0 769
********************************************************************************
-Function 수행횟수 : 1538건
[Function 사용 위치 변경후 ]
select c1,c2,c3, FN_C2_CODENM(c4) c4 <-- 최종 쿼리 추출시 Function 사용
from
(
SELECT
t1.c1, t1.c2,t2.c3,t2.c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.C1
AND t2.c4 IN (2, 4)
order by t2.c3
) z
where rownum < 5
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.005 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.079 0 722 0 4
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.094 0.085 0 722 0 4
Rows Row Source Operation
------- -----------------------------------------------------------------------
4 COUNT STOPKEY (cr=714 pr=0 pw=0 time=54872 us)
4 VIEW (cr=714 pr=0 pw=0 time=54864 us)
4 SORT ORDER BY STOPKEY (cr=714 pr=0 pw=0 time=54860 us)
1538 HASH JOIN (cr=714 pr=0 pw=0 time=60290 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=7781 us)
40000 TABLE ACCESS FULL C1_CODE_NM (cr=469 pr=0 pw=0 time=40047 us)
********************************************************************************
-총 추출건수 : 4건
SELECT C2 FROM C2_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 4 0.000 0.000 0 0 0 0
Fetch 4 0.000 0.000 0 8 0 4
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 9 0.000 0.000 0 8 0 4
********************************************************************************
-Function 수행횟수 : 4번
-4건 추출 후 Fetch 단계에서 Function 수행
-불필요하게 수행되었던 FUNCTION 수행을 효과적으로 제거하여 성능개선 효과
2 .FUNCTION이 스칼라 서브쿼리에서 수행하도록 변경하자
- SQL 추출건수가 많다면 FUNCTION의 수행위치만으로는 성능개선이 힘들다.
- 반복되는 입력값을 통해 동일한 결과값을 추출하는 경우
매번 Function을 사용하는 것 보다는
스칼라 서브쿼리 내에서 수행되도록 변경한다면
Multi Buffer(10g이후) 사용이 가능해지므로 Function의 수행횟수를 감소시켜 성능개선의 효과를 볼수 있다
- _query_execution_cache_max_size Multi Buffer 사이즈 조정가능.(충분한 검토후 사용해야함)
* 입력 값에 대응하는 값의 종류가 다양하지 않고, 입력 값에 대한 결과가 항상 동일하다는 것이 보장 된다면,
SQL 작성시 Function 은 스칼라 서브쿼리에서 수행하도록 하여, Function 수행에 의한 부하를 감소시켜야 한다!
3. FUNCTION을 호출하는 값의 패턴을 분석하자
- Function을 스칼라 서브쿼리에서 수행하도록 변경하더라도,
중복값이 거의 존재하지 않는는 Unique한 값이 Function의 입력값인 경우
Cache 효과를 거의 볼수 없으므로 성능상 유리한 점이 없다.
- 오히려 Unique한 값이 입력값으로 사용되는 Function인 경우 Cache 효과는 누리지 못하면서, 내부적으로 Multi Buffer 관리 비용만 발생한다.
- 따라서 Function을 호출하는 입력값의 패턴에 대한 분석 없이 Function을 스칼라 서브쿼리에서 수행하도록 SQL을 작성하는것은 바람직 하지 않다
* NUM_DISTINCT 값이 테이블 ROW수와 비슷한 경우에는 Function의 사용보다는 Outer Join으로 변경하여 수행하는것이 성능상 유리할 수 있다.
(Outer Join을 수행할 경우, 가장 유리한 조인방법 선택이 가능하고 소프트 파싱의 부하도 줄일 수 있다)
- 패턴 분석(테이블)
| TABLE_NAME | NUM_ROWS | BLOCKS |
|---|---|---|
| FUNCTION_TABLE | 100000 | 253 |
- 패턴분석(컬럼)
| COLUMN_NAME | NUM_DISTINCT | |
|---|---|---|
| C1 | 100000 | <-- 아우터 조인이 유리할 수 있다. |
| C2 | 2 | |
| C3 | 26 | |
| C4 | 3 | <-- 이러한 경우에 FUNCTION을 스칼라 서브쿼리를 통해 수행하도록 하면 성능개선의 큰 효과를 볼 수 있다 |
- 테이블 통계정보의 NUM_ROWS와 컬럼 통계정보의 NUM_DISTINCT를 보고 패턴을 파악하여 효과적인 방법을 선택하자!
4. SELECT절에 사용된 FUNCTION을 조인으로 변경하자
-NUM_DISTINCT값이 매우 많은 경우 스칼라 서브쿼리로 변경해도 성능개선이 되지 않는다.
-이러한 경우에는 Outer Join으로 변경하여 성능을 개선시킬 수 있다
(변경전)
SELECT (SELECT fn_c1_codenm(c1) FROM DUAL), c1
FROM FUNCTION_TABLE
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1001 15.460 15.503 0 301243 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 15.475 15.506 0 301243 0 100000
Rows Row Source Operation
------- -----------------------------------------------------------------------
100000 FAST DUAL (cr=0 pr=0 pw=0 time=104896 us)
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=1243 pr=0 pw=0 time=79 us)
********************************************************************************
SELECT C2 FROM C1_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 100000 1.888 1.773 0 0 0 0
Fetch 100000 1.154 1.166 0 300000 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 200001 3.042 2.939 0 300000 0 100000
********************************************************************************
(변경후)
SELECT b.c2, a.c1
FROM function_table a,
c1_code_nm b
WHERE a.c1 = b.c1(+)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1001 0.359 0.287 0 1706 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.359 0.288 0 1706 0 100000
Rows Row Source Operation
------- -----------------------------------------------------------------------
100000 HASH JOIN OUTER (cr=1706 pr=0 pw=0 time=160334 us)
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=56 us)
100000 TABLE ACCESS FULL C1_CODE_NM (cr=1461 pr=0 pw=0 time=62 us)
********************************************************************************
- SQL 수행 시 Function 을 수행하면서 발생하는 10 만 번의 파싱도 동시에 제거됨으로써, 소프트 파싱으로 인한 부하도 크게 개선되었다
- Function 을 통해 10 만 번 반복적으로 Unique Scan 을 했던 C1_CODE_NM 테이블이
조인으로 수행되도록 SQL 을 작성하고 Hash Join 으로 처리함으로써, 반복적인 액세스를 제거하여 성능을 개선하였다.
5. WHERE절의 FUNCTION을 SELECT절로 옮기자
SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3, t2.c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
AND fn_c2_codenm(T2.C4) BETWEEN 'A' AND 'B'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 18 1.607 1.762 0 20731 0 1667
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 20 1.607 1.762 0 20731 0 1667
Rows Row Source Operation
------- -----------------------------------------------------------------------
1667 HASH JOIN (cr=20731 pr=0 pw=0 time=1440778 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=84 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=20486 pr=0 pw=0 time=2174393 us)
********************************************************************************
SELECT C2 FROM C2_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 10000 0.250 0.170 0 0 0 0
Fetch 10000 0.078 0.100 0 20000 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 20001 0.328 0.270 0 20000 0 10000
********************************************************************************
-SELECT count(*) FROM c1_code_nm t2 WHERE t2.c3 IN ('A') 의 수행결과는 5,000건이다.
-따라서 위의 조건으로 걸러낸 결과에 대해서 Function이 수행될것이라 생각했지만 실제로는 10,000번이 수행되었다
-그 이유는 Optimizer 가 내부적으로 BETWEEN 조건의 경우 다음과 같이 변경하여 처리하기 때문이다.
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=178 Card=12 Bytes=264)
1 0 HASH JOIN (Cost=178 Card=12 Bytes=264)
2 1 TABLE ACCESS (FULL) OF 'FUNCTION_TABLE' (TABLE) (Cost=62 Card=17K Bytes=195K)
3 1 TABLE ACCESS (FULL) OF 'C1_CODE_NM' (TABLE) (Cost=115 Card=13 Bytes=130)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
2 - filter("T1"."C4"=2 AND "T1"."C2"=0)
3 - filter("T2"."C3"='A' AND "FN_C2_CODENM"("T2"."C4")>='A' AND "FN_C2_CODENM"("T2"."C4")<='B')
-----------------------------------------------------------
-where 절의 function을 select 절로 옮기자
SELECT /*+ NO_MERGE(A) */ *
FROM (
SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3,
t1.c4,
fn_c2_codenm(t2.c4) AS ft2c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
) A
WHERE ft2c4 BETWEEN 'A' AND 'B'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 18 1.825 1.849 0 24099 0 1667
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 20 1.825 1.849 0 24099 0 1667
Rows Row Source Operation
------- -----------------------------------------------------------------------
1667 VIEW (cr=24099 pr=0 pw=0 time=1689310 us)
1667 HASH JOIN (cr=20731 pr=0 pw=0 time=1453056 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=88 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=20486 pr=0 pw=0 time=2255478 us)
********************************************************************************
SELECT C2 FROM C2_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 11701 0.218 0.200 0 0 0 0
Fetch 11701 0.062 0.117 0 23402 0 11701
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 23403 0.281 0.317 0 23402 0 11701
********************************************************************************
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=178 Card=12 Bytes=24K)
1 0 VIEW (Cost=178 Card=12 Bytes=24K)
2 1 HASH JOIN (Cost=178 Card=12 Bytes=264)
3 2 TABLE ACCESS (FULL) OF 'FUNCTION_TABLE' (TABLE) (Cost=62 Card=17K Bytes=195K)
4 2 TABLE ACCESS (FULL) OF 'C1_CODE_NM' (TABLE) (Cost=115 Card=13 Bytes=130)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
3 - filter("T1"."C4"=2 AND "T1"."C2"=0)
4 - filter("T2"."C3"='A' AND "FN_C2_CODENM"("T2"."C4")>='A' AND "FN_C2_CODENM"("T2"."C4")<='B')
-----------------------------------------------------------
-인라인뷰 바깥쪽의 조건이 인라인뷰 안으로 침투되어 성능저하 발생
-Function 을 Select 절에서 수행해놓고 나중에 이 값을 이용해 뷰 바깥에서 조건을 처리할 목적으로 SQL 을 재 작성했지만,
QUERY TRANSFORMATION 에 의해 FPD 가 발생함으로써, Where 절과 Select 절의Function 까지 수행하여 수행횟수가 증가함
-select절의 function을 스칼라 서브쿼리로 사용
SELECT /*+ NO_MERGE(A) */
*
FROM ( SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3,
t2.c4,
(SELECT fn_c2_codenm(T2.C4) FROM DUAL) AS ft2c4
FROM FUNCTION_TABLE t1 ,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
) A
WHERE ft2c4 between 'A' AND 'B'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 18 0.062 0.047 0 733 0 1667
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 20 0.062 0.051 0 733 0 1667
Rows Row Source Operation
------- -----------------------------------------------------------------------
1667 VIEW (cr=733 pr=0 pw=0 time=45879 us)
1667 FILTER (cr=733 pr=0 pw=0 time=45873 us)
1667 HASH JOIN (cr=731 pr=0 pw=0 time=35262 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=57 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=486 pr=0 pw=0 time=10047 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us)
********************************************************************************
SELECT C2 FROM C2_CODE_NM WHERE C1 = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1 0.000 0.000 0 2 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.000 0 2 0 1
********************************************************************************
-Function 수행 후 비교하는 조건이 사라지면서 FPD(Filter Push Down) 가 발생하지 않은 것을 알 수 있다.
FUNCTION의 사용
- FUNCTION의 사용은 프로그램 개발이나 유지보수 등 여러 측면에서 효율적이지만, 반대로 비효율적으로 사용된 FUNCTION은 DB 성능에 큰 악영향을 미칠 수 있다.
- FUNCTION의 동작방식을 정확히 이해하고, FUNCTION을 사용할 때 항상 효율적으로 수행되도록 SQL을 작성해야 한다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3799
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.