오라클 패키지의 마법을 풀어봅시다 - Part2

추천  :   http://ukja.tistory.com/29

앞서 블로그에서 패키지가 프로시저에 비해 왜 뛰어난지에 대해 간략하게 논의한 바 있다.

또 한번의 테스트를 통해서 패키지를 적절히 사용할 경우 얼마나 큰 효과를 얻을 수 있는지 알아보자.

오라클이 제공하는 프로시저나 펑션을 사용해서 비지니스 로직을 구현해 본 경험이 있는 사람이라면치를 떠는 한가지 현상이 있다. 바로 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 시스템에서는 가급적이면 프로시저나 펑션이 아닌 패키지를 사용해 보는 것이 어떠하겠는가...