태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'no_invalidate'에 해당되는 글 1건

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

[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

티스토리 툴바