My Oracle Support Banner

Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Cursor Invalidations on Gathering Statistics prior to Oracle10g
 Cursor Invalidations with Oracle10g and AUTO_INVALIDATE
 Testcase for Cursor Invalidations using AUTO_INVALIDATE

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.