UTL_RECOMP
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.
Examples
Recompile all
objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();
Recompile
objects in schema
SCOTT sequentially:EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
Recompile all
objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);
Recompile
objects in schema JOE using the number of threads specified in the parameter
JOB_QUEUE_PROCESSES:EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');
Another simple method is to use utlrp.sql command located in $ORACLE_HOME/rdbms/admin
sqlplus "/as sysdba"
sqlplus >@$ORACLE_HOME/rdbms/admin/utlrp.sql;
