Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

dbms_stat로 table/index에 대한 통계 정보가 새로 생성하면
이후 새로 생성되는 cursor의 경우 새로운 통계정보에 의한 sql plan이 만들어 지지만,
기존의 library cache에 저장된 sql은 새로운 sql plan을 얻기 위해 invalidation 되어
해당 sql은 다시 hard pare 단계를 거쳐 새로운 통계정보에 의한 sql plan을 획득하게 됩니다.

이때 shared pool내의 많은 sql 들이 hard parse 되면서
latch contention과 CPU 자원을 많이 소모하게 되는데,
이러한 현상은 "hard-parse spike"라고 합니다.

이러한 현상은 운영자들에게는 많은 부담이 될텐데요..

이러한 현상을 막기 위해 NO_INVALIDATE option을 이용해 기존의 sql에 대한 invalidation을 제어합니다. 그러나 이는 새로운 통계정보에 의한 sql plan을 당장 사용하지 못하니 이 또한 그리 좋은 방법은 아니겠죠..

10g 부터는 auto invalidate 기능이 추가 되어 "hard parse spike"를 많이 감소시켰다고 하네요..

다음은 metalink의 rolling cursor invalidation에 대한 설명입니다...


문서 557661.1   
Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

Purpose

Starting with Oracle10g, the DBMS_STATS package offers the AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows the user to specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.
According to the documentation the values NO_INVALIDATE can take are:

• TRUE: does not invalidate the dependent cursors
• FALSE: invalidates the dependent cursors immediately
• AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors

This article describes details of how AUTO_INVALIDATE works.

Scope and Application

This article may be of interest to DBAs interested in the behaviour of DBMS_STATS with respect to cursor invalidations.

Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

When statistics are modified by DBMS_STATS, new cursors not yet cached in the shared pool will use them to get execution plans. Existing cursors cached in the shared pool cannot update their execution plans however. Instead, such cursors are invalidated and new versions (children cursors) are created which get execution plans based on the updated statistics. This involves a hard-parse operation which is more expensive in resource consumption than a soft-parse which simply reuses a cached cursor.
An important question to consider is: when does this invalidation occur in time after the statistics have been modified ?

Cursor Invalidations on Gathering Statistics prior to Oracle10g

In releases prior to Oracle10g gathering statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors, unless NO_INVALIDATE was set to TRUE. An invalidation of a cached cursor means it has to be hard-parsed the next time it is executed. If large numbers of such cursors had to be invalidated and immediately re-executed e.g. due to DBMS_STATS being used on a popular object during a time period of heavy user workload, this would result in a hard-parse spike which could have serious effects on performance including high CPU usage, heavy library cache and shared pool latch contention with subsequent slowdown in application user response times.

Setting NO_INVALIDATE to TRUE would prevent such spikes but this meant that cursors would not notice the updated object statistics and would continue using existing execution plans until hard-parsed. Such a hard parse could happen on a cursor reload (i.e. on the next execution following the cursor being automatically flushed out of the shared pool due to inactivity and heavy usage of other cursors) or after a manual flushing of the shared pool (which could itself also result in hard-parse spikes as most of the flushed-out cursors would need to do a hard parse on their next execution.)

Cursor Invalidations with Oracle10g and AUTO_INVALIDATE

With the AUTO_INVALIDATE option the goal is to spread out the cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes.

In this way a cached cursor depending on an object whose statistics have been modified by DBMS_STATS will be invalidated as follows:

• when DBMS_STATS modifies statistics for an object, all current cached cursors depending on this object are marked for rolling invalidation. Let's call this time T0.
  
• the next time a session parses a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a random value up to _optimizer_invalidation_period sec from the time of this parse. The default for this parameter is 18000 sec i.e. 5 hours. Let's call the time of this parse T1 and the timestamp value Tmax. On this (first) parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)
  
• on every subsequent parse of this cursor (which is now marked for rolling invalidation and timestamped) we check whether the current time T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor again, as happened on the first (soft) parse at time T1. If Tmax has been exceeded, we invalidate the cached cursor and create a new version of it (a new child cursor) which uses the new statistics of the object to generate its execution plan. The new child is marked ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could not share the previous child.

From the above descriptions, it follows that:

• a cursor which is never parsed again after being marked for rolling invalidation will not be invalidated and may eventually be flushed out of the shared pool if memory becomes scarce
• a cursor which is only parsed once after being marked for rolling invalidation will not be invalidated (it will only be timestamped) and again may be eventually flushed out if memory in the shared pool becomes scarce
• cursors which are regularly reused will become invalidated on the next parse that happens after the timestamp Tmax has been exceeded

It should be clear that the above method is efficient in that it incurs the overhead of invalidations only for frequently reused cursors.

Exception: parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources are insignificant to their total resource usage.