(Oracle) Deterministic Function의 비밀

http://ukja.tistory.com/159

Deterministic Function의 비밀

오라클 2008.08.18 12:50

Jonathan Lewis의 [CBO Fundametals] 220 Page에 다음과 같은 언급이 있다.

The point of deterministic function is that if Oracle can determine that the current call to the function is using the same input as the previous call, then it can use the previous result and avoid the call-or so the manuals say. As far as I can tell, this feature of deterministic fuctions has never been implemented. But the capability can be emulated by scalar subqueries.

반면, Oracle Manual에는 다음과 같은 언급이 있다.

Calls to a DETERMINISTIC function might be replaced by the use of an already calculated value when the function is called multiple times within the same query, or if there is a function-based index or a materialized view defined that includes a relevant call to the function.

간단한게 정리하자면 이렇다.

• Jonathan Lewis: Deterministic Function은 Cache 효과가 있지만(그렇다고 Manual에 되어 있지만), 실제로 Cache 효과가 구현된 적은 없다.
• Oracle Manual(여기서는 9i) Deterministic Function은 Cache 효과가 있다.

누구 말이 맞나?

Test를 통해 검증해 볼 수 밖에 없다.

우선 다음과 같이 필요한 Object를 만든다. Package 변수를 통해 함수의 호출 회수를 파악하는 것에 유의하자.

drop table t1 purge;
drop table t1;
create table t1(c1 int, c2 int);

create or replace package p1 as
  g_idx int := 0;   -- 호출 회수 파악을 위한 변수
end;
/

-- Deterministic Function 선언
-- Test의 편리를 위해 Return 결과를 "1"로 통일 시킨다.
-- 이렇게 하면 Input 값만 동일하면 Cache가 이루어질 것이다.
create or replace function f1(v1 int)
return number
deterministic
is
begin
  p1.g_idx := p1.g_idx + 1;   -- 호출될 때마다 1 증가
  return 1;
end;
/

-- Column c1은 총 10종류의 값 밖에 없다.
-- 따라서 Cache가 이루어진다면 함수의 실제 호출 회수는 10회에 불과해야 한다.
insert into t1
select mod(level, 10)+1, level
from dual
connect by level <= 1000
;

commit;

일반 Query에서 Deterministic Function을 사용한 경우는 다음과 같다.
(100 Row만 Fetch 한다)

-- case1
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;

exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 70  -- 70?

왜 100도 아니고 10도 아닌 70인가? Cache가 안되었다면 100, Cache가 되었다면 10이어야 한다. 왜 어중간한 이상한 값이 나왔는가?

무언가 이상하다. 계산을 해본다. SQL*Plus의 Fetch Array Size는 15이다. 만일 Fetch Level로 Cache가 된다면?

    Fetch 때마다 호출 회수 * Fetch 회수 + 짜투리 호출 회수
    = 10*(100/15) + 짜투리 호출 회수
    = 10*6 + 10
    = 70

완벽하게 맞아 떨어진다. 즉, Fetch Call Level에서 Cache가 이루어진다고 가정할 수 있다.

검증을 위해 몇 가지 Test를 해보자.

먼저, 같은 수의 Row를 Fetch하지만, 실제 Call은 1회에 불과하도록 PL/SQL을 이용해 호출해본다.

-- case2
declare
  v_count number;
begin
  for idx in 1 .. 100 loop
      p1.g_idx := 0;
      
      for r in (select f1(c1) from t1
        where rownum <= idx) loop
        null;
      end loop;
      
      dbms_output.put_line('count = ' || idx || ', call = ' || p1.g_idx);
  end loop;
end;
/
count = 1, call = 1 
count = 2, call = 2 
count = 3, call = 3 
count = 4, call = 4 
count = 5, call = 5 
count = 6, call = 6 
count = 7, call = 7 
count = 8, call = 8 
count = 9, call = 9 
count = 10, call = 10
count = 11, call = 10
count = 12, call = 10
count = 13, call = 10
...
count = 98, call = 10 
count = 99, call = 10 
count = 100, call = 10

실제 최대 함수 호출 회수는 "10"회에 불과하다. Cache가 이루어진다는 의미이다.

Fetch Array Size를 크게(1000) 하면? Fetch Call Level에서 Cache가 된다면 역시 호출 회수는 10회로 줄어야 한다.

-- case3
set arraysize 1000
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;

exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 11  <-- Bingo!!!

우리의 가정이 맞다고 볼 수 있다. 만일 Fetch Array Size를 1로 하면 100회 호출이 이루어질 것이다.

-- case4
set arraysize 1
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;

exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 100

정확하다. 만일 함수 호출을 Subquery안으로 집어 넣으면 언제나 Cache가 이루어진다.

-- case5 : scalar subquery
set arraysize 1
exec p1.g_idx := 0;

select (select f1(c1) from dual)
from t1 where rownum <= 100
;

exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 10  <-- Oh yes!!!

왜 Jonatha Lewis는 Cache 기능이 구현된 적이 없다고 했을까? 이 문제를 Jonatha Lewis와 Email로 논의한 결과 답변은 "내가 책에 적은 것은 9i 기준이였다. 10g에서는 Cache가 이루어지며 Fetch Call Level에서 동작한다"라고 밝혀 왔다.

위의 테스트 결과와 완벽하게 일치한다.

정리해보면    
Deterministic Function의 Cache 기능은 10g에서부터 지원된다.
이전 버전에서 이 효과를 누리려면 (Scalar) Subquery를 사용한다.
Cache는 Query Level이 아닌 Fetch Level에서 이루어진다.
따라서 Cache 효과를 누리려면 Fetch Array Size를 크게 지정해야 한다.