태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Invalidation'에 해당되는 글 2건

  1. 2007.12.05 [Oracle is Mad] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g에서의 통계 정보 수집과 Cursor Invalidation 문제 (4)
  2. 2007.08.12 오라클 패키지의 마법을 풀어봅시다 - Part2 (2)

[Oracle is Mad] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g에서의 통계 정보 수집과 Cursor Invalidation 문제

오라클 2007.12.05 18:02
dbms_stats를 이용해 통계 정보를 수집한 후 갑자기 hard parse가 몰리면서 library cache pin 경합으로 인해 시스템 성능 저하 현상이 발생하는 경우가 많다.

다음과 같은 상황에서 이런 현상이 발생한다.
- 통계 정보 수집으로 통계 정보가 변경된다.
- 통계 정보가 변경되면 관련된 SQL Cursor들이 invalidate된다.
- SQL Cursor가 invalidate되면 다음 번 Access때 hard parse가 발생한다.
- hard parse시에는 LCO에 대해 library cache pin을 exclusive모드로 획득한다.
- hard parse가 진행 중인 LCO를 실행하려는 다른 session들은 library cache pin 이벤트를 대기한다.

즉, 통계 정보 갱신에 의한 Cursor invalidation으로 인해 hard parse storm이 발생하고 이로 인해 성능 문제가 발생한다.

9i부터는 통계 정보 수집시 no_invalidate 옵션으로 Cursor invalidation을 제어할수 있다.
- no_invalidate == false: 통계 정보 갱신 후 관련된 SQL Cursor들을 즉시 invalidate한다. 9i에서의 기본값이다.
- no_invalidate == true: 통계 정보 갱신 후 관련된 SQL Cursor들을 invalidate하지 않는다. SQL Cursor들이 Shared Pool에서 age-out 된 후 reaload될 때 갱신된 통계 정보가 사용된다.

위의 내용을 보면 알겠지만, no_invalidate 옵션은 참으로 애매모호한 옵션이다. false로 지정하면 hard parse storm이 발생해서 문제가 생길 것이고, true로 지정하면 갱신된 통계 정보가 반영되지 않으므로 곤란하다.

이런 고민을 해결하기 위해 Oracle 10g에서 dbms_stats.auto_invalidate(실제 값은 Null) 옵션이 추가되었다.
- no_invalidate == dbms_stats.auto_invalidate: 통계 정보 갱신 후, 통계 정보 반영을 일정 시간 지연해서 적용한다. 이 시간 값은 _optimizer_invalidation_period 파라미터로 결정된다. 기본값이 18000(초)로 5시간이다. 즉 통계 정보 갱신 후 5시간이 지난 시점에(5시간에 걸쳐) Cursor가 Access되면 그 때 통계 정보를 반영한다. 10g에서는 auto_invalidate가 기본값이다.

따라서 10g에서는 통계 정보를 수집해도 즉각 반영되지 않고, 자연스럽게 hard parse storm도 피할 수 있다. 대신 일정 시간 이후 반영이 되므로 일정 목적을 달성할 수 있다. 이런에  현상 때문에 Oracle 10g를 처음 운영하는 환경에서 오해들이 있었다. "왜 통계 정보를 수집했는데 반영이 안돼? 버그 아냐"... 다행히 버그가 아니고 원래 그렇게 설계가 된 것이다.

만일, auto_invalidate를 사용하면서 특정 SQL Cursor는 즉시 invalidation하고 싶다면 어떻게 해야할까?
가장 간단한 방법은 alter system flush shared_pool을 이용해서 모든 Cursor를 invalidation시키는 것이다.
더 좋은 방법은 alter session set "_optimizer_invalidation_period" = 1; 과 같은 값을 적용해서 가능한 빨리 invalidation을 시키는 것이다. 물론 이것은 Auto Invalidation의 원래 취지에는 어긋난다.

통계 정보 수집시 SQL Cursor의 invalidation 문제는 지금까지 문서화가 잘 안되어 있어서 많은 오해들이 있었다. 이 글이 이런 오해를 푸는데 도움이 되었으면 한다.

PS)
auto_invalidate 옵션으로 통계 정보 갱신 후 일정 시간이 지나 SQL Cursor를 새로 생성할 시점이 오면 Oracle은 어떻게 통계 정보를 반영할까?
기존의 SQL Cursor를 invalidate시키는 것이 아니라 새로운 Child Cursor를 생성한다. 실제 로 테스트를 해보면 새로운 Child Cursor가 생기고 v$sql_shared_cursor.roll_invalid_mismatch가 발생한 것으로 관찰된다.

