|Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
Part Number E16760-04
This chapter contains the following topics:
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects. Although invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.
Parallel recompilation can exploit multiple CPUs to reduce the time taken to recompile invalid objects. The degree of parallelism is specified by the first argument to RECOMP_PARALLEL Procedure.
In general, a parallelism setting of one thread for each available CPU provides a good initial setting. However, please note that the process of recompiling an invalid object writes a significant amount of data to system tables and is fairly I/O intensive. A slow disk system may be a significant bottleneck and limit speedups available from a higher degree of parallelism.
This package uses the job queue for parallel recompilation.
This package must be run using
You must be connected
SYSDBA to run this script.
This package expects the following packages to have been created with VALID status:
There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.
Recompile all objects sequentially:
Recompile objects in schema
Recompile all objects using 4 parallel threads:
Recompile objects in schema JOE using the number of threads specified in the parameter
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');
Table 232-1 UTL_RECOMP Package Subprograms
Recompiles invalid objects in a given schema, or all invalid objects in the database, in parallel
Recompiles invalid objects in a given schema or all invalid objects in the database
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.
UTL_RECOMP.RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
Table 232-2 RECOMP_PARALLEL Procedure Parameters
The number of recompile threads to run in parallel. If
The schema in which to recompile invalid objects. If
Flag values are intended for internal testing and diagnosability only.
The parallel recompile exploits multiple CPUs to reduce the time taken to recompile invalid objects. However, please note that recompilation writes significant amounts of data to system tables, so the disk system may be a bottleneck and prevent significant speedups.
This procedure recompiles invalid objects in a given schema or all invalid objects in the database.
UTL_RECOMP.RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);