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

References

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

• DBMS_STATS
  • GATHER_TABLE_STATS
  • GET_PREFS

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;