合 【MOS】收集统计信息导致游标失效ROLL_INVALID_MISMATCH Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

APPLIES TO:

Oracle Database - Personal Edition - Version 10.1.0.2 and later

Oracle Database Cloud Schema Service - Version N/A and later

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Information in this document applies to any platform.

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 document describes details of how AUTO_INVALIDATE works.

SCOPE

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

DETAILS

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 executes a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a (pseudo-)random value up to _optimizer_invalidation_period sec from the time of this parse. The reason it does this is to randomly distribute the actual invalidation so as to avoid multiple parses occurring at the same time as much as possible.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) execution and 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 execution and 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.

Testcase for Cursor Invalidations using AUTO_INVALIDATE

Here is a short testcase demonstrating the above from a 10.2.0.3 database. It creates a simple table, runs a simple query on it, then gathers statistics and monitors what happens to the cached cursor for the query as time passes and the query is re-executed.

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。