Recompiling invalid program units

Question:
How can I recompile all invalid program units in my schema?
Answer:
Oracle offers two built-in packaged utilities to recompile invalid program units. In addition, Solomon Yakobson, an outstanding Oracle technologist, wrote his own recompile utility, which he has made generally available for our use. Finally, many PL/SQL editors offer their own features to accomplish this.
I review briefly the two Oracle options and the Yakobson utility below and then show usage of each in a performance comparison script.
UTL_RECOMP. New to Oracle Database 10g Release 2 and a very powerful utility designed specifically for recompilation, UTL_RECOMP offers "a packaged interface to recompile invalid PL/SQL modules, Java classes, index types, and operators in a database sequentially or in parallel" (from the description found in the utlrcmp.sql script that creates the UTL_RECOMP package and several associated tables and views). Only a SYSDBA account has the authority to run the UTL_RECOMP subprograms (RECOMP_SERIAL and RECOMP_PARALLEL), unless a DBA grants EXECUTE on this package to your schema or to PUBLIC.
For more information on this package, visit the PL/SQL Packages and Types Reference, at download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_recomp.htm#ARPLS380.
DBMS_UTILITY.COMPILE_SCHEMA. This utility has been around since Oracle Database Version 7. You can run it from any schema, and it will recompile either all program units (the only option available before Oracle Database 10g) or simply all invalid program units (available via a new argument in Oracle Database 10g's version of COMPILE_SCHEMA).
For more information on this subprogram, visit the PL/SQL Packages and Types Reference, at download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9332
Recompile script from Solomon Yakobson. You can run this handy utility (available via the Downloads link on the Best Practice PL/SQL page—oracle.com/technology/pub/columns/plsql) from any schema, and it will recompile all invalid programs according to the dependency order, so that when it is done, all programs without compile errors will have been recompiled and the status will have been set to VALID. This function returns the following values:
0 - Success. All requested objects are recompiled and VALID.
1 - At least one of the objects to be recompiled is not of a supported object type.
2 - At least one of the objects to be recompiled depends on an invalid object outside the scope of the current recompile request.
4 - At least one of the objects to be recompiled was compiled with errors and is INVALID.
So, as is typical in the world of Oracle and PL/SQL, you have alternatives. Which should you use? To answer this question I put together a script—recompile_comparison.sql, shown in Listing 3, —to check the performance of these three approaches. As you review these numbers, keep in mind that the absolute values are not important; rather, the difference between the values is key.
Code Listing 3: recompile_comparison.sql
-- Saved in recompile_comparison.sql

SET SERVEROUTPUT ON FORMAT WRAPPED

SPOOL recompile_comparison.log

DECLARE
-- What program do you want to recompile,
-- to force invalidation of other objects?
g_program VARCHAR2 ( 100 ) := 'package qu_all_objects';
--
g_start_time PLS_INTEGER;
--
l_dummy PLS_INTEGER;

PROCEDURE show_invalid ( context_in IN VARCHAR2 )
IS
l_invalid PLS_INTEGER;
BEGIN
SELECT COUNT ( * )
INTO l_invalid
FROM user_objects
WHERE status = 'INVALID';

DBMS_OUTPUT.put_line ( 'Invalid object count '
|| context_in
|| ': '
|| l_invalid
);
END show_invalid;

PROCEDURE before_recompile
IS
BEGIN
EXECUTE IMMEDIATE 'alter ' || g_program || ' compile reuse settings';

show_invalid ( 'before' );
-- Change get_cpu_time to get_time for versions earlier than 10g
g_start_time := DBMS_UTILITY.get_cpu_time;
END before_recompile;

PROCEDURE after_recompile ( approach_in IN VARCHAR2 )
IS
BEGIN
-- Change get_cpu_time to get_time for versions earlier than 10g
DBMS_OUTPUT.put_line ( 'Time for "'
|| approach_in
|| '" = '
|| TO_CHAR ( DBMS_UTILITY.get_cpu_time
- g_start_time
)
);
show_invalid ( 'after' );
END after_recompile;
BEGIN
before_recompile;
l_dummy := recompile ( o_owner => USER, display => FALSE );
after_recompile ( 'Yakobson utility' );
--
before_recompile;
DBMS_UTILITY.compile_schema ( USER
-- Comment out following line for versions earlier than 10g
, compile_all => FALSE, reuse_settings => TRUE
);
after_recompile ( 'dbms_utility.compile_schema' );
--
before_recompile;
SYS.UTL_RECOMP.recomp_serial ( USER );
after_recompile ( 'utl_recomp.serial' );
END;
/

SPOOL OFF
I ran the recompile_comparison script on Oracle Database 10g Release 2 and got these results:
SQL> @recompile_comparison

Time for
"Yakobson utility" = 6003
Time for
"dbms_utility.compile_schema" = 5900
Time for
"utl_recomp.recomp_serial" = 5936
I ran a modified version of recompile_comparison on Oracle9i Database Release 2 (adjusted to use DBMS_UTILITY.GET_TIME instead of GET_CPU_TIME, and no calls to UTL_RECOMP, because it does not exist in Oracle9i Database) and got these results:
Time for
"Yakobson utility" = 7244
Time for
"dbms_utility.compile_schema" = 22309
The call to DBMS_UTILITY.COMPILE_SCHEMA on Oracle9i Database took much longer, because that utility recompiles all program units in the schema, not just those that are invalid. The Oracle Database 10g version of this program allows you to specify that you want to compile only invalid objects.
From these results, I conclude the following:
1. In Oracle Database 10g, I will use DBMS_UTILITY.COMPILE_SCHEMA, which will run as efficiently as or better than the other options (when compiling only invalid objects) and can be run from any schema (no special privileges required).
2. With versions of Oracle Database prior to Oracle Database 10g, I will use Yakobson's utility. I will avoid DBMS_UTILITY.COMPILE_SCHEMA, because with versions of Oracle Database prior to Oracle Database 10g, it will always recompile all the program units in the schema and so will be much slower.