INDEX SCAN 방식에 따른 사용자 정의 함수의 동작을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT MOD (ROWNUM, 100) AS c1, MOD (ROWNUM, 100) AS c2 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT 0 AS c1 FROM DUAL;
CREATE INDEX t1_x1 ON t1 (c1, c2);
CREATE OR REPLACE FUNCTION f1 RETURN NUMBER
IS
l_c1 NUMBER;
BEGIN
SELECT c1 INTO l_c1 FROM t2;
RETURN l_c1;
END;
/
아래는 INDEX RANGE SCAN 시 트레이스 tkprof 결과다. f1 함수가 1번만 수행된 것을 확인할 수 있다.
-- 2 SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 = f1 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.026 0 3 0 0 Fetch 2 0.000 0.000 0 3 0 10 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 4 0.000 0.027 0 6 0 10 SELECT C1 FROM T2 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.025 0 3 0 1 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 3 0.000 0.025 0 3 0 1
트레이스 내용을 살펴보면 쿼리가 파싱되고, 사용자 정의 함수가 수행된 후, 그 결과로 쿼리가 수행된 것을 확인할 수 있다. (--> 가 쿼리 Call)
-- 3 ===================== PARSING IN CURSOR #1 len=47 dep=0 uid=84 oct=3 lid=84 tim=9158911325983 hv=2399180123 ad='7ffadc72ab70' sqlid='22y0uxy7h15av' SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 = f1 END OF STMT --> PARSE #1:c=0,e=187,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=246717666,tim=9158911325981 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158911326480 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT PARSE #2:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158911326479 EXEC #2:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158911326689 FETCH #2:c=0,e=25328,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158911352079 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=167055 op='TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=4251 card=327)' CLOSE #2:c=0,e=3,dep=1,type=3,tim=9158911352474 --> EXEC #1:c=0,e=26397,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=246717666,tim=9158911352578 --> FETCH #1:c=0,e=50,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=246717666,tim=9158911352764 --> FETCH #1:c=0,e=76,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=246717666,tim=9158911354848 --> STAT #1 id=1 cnt=10 pid=0 pos=1 obj=167054 op='INDEX RANGE SCAN T1_X1 (cr=6 pr=0 pw=0 time=0 us cost=2 size=104 card=4)' --> CLOSE #1:c=0,e=44,dep=0,type=0,tim=9158911358008
아래는 INDEX SKIP SCAN 시 트레이스 tkprof 결과다. f1 함수가 12번 수행되었다.
-- 4 SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c2 = f1 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.027 0 6 0 0 Fetch 2 0.000 0.005 0 36 0 10 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 4 0.000 0.032 0 42 0 10 SELECT C1 FROM T2 Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 1 0.000 0.000 0 0 0 0 Execute 12 0.000 0.000 0 0 0 0 Fetch 12 0.000 0.026 0 36 0 12 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 25 0.000 0.027 0 36 0 12
트레이스 내용을 살펴보면 쿼리가 파싱되고, 사용자 정의 함수가 수행된 후, 그 결과로 쿼리가 수행되었지만, 사용자 정의 함수가 실행 단계에서 1번, 패치 단계에서 10번(=결과 건수) 수행된 것을 확인할 수 있다. 읽기 일관성을 보장하기 위해 재수행된 것으로 보인다.
-- 5 ===================== PARSING IN CURSOR #1 len=50 dep=0 uid=84 oct=3 lid=84 tim=9158386281204 hv=1992152571 ad='7ffadf97a3e8' sqlid='dtfu9fdvbvpgv' SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c2 = f1 END OF STMT --> PARSE #1:c=0,e=185,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4140134295,tim=9158386281202 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386281715 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT PARSE #2:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386281713 EXEC #2:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386281933 FETCH #2:c=0,e=25731,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386307727 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=167055 op='TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=4251 card=327)' CLOSE #2:c=0,e=3,dep=1,type=3,tim=9158386308119 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386308323 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386308320 FETCH #3:c=0,e=58,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386308561 CLOSE #3:c=0,e=2,dep=1,type=3,tim=9158386308639 --> EXEC #1:c=0,e=27295,p=0,cr=6,cu=0,mis=0,r=0,dep=0,og=1,plh=4140134295,tim=9158386308695 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386308957 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #2:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386308956 FETCH #2:c=0,e=42,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386309169 CLOSE #2:c=0,e=2,dep=1,type=3,tim=9158386309243 --> FETCH #1:c=0,e=486,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=4140134295,tim=9158386309302 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386311500 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386311497 FETCH #3:c=0,e=84,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386311949 CLOSE #3:c=0,e=3,dep=1,type=3,tim=9158386312062 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386312203 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #2:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386312202 FETCH #2:c=0,e=38,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386312408 CLOSE #2:c=0,e=2,dep=1,type=3,tim=9158386312480 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386312573 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386312572 FETCH #3:c=0,e=35,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386312769 CLOSE #3:c=0,e=2,dep=1,type=3,tim=9158386312838 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386312926 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #2:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386312924 FETCH #2:c=0,e=39,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386313274 CLOSE #2:c=0,e=2,dep=1,type=3,tim=9158386313347 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386313449 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386313447 FETCH #3:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386313642 CLOSE #3:c=0,e=1,dep=1,type=3,tim=9158386313710 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386313801 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #2:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386313799 FETCH #2:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386313992 CLOSE #2:c=0,e=2,dep=1,type=3,tim=9158386314060 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386314152 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386314151 FETCH #3:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386314345 CLOSE #3:c=0,e=2,dep=1,type=3,tim=9158386314414 ===================== PARSING IN CURSOR #2 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386314503 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #2:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386314502 FETCH #2:c=0,e=35,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386314697 CLOSE #2:c=0,e=2,dep=1,type=3,tim=9158386314765 ===================== PARSING IN CURSOR #3 len=17 dep=1 uid=84 oct=3 lid=84 tim=9158386314852 hv=3655014024 ad='7ffada236720' sqlid='fu5xqcvcxq5n8' SELECT C1 FROM T2 END OF STMT EXEC #3:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1513984157,tim=9158386314851 FETCH #3:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1513984157,tim=9158386315043 CLOSE #3:c=0,e=2,dep=1,type=3,tim=9158386315110 --> FETCH #1:c=0,e=4249,p=0,cr=31,cu=0,mis=0,r=9,dep=0,og=1,plh=4140134295,tim=9158386315493 --> STAT #1 id=1 cnt=10 pid=0 pos=1 obj=167054 op='INDEX SKIP SCAN T1_X1 (cr=42 pr=0 pw=0 time=0 us cost=11 size=104 card=4)' --> CLOSE #1:c=0,e=51,dep=0,type=0,tim=9158386318288
가설을 검증하기 위해 아래와 같이 함수를 생성하자. 내부에서 t2 테이블을 갱신한다.
-- 6
CREATE OR REPLACE FUNCTION f2 RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_c1 NUMBER;
BEGIN
SELECT c1 INTO l_c1 FROM t2;
UPDATE t2 SET c1 = c1 + 1;
COMMIT;
RETURN l_c1;
END;
/
INDEX RANGE SCAN의 쿼리 7-1은 f2 함수를 1번만 조회하기 때문에 결과가 반환된다. INDEX SKIP SCAN의 쿼리 7-2는 재수행 과정에서 값이 달라졌기 때문에 결과가 반환되지 않았다. 읽기 일관성과 관련된 동작임을 확인할 수 있다. 스칼라 서브 쿼리를 사용한 쿼리 7-3은 결과가 반환되었다.1 c2가 13이 아닌 3인 행이 반환된 점이 의외다.
-- 7-1 SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 = f2; C1 C2 -- -- 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10 행이 선택되었습니다. -- 7-2 SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c2 = f2; 선택된 레코드가 없습니다. -- 7-3 SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c2 = (SELECT f2 FROM DUAL); C1 C2 -- -- 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 10 행이 선택되었습니다.
쿼리 7-2의 트레이스 tkprof 결과를 확인하면 원인을 알 수 있다. f2 함수가 2번만 수행되었다. 최초 수행 결과와 실행 결과가 달라 이후 과정을 수행하지 않은 것으로 보인다.
-- 8 SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c2 = f2 Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 1 0.000 0.007 0 2 0 0 Execute 1 0.000 0.003 0 12 8 0 Fetch 1 0.000 0.000 0 0 0 0 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 3 0.000 0.010 0 14 8 0 SELECT C1 FROM T2 Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 0 0.000 0.000 0 0 0 0 Execute 2 0.000 0.000 0 0 0 0 Fetch 2 0.000 0.000 0 6 0 2 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 4 0.000 0.000 0 6 0 2 UPDATE T2 SET C1 = C1 + 1 Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 0 0.000 0.000 0 0 0 0 Execute 2 0.000 0.000 0 6 6 2 Fetch 0 0.000 0.000 0 0 0 0 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 2 0.000 0.000 0 6 6 2
- 스칼라 서브 쿼리 캐싱으로 인해 함수가 1번만 수행된다. DETERMINISTIC 함수를 사용해도 동일한 결과를 얻을 수 있다. [본문으로]
'Oracle > Tuning' 카테고리의 다른 글
| JPPD와 ROLLUP (0) | 2018.12.10 |
|---|---|
| FK로 인한 DML 성능 저하 (0) | 2018.12.08 |
| TIMESTAMP 타입과 SYSTIMESTAMP 함수 (0) | 2018.12.01 |
| SQL 메타 데이터 (0) | 2018.12.01 |
| 다수 테이블에 대한 OR 조건 (0) | 2018.12.01 |
