14 UTL_RECOMP

The UTL_RECOMP package recompiles invalid PL/SQL modules, invalid views, index types, and operators in a database.

This chapter contains the following topics:

• Using UTL_RECOMP

  • Overview

  • Operational notes

  • Examples

• UTL_RECOMP Subprograms

Using UTL_RECOMP

• Overview

• Operational Notes

• Examples

Overview

UTL_RECOMP is particularly useful after a major-version upgrade that typically invalidates all PL/SQL objects.

Although invalid objects are recompiled automatically on use, it is useful to run this before operation to eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.

Operational Notes

• This package must be run using ttIsql.

• To use this package, you must be the instance administrator and run it as SYS.UTL_RECOMP.

• This package expects the following packages to have been created with VALID status:

  • STANDARD (standard.sql)

  • DBMS_STANDARD (dbmsstdx.sql)

  • DBMS_RANDOM (dbmsrand.sql)

• There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.

• Because TimesTen does not support DBMS_SCHEDULER, the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore, there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL in TimesTen.

Examples

Recompile all objects sequentially:

Command> EXECUTE SYS.UTL_RECOMP.RECOMP_SERIAL();

Recompile objects in schema SCOTT sequentially:

Command> EXECUTE SYS.UTL_RECOMP.RECOMP_SERIAL('SCOTT');

UTL_RECOMP Subprograms

Table 14-1 summarizes the UTL_RECOMP subprograms, followed by a full description of each subprogram.

Table 14-1 UTL_RECOMP Package Subprograms

Subprogram	Description
RECOMP_PARALLEL Procedure	Recompiles invalid objects in a given schema, or all invalid objects in the database, in parallel. As noted earlier, in TimesTen the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore, there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL in TimesTen.
RECOMP_SERIAL Procedure	Recompiles invalid objects in a given schema or all invalid objects in the database.

Note:

• The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

• The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

RECOMP_PARALLEL Procedure

This procedure uses the information exposed in the DBA_Dependencies view to recompile invalid objects in the database, or in a given schema, in parallel.

In TimesTen, the threads value is always 1 regardless of how it is set. As a result, there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL.

Syntax

UTL_RECOMP.RECOMP_PARALLEL(
   threads  IN   BINARY_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   BINARY_INTEGER DEFAULT 0);

Parameters

Table 14-2 RECOMP_PARALLEL Procedure Parameters

Parameter	Description
threads	The number of recompile threads to run in parallel In TimesTen, threads is always 1.
schema	The schema in which to recompile invalid objects If NULL, all invalid objects in the database are recompiled.
flags	Flag values intended for internal testing and diagnosability only

RECOMP_SERIAL Procedure

This procedure recompiles invalid objects in a given schema or all invalid objects in the database.

Syntax

UTL_RECOMP.RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   BINARY_INTEGER DEFAULT 0);

Parameters

Table 14-3 RECOMP_SERIAL Procedure Parameters

Parameter	Description
schema	The schema in which to recompile invalid objects If NULL, all invalid objects in the database are recompiled.
flags	Flag values intended for internal testing and diagnosability only