12 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

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 because this will either 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');

Summary of UTL_RECOMP subprograms

Table 12-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.

Notes:

  • 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 12-2 RECOMP_PARALLEL procedure parameters

Parameter Description

threads

The number of recompile threads to run in parallel. If NULL, use the value of 'job_queue_processes'.

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 are 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 12-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 are intended for internal testing and diagnosability only.