많은 오라클 전문가들이 프로시저대신 패키지를 사용할 것을 권장한다. 특히 패키지를 만든 오라클 사람들이...
하지만, 왜 그럴까? 많은 사람들이 이 사실을 모르고, 심지어 프로시저를 사용하면 되는데 패키지가 무슨 필요? 라며 잘못된 견해를 전파한다.
프로시저가 아닌 패키지를 사용해야 하는 이유는, 결론부터 말하면 패키지의 향상된 의존성(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_PROC PROCEDURE VALID
PKGTEST PACKAGE VALID
PKGTEST PACKAGE BODY VALID
-- Pkgtest_table에 대해 Alter를 수행하면?
SQL> alter table pkgtest_table add name2 varchar(1);
OBJECT_NAM OBJECT_TYPE STATUS
---------- -------------------- --------------
NOPKG_PROC PROCEDURE INVALID
PKGTEST PACKAGE VALID
PKGTEST PACKAGE BODY INVALID
위의 결과에서 다음과 같은 재밌는 사실을 발견할 수 있다.
- NOPKG_PROC 프로시저는 기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
- PKGTEST 패키지는 좀 특이한다.
- PKGTEST 패키지 바디(body) 기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
- 반면 PKGTEST 패키지 자체는 놀랍게도 여전히 VALID 상태이다.
패키지의 이러한 특징을 가리켜 흔히 "패키지는 의존성 체인을 깬다"라는 표현을 사용한다. 위의 예를 들면 pkgtest_table이 변경됨으로써 pkgtest 패키지가 무효화될 위기임에도 불구하고 중간에 패키지 바디라는 중간 객체만이 무효화되고 패키지 자체는 무효화되지 않는다.
이 패키지의 특정, 즉 의존성 체인을 깨는 특징이 왜 그렇게 중요할까? 그 이유는 하드 파싱과 관련이 있다.
만일 수십 개의 프로시저가 이 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 대기 현상에 대해서는 아래 필자의 책 참고~~
OWI Advanced Oracle Wait Interface in 10g
앞서 블로그에서 패키지가 프로시저에 비해 왜 뛰어난지에 대해 간략하게 논의한 바 있다.
또 한번의 테스트를 통해서 패키지를 적절히 사용할 경우 얼마나 큰 효과를 얻을 수 있는지 알아보자.
오라클이 제공하는 프로시저나 펑션을 사용해서 비지니스 로직을 구현해 본 경험이 있는 사람이라면 치를 떠는 한가지 현상이 있다. 바로 library cache pin 경합 현상이다.
예를 들어 특정 쇼핑몰에서 가격을 계산하는 특정 로직을 get_price(goods_no) 라는 함수로 구현했다고 하자. 이 경우 다음과 같이 수 백, 수 천개의 SQL 문장이 이 함수를 사용하게 된다.
SELECT get_price(goods_no) as price, goods_no, goods_name, goods_type ...
SELECT order_no, order_item, get_price(goods_no)*order_cnt as order_amount, ...
...
시스템이 Busy하게 구동되는 상황이라면 위와 같은 수백, 수천 개의 SQL문장이 동시에 수십 개의 세션에서 수행되고 있을 것이다. 만일 이 상황에서 get_price 펑션의 내용을 바꿀려고 한다면? 업무 규칙이 바뀌어서 가격을 계산하는 로직의 변경되었다면 get_price 펑션을 변경해야 한다.
문제는 이 수백, 수천 개의 SQL 문장이 모두 get_price 펑션에 대해 의존성을 가진다는 사실이다.
따라서 get_price 펑션을 재정의(create or replace function...) 하는 순간 이 펑션을 사용하는 모든 SQL 문장이 무효화(Invalidate)된다. 펑션이 재정의되고 나면 순식간에 수백, 수천개의 SQL 문장을 동시에 수십 개의 세션이 리컴파일하는 무시무시한 사태가 생기게 되는 것이다.
결과는? Wait Event로 보면 library cache pin/library cache lock과 같은 Library Cache 관련 대기이벤트가 증가하면서 세션 수가 급증하고 결국 DB를 리스타트해야 하는 상황이 발생한다.
그렇다면 해결책은 없는 건가? 시스템을 운영하다보면 쇼핑몰의 가격 정책이 바뀌는 것과 같은 일을 종종 발생할 수 밖에 없지 않겠는가?
해결책은 바로 펑션이나 프로시저가 아닌 패키지를 사용하는 것이다.
만일 get_price 펑션이 아닌, price_pkg.get_price 와 같이 패키지를 구성되어 있다면, price_pkg.get_price 펑션(패키지 바디)이 변경되더라도 price_pkg 패키지 자체는 무효화되지 않는다. 따라서 price_pkg.get_price 를 사용하는 모든 SQL 문장도 무효화되지 않는다.
결과는? 모든 SQL 문장은 리컴파일 과정 없이 그대로 재활용된다.
놀랍지 않은가? 앞서 펑션이나 프로시저의 변경 문제를 해결하기 위해 수많은 DBA와 시스템 운영자들이 온갖 수를 다 쓰고는 하지만, 해결책은 매우 간단하데 있다. 내가 본 어떤 곳에서는 이런 문제를 해결하기 위해 프로시저와 펑션을 원천적으로 사용하지 못하도록 한 곳도 있다. 우습다고 밖에 할 수 없다.
아래 테스트 결과는 다음과 같은 동일한 시나리오를 펑션을 사용한 경우와 패키지를 사용한 경우의 성능 차이를 비교한 것이다.
- 총 5000 종류의 SQL 문장이 수행되며, 모든 SQL 문장은 특정 펑션 또는 패키지를 사용한다.
- 동시에 5개의 세션이 5000 종류의 SQL 문장을 수행하며, 펑션 또는 패키지 바디를 재성성(리컴파일)한다.
- 이 작업을 각 세션이 10번씩 반복한다.
아래에 그 결과를 비교한 표가 있다.
| 펑션을 사용한 경우 | 패키지를 사용한 경우 |
| Type=EVENT, Name=events in waitclass Other, Value=32616(cs) Type=EVENT, Name=library cache pin, Value=6234(cs) Type=EVENT, Name=jobq slave wait, Value=4883(cs) Type=EVENT, Name=library cache lock, Value=418(cs) Type=EVENT, Name=latch: library cache, Value=294(cs) Type=EVENT, Name=latch: shared pool, Value=25(cs) Type=EVENT, Name=library cache load lock, Value=17(cs) Type=EVENT, Name=latch: library cache lock, Value=12(cs) Type=EVENT, Name=latch: library cache pin, Value=7(cs) Type=EVENT, Name=latch: row cache objects, Value=1(cs) Type=EVENT, Name=cursor: mutex X, Value=1(cs) Type=STATS, Name=session pga memory max, Value=14010296 Type=STATS, Name=redo size, Value=1175112 Type=STATS, Name=execute count, Value=253869 Type=STATS, Name=parse count (total), Value=253423 Type=STATS, Name=parse count (hard), Value=60971 Type=STATS, Name=parse time elapsed, Value=55413 Type=STATS, Name=session logical reads, Value=15751 Type=STATS, Name=redo entries, Value=4413 Type=STATS, Name=session cursor cache hits, Value=933 Type=STATS, Name=sorts (memory), Value=172 Type=STATS, Name=user commits, Value=60 Type=STATS, Name=physical reads, Value=0 Type=STATS, Name=sorts (disk), Value=0 Type=TIME, Name=DB time, Value=57423(cs) Type=TIME, Name=sql execute elapsed time, Value=57417(cs) Type=TIME, Name=parse time elapsed, Value=54376(cs) Type=TIME, Name=hard parse elapsed time, Value=12297(cs) |
Type=EVENT, Name=jobq slave wait, Value=1463(cs) Type=EVENT, Name=latch: library cache, Value=1016(cs) Type=EVENT, Name=library cache pin, Value=431(cs) Type=EVENT, Name=library cache load lock, Value=73(cs) Type=EVENT, Name=latch: library cache pin, Value=61(cs) Type=EVENT, Name=latch: library cache lock, Value=40(cs) Type=EVENT, Name=library cache lock, Value=22(cs) Type=EVENT, Name=latch: shared pool, Value=10(cs) Type=EVENT, Name=events in waitclass Other, Value=0(cs) Type=STATS, Name=session pga memory max, Value=14206904 Type=STATS, Name=redo size, Value=875492 Type=STATS, Name=execute count, Value=253413 Type=STATS, Name=parse count (total), Value=253126 Type=STATS, Name=session logical reads, Value=13299 Type=STATS, Name=redo entries, Value=3461 Type=STATS, Name=parse time elapsed, Value=1083 Type=STATS, Name=session cursor cache hits, Value=805 Type=STATS, Name=sorts (memory), Value=145 Type=STATS, Name=parse count (hard), Value=60 Type=STATS, Name=user commits, Value=60 Type=STATS, Name=sorts (disk), Value=0 Type=STATS, Name=physical reads, Value=0 Type=TIME, Name=DB time, Value=5226(cs) Type=TIME, Name=sql execute elapsed time, Value=5220(cs) Type=TIME, Name=parse time elapsed, Value=1239(cs) Type=TIME, Name=hard parse elapsed time, Value=15(cs) |
가장 주목할 것은 펑션을 사용한 SQL 리컴파일이 6만 여번(parse count(hard))인데 반해 패키지를 사용한 경우에는 0번, 즉 SQL 리컴파일이 전혀 이루어지지 않았다는 것이다. 이 결과는 DB time으로 극명히 드러나는데, 펑션을 사용한 경우 DB time이 574초인데 반해, 패키지를 사용한 경우에는 52초로 패키지를 사용한 경우 10배 이상의 성능을 보이는 것을 알 수 있다. 또한 library cache pin 이벤트 대기 시간을 주목하자.
놀랍지 않은가? 패키지의 고유의 특정인 커서 재활용 효과가 이런 큰 성능 차이를 보여준다.
이 글을 읽는 사람들만이라도 이런 패키지의 고유의 특징을 잘 이해해서 Production 시스템에서는 가급적이면 프로시저나 펑션이 아닌 패키지를 사용해 보는 것이 어떠하겠는가...
출처 :
http://blog.naver.com/ukja?Redirect=Log&logNo=120040673086
http://blog.naver.com/ukja?Redirect=Log&logNo=120041227938
