[출처] [Oracle is Mad] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g에서의 통계 정보 수집과 Cursor Invalidation 문제|작성자 욱짜
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시간이 지난 시점에 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을 시키는 것이다.
통계 정보 수집시 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
다음과 같은 상황에서 이런 현상이 발생한다.
- 통계 정보 수집으로 통계 정보가 변경된다.
- 통계 정보가 변경되면 관련된 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시간이 지난 시점에 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을 시키는 것이다.
통계 정보 수집시 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
반응형