Module Validation and Dependency Management

Whenever you create or replace a stored object in the database, the PL/SQL engine compiles the code. If the compile succeeds, then the following information is stored in the database:

• The source code for the module, as you wrote it.
• The tree structure of the module, a hierarchical representation of the original source code, with a list of local and external references. The tree structure is used only by the compilation (and automatic recompilation) process.
• The pcode for the module, which is a list of instructions to the PL/SQL execution engine. The PL/SQL execution engine uses the pcode to execute the module when it is called by a host program.

When a module compiles, its status is set to VALID. This status is maintained in the SYS.OBJ$ table. Upon compilation, the PL/SQL engine also has resolved all references to other database objects such as tables and other stored programs. Each of these references constitutes a dependency for that module. In other words, the validity of the module is dependent upon the validity of all objects on which it depends. All dependency information is stored in the SYS.DEPENDENCY$ table.

NOTE: The tree structure, pcode, and dependency information is maintained only for named modules. Anonymous blocks and database triggers are compiled only when (and each time that) they are executed. The generated pcode for these objects is stored directly in the shared pool of the database instance for as long as they are used, and until they are erased from the System Global Area, using a least-recently-used algorithm.

Starting with Oracle Server Release 7.3, triggers are compiled and their pcode stored in the database.

Interdependencies of Stored Objects

A stored object must be VALID in order for its pcode to be loaded into the shared pool and executed by the host program. As noted above, if the compile succeeds at create/replace time, then the status is set to VALID. This status may, however, depend on other objects. Consider the following function:

FUNCTION full_name (employee_id_in IN NUMBER) RETURN VARCHAR2 IS first_and_last VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO first_and_last FROM employee WHERE employee_id = employee_id_in;
return first_and_last; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END;

Suppose that on Monday I save this function to the database. It compiles successfully and its status is set to VALID. The PL/SQL compiler also adds a record in the DEPENDENCY$ table to indicate that full_name is dependent on the employee table. Then on Tuesday, the DBA team adds another column to the employee table. The Oracle Server automatically checks the dependencies for the employee table and sets the status of all dependent objects to INVALID. This is a recursive process. Once full_name is set to INVALID, then any modules calling full_name are also set to INVALID.[1]

[1] An important exception to this "chain reaction" occurs with packaged modules. If, for example, full_name was defined within a package called, say, "employee", then even if the status of the full_name module is set to INVALID, no modules that call full_name will be tagged invalid, unless the specification of full_name changed (which it does not in this case). See the package examples and documentation on the disk for more information about this extra protection provided by packages.

The next time a user runs the full_name function, the database notices that the status is INVALID. It then calls the PL/SQL engine to compile the function. If the compile succeeds, then the pcode is loaded to shared memory and the function runs.

Automatic Versus Manual Compilation

It is easy to see that the database does a lot of work for you by maintaining the stored object dependencies and automatically recompiling objects as needed. You may not want to always depend on such recompilation, however. There are two potential drawbacks to automatic recompilation:

• The recompilation takes place at run time, when the user has requested an action. This means that the user waits while PL/SQL does its thing. The pause should not be very noticeable for small objects. Large packages can, however, take more than several seconds to compile. Do you want your users to wait while this happens?
• The recompilation can fail. What if the reason that the full_name function is INVALID is that the employee table was dropped? The database cannot recover from such an error, as far as full_name is concerned.

A much more sensible approach to take with stored object recompilation is to manually recompile all INVALID objects before the user tries to execute those objects. This way the compilation time is moved off-line and if any objects fail to compile, you can analyze and resolve the problem. To do this manual recompilation you need to coordinate closely with the DBA group (if it is separate from the application development team). You can determine which modules are INVALID by examining the contents of the USER_OBJECTS or ALL_OBJECTS views. You could even generate the commands necessary to recompile all INVALID PL/SQL objects with the following query:

SELECT 'ALTER ' || object_type || ' ' || object_name || ' COMPILE;' FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') AND status = 'INVALID';

Output from this query might look like:

ALTER PACKAGE PSGLOBAL COMPILE; ALTER FUNCTION FULL_NAME COMPILE;

You could also force recompilation of a module by using the ALTER_COMPILE procedure in the DBMS_DDL package (see Appendix A, What's on the Companion Disk?, for more details), as follows:

DBMS_DDL.ALTER_COMPILE ('package', 'SCOTT', 'FULL_NAME');

It is easy, when you use stored objects, to take for granted automatic management of these objects by the database. You might scarcely notice the status of your objects and the recompilations that take place behind the scenes.

That's cool. That's the whole idea. As you move your applications into production, however, you would be well served to remember how the database works with your stored objects. That way, when something goes wrong (very slow execution time under certain circumstances, for example), you have some idea of how to fix it.