참조)
http://wiki.ex-em.com/index.php/OPTIMIZER_INVALIDATION_PERIOD
신고
Trackback 0 : Comments 4
  1. 재혁재우 2008.11.21 17:40 신고 Modify/Delete Reply

    설명을 읽었는데 그래도 이해를 못해서 질문드립니다. --;

    전제 : 만약 하나의 테이블(T1)이 있다고 가정하고 현재의 실행계획를 A라 하고
    통계정보를 갱신한 후의 실행계획을 B라고 할때... (단, 무조건 실행계획은 바뀐다고 가정한다)

    1. T1 테이블에 대해 no_invalidate == true 으로 통계정보 수집한 후,
    T1을 사용하는 SQL Cursor를 요청하면 그 Cursor가 Shared Pool에서 age-out 되지 않으므로
    만약 지속적인 Cursor 사용이 요청되면 영원히 갱신된 통계 정보는 사용되지 못해 실행계획 A만 사용하는 건가요?

    2. T1에 대해 no_invalidate == dbms_stats.auto_invalidate 으로 통계정보 수집하면
    _optimizer_invalidation_period 기간안에 해당 Cursor가 무조건 invalidate 되나요?

    3. 2번 질문에 이어서
    _optimizer_invalidation_period 기간안에 invalidate되지 못한 cursor 사용요청이 오면 실행계획 A가 사용되나요?
    _optimizer_invalidation_period 기간안에 invalidate된 cursor 사용요청이 오면 실행계획 B가 사용되나요?
    _optimizer_invalidation_period 기간이후에는 invalidate 되어 있기 때문에 실행계획 B가 사용되나요?


    이상입니다.

  2. 욱짜 2008.11.21 23:03 신고 Modify/Delete Reply

    Auto Invalidation의 의미를 이렇게 이해하시면 좋겠습니다.

    "Invalidation을 하기는 하되 한꺼번에 하지 않고 적당히 나누어서 Query가 실행될 때 하겠다"

    이 관점으로 생각하면 모든 것이 명확해집니다.

    1번: 언젠가는(나누어서 하니까) Invalidation이 될 것이기 때문에 실행 계획은 그 시점에 바뀌게 됩니다.

    2번: 주어진 기간에 Query가 수행될 때(정확하게 언제인지는 알 수 없지만) Invalidation이 이루어집니다. 만일 주어진 시간안에 수행이 되지 않으면? 나중에 수행될 때 Invalidation이 될 것이기 때문에 문제가 안됩니다.

    3번: Yes, Yes, Yes 입니다.

    주의할 것은 Invallidation을 시켜주는 Process가 따로 있는 것이 아니라 Query를 수행하는 Process가 (내부적인) 알고리즘에 따라 적당하게 Invalidation을 시킨다는 것입니다. 이말은 Query가 수행이 안되면 Invalidation이 발생하지 않는다는 것입니다. 수행되지 않은 Query는 Invalidation이 이루어질 필요도 없으니까 전혀 문제가 안됩니다.

  3. kpt0507 2009.09.18 16:54 신고 Modify/Delete Reply

    안녕하세요 욱짜님 한 가지 궁금한 것이 있어서 질문 드립니다.

    현재 시스템에 sql_id='9kdgr0v1kjadc' 에 두 개의 child_cursor 가 있습니다.

    하나는 child_number=0 하나는 child_number=1 입니다. ( 당연하겠지요 ^^;)


    select child_number,roll_invalid_mismatch from v$sql_shared_cursor where sql_id = '9kdgr0v1kjadc'; 의

    결과는
    child_number | roll_invalid_mismatch
    0 Y
    1 N

    입니다.

    당연히 모든 session 이 child_number=1 인 child_cursor를 share 할 것이라 생각했는데

    select * from table(dbmx_xplan.display_cursor('9kdgr0v1kjadc')); 를 해서 현재

    사용되는 실행계획을 보면

    child_number=0 인 child_cursor의 실행계획을 보여줍니다.

    질문)
    child_number 가 max인 child_cursor가 실제 share 되는 cursor가 맞는지 예외는 없는지요?

    • 욱짜 2009.09.18 17:27 신고 Modify/Delete

      반갑습니다.

      번거로우시겠지만, 위의 질문을 다른 분들도 공유할 수 있도록 Ask Exem에 올려주시겠습니까?

      http://ask.ex-em.com

      Email 주소만 정확하게 적어주시면 게시판을 통해 질문/답변/의견 교환이 가능합니다. Email 주소는 외부에 노출되지 않으므로 걱정하지 않으셔도 됩니다.

      감사합니다.

Write a comment


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

오라클 2007.08.12 01:57

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

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

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

신고
Trackback 0 : Comments 2
  1. 고성일 2008.02.26 09:36 신고 Modify/Delete Reply

    담아 갑니다~!

  2. 삼이 2010.02.22 15:05 신고 Modify/Delete Reply

    좋은 정보 감사합니다. 담아갈께요~

Write a comment

티스토리 툴바