\
=====================================================================================
FUNCTION 수행과 SQL 성능문제 이해하기
=====================================================================================
DROP TABLE FUNCTION_TABLE PURGE;
DROP TABLE C1_CODE_NM PURGE;
DROP TABLE C2_CODE_NM PURGE;
< FUNCTION_TABLE >
■ 생성 요건
- 테이블 데이터 건수는 100000 ROWS
- 칼럼 C1은 값의 종류가 100000가지 즉 UNIQUE 성
- 칼럼 C2는 값의 종류가 2가지
■ 테이블 생성
CREATE TABLE FUNCTION_TABLE AS
SELECT LEVEL C1,
MOD(LEVEL, 2) C2,
CHR(65+MOD(LEVEL,26)) C3,
MOD(LEVEL, 3) +1 C4
FROM DUAL
CONNECT BY LEVEL <= 100000;
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_FUNCTION_TABLE_C1 ON FUNCTION_TABLE(C1);
CREATE INDEX IDX_FUNCTION_TABLE ON FUNCTION_TABLE(C2,C3);
CREATE INDEX IDX_FUNCTION_TABLE_C4 ON FUNCTION_TABLE(C4);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'FUNCTION_TABLE',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[칼럼 C1에 대한 코드성 테이블 생성 및 인덱스 생성]
< C1_CODE_NM >
■ UNIQUE한 C1값에 대한 코드성 테이블 생성
CREATE TABLE C1_CODE_NM AS
SELECT LEVEL C1,
LEVEL||'C2 CODE VALUE' C2,
CHR(65+MOD(LEVEL,20)) C3,
MOD(LEVEL,5) C4
FROM DUAL
CONNECT BY LEVEL <= 100000;
■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C1_CODE_NM ON C1_CODE_NM(C1);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C1_CODE_NM', CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[칼럼 C2에 대한 코드성 테이블 생성 및 인덱스 생성]
< C2_CODE_NM >
■ 3가지 값에 대한 코드성 테이블 생성
CREATE TABLE C2_CODE_NM AS
SELECT MOD(LEVEL, 3) C1,
CHR(65+MOD(LEVEL,3)) C2
FROM DUAL
CONNECT BY LEVEL <= 3;
■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C2_CODE_NM ON C2_CODE_NM(C1);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C2_CODE_NM', CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[ FUNCTION 생성 ]
■ 칼럼 C1 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C1_CODENM(P_CODE NUMBER)
RETURN VARCHAR2 IS
V_P_CODE_NM VARCHAR2(100);
BEGIN
SELECT C2 INTO V_P_CODE_NM
FROM C1_CODE_NM
WHERE C1 = P_CODE;
RETURN V_P_CODE_NM;
END;
■ 칼럼 C2 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C2_CODENM(P_CODE NUMBER)
RETURN VARCHAR2 IS
V_P_CODE_NM VARCHAR2(100);
BEGIN
SELECT C2 INTO V_P_CODE_NM
FROM C2_CODE_NM
WHERE C1 = P_CODE;
RETURN V_P_CODE_NM;
END;
set autotrace traceonly
ALTER SESSION SET SQL_TRACE = TRUE;
select c1, fn_c1_codenm(c1) c2, c3 from FUNCTION_TABLE
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
/
SQL> set autotrace TRACEONLY
SQL> /
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2440560795
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 104K| 1639K| 68 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| FUNCTION_TABLE | 104K| 1639K| 68 (2)| 00:00:01 |
------------------------------------------------------------------------------------
###### MAIN SQL ######
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 11.92 13.53 0 6911 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 11.93 13.54 0 6913 0 100000
###### Function ######
SELECT C2
FROM
C1_CODE_NM WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 14.42 17.95 0 0 0 0
Fetch 100000 4.02 3.85 0 300000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 18.45 21.81 0 300000 0 100000
- Function 이 Main SQL 의 추출 데이터인 총 10만건과 동일한 횟수로 반복 수행된다.
- Function 의 1회 수행 속도는 빠르나 10만번의 실행 횟수가 해당 SQL 을 비효율로 만들었다.
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
where c2 = 0
and c3 = 'A';
Execution Plan
----------------------------------------------------------
Plan hash value: 1948025854
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3846 | 38460 | 73 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FUNCTION_TABLE | 3846 | 38460 | 73 (5)| 00:00:01 |
------------------------------------------------------------------------------------
###### MAIN SQL ######
SELECT C2
FROM
C1_CODE_NM WHERE C1 = :B1
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
where c2 = 0
and c3 = 'A'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 258 10.69 10.98 0 501 30768 3846
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 260 10.69 10.98 0 501 30768 3846
###### Function ######
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3846 0.11 0.11 0 0 0 0
Fetch 3846 0.09 0.09 0 11538 0 3846
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7693 0.21 0.21 0 11538 0 3846
- Function 이 수행되는 시점은 Where 절을 만족하는 데이터를 Fetch 할때 수행한다.
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
where c2 = 0
and c3 = 'A'; AND ROWNUM = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1367463562
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| FUNCTION_TABLE | 2 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FUNCTION_TABLE | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
###### MAIN SQL ######
select c1, FN_C1_CODENM(c1) c2, c3
from function_table
where c2 = 0
and c3 = 'A' AND ROWNUM = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 8 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 8 1
###### Function ######
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
- Function 이 수행되는 시점은 Where 절을 만족하는 데이터를 Fetch 할때 수행한다.
SQL> select /*+ LEADING(T1) USE_HASH(T1 T2) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c3 = FN_C2_CODENM(t2.c4);
768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1338498910
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3846 | 157K| 237 (15)| 00:00:03 |
|* 1 | HASH JOIN | | 3846 | 157K| 237 (15)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| FUNCTION_TABLE | 3846 | 49998 | 73 (5)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| C1_CODE_NM | 5000 | 141K| 164 (20)| 00:00:02 |
-------------------------------------------------------------------------------------
- Function 은 총 10만번 수행된다. 그 이유는 Function 을 호출하는 T2 테이블의 데이터를 줄여주는 상수 조건이 없고, T1과 T2 의 조인 방식이 Hash Join 이기 때문이다.
- FN_C2_CODENM (T2.C4) 의 결과 값은 T1 과 T2 가 Hash Join 을 하기 전제 추출되어야 하기 때문에 Function 은 T2 테이블의 전체 데이터 건수만큼 10만번 돈다.
SQL> select /*+ LEADING(T1) USE_HASH(T1 T2) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c4 IN (2,4)
10 and t2.c3 = FN_C2_CODENM(t2.c4);
384 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1338498910
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 84000 | 220 (9)| 00:00:03 |
|* 1 | HASH JOIN | | 2000 | 84000 | 220 (9)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| FUNCTION_TABLE | 3846 | 49998 | 73 (5)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| C1_CODE_NM | 2000 | 58000 | 147 (11)| 00:00:02 |
-------------------------------------------------------------------------------------
###### MAIN SQL ######
select /*+ LEADING(T1) USE_HASH(T1 T2) */
t1.*,
t2.*
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)
and t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 185.48 194.27 708 742 0 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 185.50 194.29 708 742 0 384
###### Function ######
SELECT C2
FROM
C2_CODE_NM WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 40000 1.28 1.35 0 0 0 0
Fetch 40000 0.63 0.67 2 60000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80001 1.92 2.02 2 60000 0 20000
- T2.C4 IN (2,4) 조건을 추가하자 Function 수행횟수가 10만번에서 4만번으로 줄었다.
- T2.C4 = FN_C2_CODENM(T2.C4) 을 수행하기 전에 T2.C4 IN (2,4) 으로 추출된 4만건에 대해서만, 추가적으로 Function 을 수행하였기 때문이다.
SQL> select /*+ LEADING(T1) USE_NL(T1 T2) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c3 = FN_C2_CODENM(t2.c4);
768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2325220898
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3846 | 157K| 3923 (1)| 00:00:48 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3846 | 157K| 3923 (1)| 00:00:48 |
|* 3 | TABLE ACCESS FULL | FUNCTION_TABLE | 3846 | 49998 | 73 (5)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDX_C1_CODE_NM | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| C1_CODE_NM | 1 | 29 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
###### MAIN SQL ######
select /*+ LEADING(T1) USE_NL(T1 T2) */
t1.*,
t2.*
from FUNCTION_TABLE t1,
C1_CODE_NM t2
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 53 17.69 20.91 0 2351 43244 768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 17.69 20.92 0 2351 43244 768
###### Function ######
SELECT C2
FROM
C2_CODE_NM WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3846 0.41 0.45 0 0 0 0
Fetch 3846 0.06 0.07 0 6154 0 2308
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7693 0.48 0.52 0 6154 0 2308
- Hash join 에서 Nested Loops Join 으로 변경시 수행횟수가 3846 번으로 감소하였다.
- Nested Loops Join 의 경우 Hash Join 과 다르게 선행 테이블을 액세스 한 후 조인 조건으로 후행 테이블을 반복 탐색하며 조인을 수행한다. 이때 선행 테이블에서 추출된 건수만큼 반복 수행한다.
SQL> select /*+ LEADING(T1) USE_NL(T1 T2) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c4 IN (2,4)
10 and t2.c3 = FN_C2_CODENM(t2.c4);
384 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2325220898
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 84000 | 3922 (1)| 00:00:48 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2000 | 84000 | 3922 (1)| 00:00:48 |
|* 3 | TABLE ACCESS FULL | FUNCTION_TABLE | 3846 | 49998 | 73 (5)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDX_C1_CODE_NM | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| C1_CODE_NM | 1 | 29 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
###### MAIN SQL ######
select /*+ LEADING(T1) USE_NL(T1 T2) */
t1.*,
t2.*
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)
and t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 7.03 8.91 0 2259 18572 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 7.03 8.91 0 2259 18572 384
###### Function ######
SELECT C2
FROM
C2_CODE_NM WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1538 0.15 0.17 0 0 0 0
Fetch 1538 0.02 0.02 0 2307 0 769
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3077 0.18 0.20 0 2307 0 769
- 상수 조건이 있을 경우 조인 조건으로 조인이 성공된 3846 건 중에서 T2.C4 IN (2,4) 조건을 만족한 1538 건에 대해서만 Function 이 수행된다.
SQL> select /*+ LEADING(T2) USE_NL(T2 T1) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c3 = FN_C2_CODENM(t2.c4);
768 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1116069955
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3846 | 157K| 5169 (1)| 00:01:03 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3846 | 157K| 5169 (1)| 00:01:03 |
|* 3 | TABLE ACCESS FULL | C1_CODE_NM | 5000 | 141K| 164 (20)| 00:00:02 |
|* 4 | INDEX UNIQUE SCAN | IDX_FUNCTION_TABLE_C1 | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| FUNCTION_TABLE | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
- 테이블 T2 를 먼저 수행한 경우, 데이터를 줄일 수 있는 상수조건이 존재하지 않는다. 따럿 T2 테이블 전체 데잍터에 대해 and t2.c3 = FN_C2_CODENM(t2.c4) 을 처리해야 함으로 Function 이 10만번 수행된다.
SQL> select /*+ LEADING(T2) USE_NL(T2 T1) */
2 t1.*,
3 t2.*
4 from FUNCTION_TABLE t1,
5 C1_CODE_NM t2
6 where t1.c2 = 0
7 and t1.c3 = 'A'
8 and t1.c1 = t2.c1
9 and t2.c4 IN (2,4)
10 and t2.c3 = FN_C2_CODENM(t2.c4);
384 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1116069955
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 84000 | 2149 (1)| 00:00:26 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2000 | 84000 | 2149 (1)| 00:00:26 |
|* 3 | TABLE ACCESS FULL | C1_CODE_NM | 2000 | 58000 | 147 (11)| 00:00:02 |
|* 4 | INDEX UNIQUE SCAN | IDX_FUNCTION_TABLE_C1 | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| FUNCTION_TABLE | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
###### MAIN SQL ######
select /*+ LEADING(T2) USE_NL(T2 T1) */
t1.*,
t2.*
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)
and t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 181.06 183.27 212 6999 0 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 181.06 183.27 212 6999 0 384
###### Function ######
SELECT C2
FROM
C2_CODE_NM WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 40000 1.29 1.30 0 0 0 0
Fetch 40000 0.63 0.64 0 60000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80001 1.92 1.94 0 60000 0 20000
- 상수 조건이 있을 시에는 데이터를 먼저 줄여놓고 함수가 실행되기 때문에 select count(*) from C1_CODE_NM t2 where T@.C4 IN(2,4) 의 결과 값이 4만으로 Function 수행 횟수와 일치한다.
- T2 를 먼저 수행할때 T2.C4 IN(2,4) 조건을 처리하여 데이터를 걸러 낸 후, 4 만건에 대해서만 T2,C3 = FN_C2_CODENM(T2.C4) 조건을 처리하여 5000 건이 추출된다.