11G OCM Gather statistics on a specific table without invalidating cursors

Contents

  1. 1 References
  2. 2 Overview
  3. 3 Procedure
    1. 3.1 Gather Statistics Without Invalidating Cursors
    2. 3.2 Get Current Preference for NO_INVALIDATE


References

Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)

Overview

The 11G OCM Upgrade Exam objective is:

Gather statistics on a specific table without invalidating cursors

Procedure

Gather Statistics Without Invalidating Cursors

The GATHER_TABLE_STATS procedure has a no_invalidate parameter which is described as:

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

An example of use would be: 

EXEC dbms_stats.gather_table_stats( 'SH', 'CUSTOMERS', no_invalidate => TRUE )

Get Current Preference for NO_INVALIDATE

The current preference for the NO_INVALIDATE parameter is obtained by the GET_PREFS procedure: 

SELECT dbms_stats.get_prefs( 'NO_INVALIDATE' ) FROM dual;
DBMS_STATS.GET_PREFS('NO_INVALIDATE')
DBMS_STATS.AUTO_INVALIDATE