-- procedure명으로 조회.
select * from SYSIBM.SYSPROCEDURES where PROCNAME like '%프로시저 명%';
-- oracle 의 dual 에 해당하는 테이블 :: sysibm.sysdummy1
select current_timestamp from sysibm.sysdummy1;
하지만, 왜 그럴까? 많은 사람들이 이 사실을 모르고, 심지어 프로시저를 사용하면 되는데 패키지가 무슨 필요? 라며 잘못된 견해를 전파한다.
프로시저가 아닌패키지를 사용해야 하는 이유는, 결론부터 말하면 패키지의 향상된 의존성(Dependency)관리때문이다.
아래 간단한 패키지와 프로시저가 있다. 이 둘의 기능(하는 일)은 완전히 동일하다. 다만 하나는 패키지로 구현되어 있고, 다른 하나의 프로시저로 구현되어 있을 뿐이다.
-- 패키지
create or replace package pkgtest as
procedure pkgtest_proc(v_id int);
end pkgtest;
/
create or replace package body pkgtest as
procedure pkgtest_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
end pkgtest;
/
-- 프로시저
create or replace procedure nopkg_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
/
여기서 주목해야 할 것은 pkgtest 패키지와 nopkg_proc 프로시저가 모두 pkgtest_table에 대해 의존성(Dependency)를 가지고 있다는 사실이다. 여기에서 간혹 심각한 문제가 발생한다.
Pkgtest_table에 대해 DDL을 수행하게 되면 이 테이블을 참조하고 있는 모든 객체에 대해 무효화(Invalidation)가 수행된다.아래 스크립트를 보자
-- pkgtest_table에 대해 의존성을 가지는 패키지와 프로시저가 Valid 상태이다.
SQL> select object_name,object_type, status
from dba_objects where object_name in ('PKGTEST', 'NOPKG_PROC');
OBJECT_NAM OBJECT_TYPE STATUS
---------- -------------------- --------------
NOPKG_PROCPROCEDURE VALID
PKGTEST PACKAGE VALID
PKGTEST PACKAGE BODYVALID
-- Pkgtest_table에 대해 Alter를 수행하면?
SQL> alter table pkgtest_table add name2 varchar(1);
OBJECT_NAMOBJECT_TYPE STATUS
------------------------------ --------------
NOPKG_PROCPROCEDURE INVALID
PKGTEST PACKAGE VALID
PKGTESTPACKAGE BODYINVALID
위의 결과에서 다음과 같은 재밌는 사실을 발견할 수 있다.
- NOPKG_PROC 프로시저는 기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
- PKGTEST 패키지는 좀 특이한다.
- PKGTEST 패키지 바디(body)기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
- 반면 PKGTEST 패키지 자체는 놀랍게도 여전히 VALID 상태이다.
이 패키지의 특정, 즉 의존성 체인을 깨는 특징이 왜 그렇게 중요할까? 그 이유는 하드 파싱과 관련이 있다.
만일 수십 개의 프로시저가 이 pkgtest_table에 대해 의존성을 가지는 상태에서 운영상의 이유로 pkgtest_table을 Alter했다고 가정해보자. 이수십 개의 프로시저가 모두 INVALID 상태가 될 것이고, 따라서 이 프로시저들을 수행하는 모든 쿼리는 재컴파일이 이루어져 한다. 붐~! 아마 library cache pin이라는 이름의 대기 현상의 증가하면서 자칫 시스템 장애를 불러일으킬 수 있다.
하지만 프로시저가 아닌 패키지로 되어 있었다면? 다행히 패키지 자체는 여전히 VALID 상태이이 때문에 이 패키지들을 사용하는 모든 쿼리 또한 재사용이 가능하 다. 실행 시점에 패키지 바디만 리컴파일해주면 된다. 옙!! 여러분은 방금 시스템 장애로부터 사장님을 구한 셈이다.
이것을 증명하기 위해 pkgtest_table을 Alter한 후, 다음과 같이 패키지와 프로시저의 수행 결과를 SQL Trace를 이용해 분석해보자.
-- 패키지를 참고하는 쿼리 문장은 하드 파싱을 수행하지 않는다.(즉, Library cache Miss가 발생하지 않으며 리컴파일또한 수행하지 않는다)
BEGIN pkgtest.pkgtest_proc(1); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 0 <-- 여기를 주목하세요!!Optimizer mode: ALL_ROWS
Parsing user id: 55
-- 하지만 프리시저를 사용하는 하드파싱을 수행한다.(즉, Library cache miss가 발생하고 리컴파일을 수행한다)
BEGIN nopkg_proc(1); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 1 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 1 <-- 여기를 주목. 모든 악의 근원!!!Optimizer mode: ALL_ROWS
Parsing user id: 55
왜 오라클이 굳이 패키지라는 복잡한 개념을 구현했는지 이해가 되는가? 프로그래밍의 간편함과 더불어 쿼리 재사용성 증가라는 탁월한 효과를 얻을 수 있기 때문이다.
이제 입질이 슬슬 오는가...?
PS)
SQL Server 2005 에서는 Statement Level의 Recompile을 지원함으로써 오라클의 패키지와 동일한 효과를 제공한다. 자세한 내용은 나중에...
하드파싱(리컴파일)에 의한 library cache pin 대기 현상에 대해서는 아래 필자의 책 참고~~
