태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Trigger와 Cursor 재활용

오라클 2009. 1. 8. 13:41
DBA나 Tuner로부터 다음과 같은 가이드를 접한 사람들이 많을 것이다.

Trigger내에 있는 SQL 문장을 Procedure로 변환하면 Parse가 줄어든다.


실제로도 그렇다. 하지만 위의 말이 정확하게 어떤 의미인지 고민해 본 적이 있는가?

아주 간단한 테스트로 검증해 볼 수 있다.

다음과 같이 Trigger를 만든다. 동일한 Query를 한번은 Procedure를 통해, 한번은 직접 호출을 통해 수행한다.

create table t1(c1 int);

create or replace procedure proc1
is
begin
  for x in (select /*+ procedure */ * from dual) loop
    null;
  end loop;
end;
/

create or replace trigger trg1
after insert on t1
begin
  proc1;
 
  for x in (select /*+ trigger */ * from dual) loop
    null;
  end loop;
end;
/

그리고 다음과 같이 10046 Event(SQL Trace)를 활성화한 후 총 5건의 Row를 Insert한다. 따라서 Trigger는 총 5번 Fire되었을 것이다.

@trace_on 10046 1

insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);

@trace_off


Tkprof Report의 결과는 다음과 같다.

SELECT /*+ procedure */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0         15          0           5

SELECT /*+ trigger */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         15          0           5


Procedure안에 있던 Query는 단 한번의 Parse만 이루어진 반면, Trigger안에 있던 Query는 5번 모두 Parse가 이루어진 것을 알 수 있다.

따라서 "Trigger내에 있는 SQL 문장을 Procedure로 변환하면 Parse가 줄어든다"라는 가이드는 정확했다고 할 수  있다.

여기서 한가지 의문을 가질 수 있다. 도대체 어떻게 Parse회수를 줄인 것일까?

위와 동일한 테스트를 session_cached_cursors 파라미터의 값을 0으로 변경하고, 즉 Session Cursor Sharing을 Disable시키고 수행한 결과는 다음과 같다.

alter session set session_cached_cursors=0;

SELECT /*+ procedure */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         15          0           5


SELECT /*+ trigger */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         15          0           5


Procedure내에 있던, Trigger내에 있던 Parse 회수는 동일하다. 즉, Cursor를 재활용 못하는 것이다.

이것을 반대로 해석해 보면, Oracle은 하나의 Procedure(정확하게 말하면 PL/SQL Block)내에서 수행되는 Query의 경우에는 Session 내에 Cache를 하며, Cache된 Query에 대한 Cursor는 Close하지 않고 재활용한다는 것이다. 따라서 Parse 회수를 줄일 수 있다. 결국 Oracle의 Parse라는 것은 Cursor를 Open한다는 것과 같은 의미이다.

아래의 Test 결과를 보면 이를 더 명확하게 알 수 있다.

@trace_on 10046 1

exec proc1;

exec proc1;

exec proc1;

@trace_off

BEGIN proc1; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           3

SELECT /*+ procedure */ *
FROM
 DUAL


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          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          9          0           3


아래 테스트 결과를 보면 더 명확한 그림을 그릴 수 있다. Procedure 단위(PL/SQL Block 단위)로 Cache가 이루어지는 것을 확인할 수 있다. Cursor의 Parse 회수가 "1"이 아닌 "2"라는 것은 PL/SQL Block단위로 Cache가 이루어진다는 것을 의미한다.

create or replace procedure proc1
is
begin
  for x in (select /*+ procedure */ * from dual) loop
    null;
  end loop;
end;
/

create or replace procedure proc2
is
begin
  for x in (select /*+ procedure */ * from dual) loop
    null;
  end loop;
end;
/

@trace_on 10046 1

exec proc1;
exec proc1;
exec proc1;

exec proc2;

exec proc2;
exec proc2;

@trace_off



BEGIN proc1; END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           3


BEGIN proc2; END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           3



SELECT /*+ procedure */ *
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         18          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         18          0           6



한가지 재미있는 것은 Oracle 11g에서는 위의 가이드가 더 이상 큰 의미가 없다는 것이다. Oracle 11g에서는 아래 테스트 결과와 같이 Trigger 내의 Query에 대해서도 Cursor를 재활용한다.


SELECT /*+ procedure */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0         15          0           5

SELECT /*+ trigger */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0         15          0           5



역시 새로운 버전을 사용함으로써 누릴 수 있는 혜택 중 하나라고 할 수 있겠다.
Trackbacks 0 : Comments 0

Write a comment