Oracle Recompile Invalid Objects with utlrp.sql

calendar Feb 23, 2024 / · 8 min read · oracle administration objects dba_objects utl_recomp dbms_utility  ·
Share on: linkedin copy

Oracle Recompile Invalid Objects with utlrp.sql

Purpose

Recompiling invalid objects is a routine job that follows an upgrade or a patch. You run one Oracle-supplied script, and the database works through every invalid object, so users do not meet them one by one. The script is utlrp.sql. It lives in the $ORACLE_HOME/rdbms/admin directory, and the ? in the path below is the SQL*Plus shortcut for that home.

An object becomes invalid when something it depends on changes. A view can query a table. A PL/SQL subprogram can call other subprograms or read tables. If the referenced object changes in a way that matters, the dependent object is marked invalid. The database can repair many of these by itself when the object is used. A planned recompile moves that work to a time you choose.

Finding and diagnosing invalid objects have their own posts. To find which objects are invalid, see How to Identify and Fix Invalid Objects in Oracle Database. To read the errors that keep an object invalid, see Oracle Compilation Errors and Warnings Using SHOW ERRORS and DBA_ERRORS.

Code

 1-- 1. Count and list the invalid objects before you start
 2SELECT object_name, object_type FROM dba_objects
 3WHERE status = 'INVALID';
 4
 5-- 2. Recompile every invalid object with the Oracle script (run as SYS or a SYSDBA user)
 6@?/rdbms/admin/utlrp.sql
 7
 8-- 3. Recompile all invalid objects in serial
 9EXECUTE UTL_RECOMP.RECOMP_SERIAL();
10
11-- 4. Recompile the invalid objects of one schema in serial
12EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
13
14-- 5. Recompile all invalid objects with 4 parallel threads
15EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);
16
17-- 6. Recompile one object by hand
18ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;
19
20-- 7. Revalidate one procedure with DBMS_UTILITY
21begin
22   dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1);
23end;
24/
25
26-- 8. Check the list again after the run
27SELECT object_name, object_type FROM dba_objects
28WHERE status = 'INVALID';

Code Breakdown

Step 1 and step 8: the before and after check

  • SELECT object_name, object_type FROM dba_objects WHERE status = 'INVALID' lists the invalid objects in the whole database. Run it before and after the recompile. The two lists show what the run fixed and what is left.

Step 2: the Oracle script

  • @?/rdbms/admin/utlrp.sql runs the script from SQL*Plus. The script recompiles all objects in an invalid state, including packages, procedures and types.
  • Who can run it. Run it as the SYS user, or as another user that has the SYSDBA privilege.
  • How it chooses serial or parallel. The script runs in serial or in parallel on its own. It decides from the number of CPUs (the cpu_count parameter) multiplied by the number of threads for each CPU (the parallel_threads_per_cpu parameter). On a RAC system, the number of parallel threads is added across all nodes.

Steps 3 to 5: the UTL_RECOMP package

  • RECOMP_SERIAL recompiles invalid objects one after another. With no argument it covers all invalid objects in the database. With a schema name it covers that schema only.
  • RECOMP_PARALLEL does the same work with several threads. Its first argument is the number of threads. If you pass NULL, the package uses the value of job_queue_processes. Its second argument is the schema. If you pass NULL, it covers the whole database.
  • flags is a third argument on both procedures. Leave it at its default. Oracle documents it as meant for internal testing and diagnosis.

Steps 6 and 7: single objects

  • ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS recompiles one object with an ALTER statement. Use the ALTER statement that matches the object type.
  • DBMS_UTILITY.VALIDATE revalidates one object by schema, name and namespace. In the Oracle example, namespace 1 is used for a procedure and namespace 2 for a package body.

