ORACLE FUNCTION USING DUAL
from
1. ORACLE FUNCTION USING DUAL
Trace 를 보면 cr 값이 이상한 곳에서 튀는 경우가 있다. 이를테면 sort 한 결과에서 row 값은 그대로인데 cr 이 튀는 경우가 그러하다. 이런경우 생각할 수 있는 경우로 결과 row 에 대해 스칼라 서브쿼리나 select-list 에 use function 을 사용한 경우가 있다.
/*+ system function 을 사용하는 경우도 cr 이 바뀔 수 있나? */
이 cr 값을 줄이기 위한 방법중 하나가 dual 을 사용하는 방법으로 f(x) 를 select f(x) from dual 로 바꾼다. 이러한 방법으로 동일한 입력에 대해 결과값을 다시 사용하면서 cr을 줄일 수 있다. 결과의 재활용이란 측면에서 11g의 result cache 와 상통하는 부분도 있다. 결과값의 재사용이라는 것은 같은 입력이 반복된다는 이야기이며 달리 말해 distinct key 가 총 row 수에 비해 작다란 이야기이다.
Scott 의 empt 를 예로 들면 emp 의 deptno 이 그러하다.
2. 예제
SCOTT 에 다음과 같이 FUNCTION 을 생성했다.
CREATE OR REPLACE FUNCTION "SCOTT"."DEPTNAME" (a in number)
return varchar2 is
result varchar2(20);
begin
select dname into result from dept
where deptno = a;
return result;
end;
/
생성 후 DUAL 을 사용하지 않은 QUERY1, QUERY2 를 수행하였고 이에 대한 트레이스를 확인하였다.
(EMP TABLE 은 ROW 수를 늘려 진행했다. (약 7만건)
<QUERY1>
select e.ename ename,e.sal sal,s.grade grade,deptname(deptno) dname
from emp2 e,salgrade s
where e.sal between s.losal and s.hisal
/
<QUERY2>
select e.ename ename,e.sal sal,s.grade grade,(select deptname(deptno) from dual) dname
from emp2 e,salgrade s
where e.sal between s.losal and s.hisal
/
위 수행 쿼리에 대한 trace 내용은 아래와 같다.
<QUERY1 의 TRACE>
select e.ename ename,e.sal sal,s.grade grade,deptname(deptno) dname
from emp2 e,salgrade s
where e.sal between s.losal and s.hisal
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 5019 7.17 27.51 0 7495 0 75264
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5021 7.17 27.51 0 7495 0 75264
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
75264 NESTED LOOPS (cr=7495 pr=0 pw=0 time=5570785 us)
5 TABLE ACCESS FULL SALGRADE (cr=11 pr=0 pw=0 time=462 us)
75264 TABLE ACCESS FULL EMP2 (cr=7484 pr=0 pw=0 time=2710325 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5019 0.00 0.01
SQL*Net message from client 5019 0.08 6.31
********************************************************************************
SELECT DNAME
FROM
DEPT WHERE DEPTNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 75264 17.99 63.61 0 0 0 0
Fetch 75264 6.77 19.17 0 150528 0 75264
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 150529 24.76 82.79 0 150528 0 75264
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
75264 TABLE ACCESS BY INDEX ROWID DEPT (cr=150528 pr=0 pw=0 time=44966021 us)
75264 INDEX UNIQUE SCAN PK_DEPT (cr=75264 pr=0 pw=0 time=5959299 us)(object id 51147)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: library cache 1 0.10 0.10
<QUERY2 의 TRACE>
select e.ename ename,e.sal sal,s.grade grade,(select deptname(deptno) from dual) dname
from emp2 e,salgrade s
where e.sal between s.losal and s.hisal
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5019 0.52 0.52 0 7495 0 75264
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5021 0.55 0.55 0 7499 0 75264
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
3 FAST DUAL (cr=0 pr=0 pw=0 time=207 us)
75264 NESTED LOOPS (cr=7495 pr=0 pw=0 time=1280162 us)
5 TABLE ACCESS FULL SALGRADE (cr=11 pr=0 pw=0 time=89 us)
75264 TABLE ACCESS FULL EMP2 (cr=7484 pr=0 pw=0 time=527556 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5019 0.00 0.02
SQL*Net message from client 5019 0.13 50.25
********************************************************************************
SELECT DNAME
FROM
DEPT WHERE DEPTNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 3
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=823 us)
3 INDEX UNIQUE SCAN PK_DEPT (cr=3 pr=0 pw=0 time=535 us)(object id 51147)
먼저 TRACE 내용에 대해 간단히 이야기를 하자면 상단/하단의 TRACE 로 나뉘어지고 상단은 메인쿼리 하단은 FUNCTION 에 해당된다.
위의 QUERY1, QUERY2 를 비교하면 다음과 같은 차이점이 있다. EXECUTION PLAN 과 STATISTICS 이다.
먼저 EXECUTION PLAN 에 대해 이야기 하자면 FUNCTION 에 DUAL 을 사용한 경우 FAST DUAL 이라는 OPERATION 이 추가된다.
STATISTICS 에 대한 경우 ROWS 가 75264 에서 3으로 급감한 것을 알 수 있다. 75264 는 상단 TRACE 를 보면 알겠지만 결과 ROWS 에 해당하는 횟수이다. 이는 다시 말해 NL JOIN 후 나온 결과에 대해 DEPT.DNAME 을 추출하기 위해 FUNCTION 수행을 한다는 의미이다. 이부분에서 CR 값이 증가하게 된다. 위의 플랜에서는 이 케이스를 아쉽게도 보이지 못했다.
/*+ DEPTH 를 하나 더 들어가야 보일 듯 하다 */
그렇다면 3이라는 수는 어떻게 나왔는지 봐야 할 것이다. Emp 의 deptno 의 distinct key 값을 확인해 보았다.
SQL> select count(distinct deptno) from emp2;
COUNT(DISTINCTDEPTNO)
---------------------
3
값이 3이 나왔다. 값의 종류에 (3가지) 대해 결과값을 저장하고 재사용한다는 것을 생각했을 때 이 결과는 타당하다. 이 값을 보면 distinct key 값이 row 수보다 작을수록 유리하다란 이야기가 무엇인지 알 수 있다.
/*+ unique key 에 대해 위와 같은 기법을 사용했을 때 지표값이 어떻게 달라지는지 확인 */