13 UTL_RECOMP

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 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

---

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

---

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