Key Points

  • Invalid objects often repair themselves. When an invalid object is used, the database recompiles it. The cost is that the first caller waits. Oracle notes that revalidation adds latency to application execution, and the added latency can be significant when many objects are invalid.
  • Oracle recommends a planned recompile. The upgrade guide says to recompile invalid objects as part of the patching and upgrade procedure, so that you resolve problems and dependencies before users meet the invalid objects.
  • Run time grows with the count. Recompilation time is proportional to the number of invalid objects. After an upgrade that leaves many objects invalid, utlrp.sql can take a long time.
  • Invalidation cascades. If an object becomes invalid, all of its dependent objects are invalidated at once. One change can therefore produce a long list.
  • Parallel work is I/O heavy. Recompiling an object writes a significant amount of data to system tables. A slow disk system can limit the gain from more threads.
  • Sessions can see errors during a patch. If a patch is applied while an application is in use, a session can raise ORA-04061, ORA-04064, ORA-04065 or ORA-04068. Restart the application sessions after the patch.

Insights and Best Practices

Rehearse the recompile in the upgrade test

Oracle advises you to run utlrp.sql during upgrade tests, so that recompilation time is part of the upgrade plan. The time is proportional to the number of invalid objects, and only a test shows how many your upgrade leaves behind. Write the measured time in your runbook, and use it to set the length of the maintenance window.

Choose between the script and the package

Use utlrp.sql when you want the standard step after an upgrade or a patch. It picks serial or parallel work for you. Use UTL_RECOMP when you want control. You can set the thread count, or limit the run to one schema. For a very large number of invalid objects, Oracle names UTL_RECOMP as the tool for a mass recompilation.

If the system also serves users during the run, start with a modest thread count. The package documentation says that one thread for each available CPU is a good initial setting, and it warns that the work is fairly I/O intensive. Watch the disk before you raise the number.

Watch progress while it runs

RECOMP_PARALLEL records the objects it has tried to compile in the UTL_RECOMP_COMPILED table. If you monitor that table during the run, the number of objects should increase with time. A count that stops moving is a sign to look at the session, not to start a second run.

Do not forget SYS and SYSTEM

For an upgrade, Oracle says to make sure that invalid objects in the SYS and SYSTEM schemas are updated. When you read the before list, look at these two owners first.

Keep single-object recompiles for small jobs

When only one or two objects are invalid, an ALTER ... COMPILE statement is enough. For a package, add BODY to compile the body. For many objects, a manual loop is slower to write and easier to get wrong than one call to UTL_RECOMP.

Treat what stays invalid as a different problem

A recompile can only succeed if the code is now correct. If an object is still invalid after the run, the usual cause is an error in the object or in something it references. The recompile cannot fix that. Read the error text for the object, repair the cause, and compile it again.

When to Run This

  • After a database upgrade, as part of the test plan and again on the real upgrade.
  • After a patch that changes tables, views or PL/SQL units.
  • After a release script that alters table columns used by views or stored code.
  • After an object is renamed, because dependent views and PL/SQL program units become invalid and must be recompiled before the next use.
  • After you revoke object privileges, because dependent objects are cascade invalidated.
  • Before a go-live window, when you want no first-call delay for users.
  • When the invalid count from DBA_OBJECTS is high and growing.

Troubleshooting Common Issues

The script fails because of your account. Oracle says to run it as the SYS user, or as another user that is granted the SYSDBA system privilege. Connect as one of those and rerun it.

The run takes much longer than expected. Check the count of invalid objects first. Run time follows that count. If the database is also busy with user work, a high thread count can slow both. Lower the thread count with RECOMP_PARALLEL(n) and run again.

Objects are still invalid after the run. Query DBA_OBJECTS again and keep the list. Take one object from it and compile it by hand with the matching ALTER ... COMPILE statement. The compile output will show the real error. The linked post on SHOW ERRORS and DBA_ERRORS explains how to read it.

A dependent object stays invalid because of a missing reference. An object can depend on the absence of another object. If that object is created later, the dependent object is invalidated. Check which object the name now resolves to before you recompile.

Application sessions raise ORA-04061, ORA-04064, ORA-04065 or ORA-04068 after a patch. These errors mean an object in use was invalidated. Restart the application sessions.

You ran a schema-only recompile. The schema argument limits the run to invalid objects in that schema. Run the DBA_OBJECTS query again afterward and check the whole database, not only that schema.

References

Posts in this series