Troubleshooting PL/SQL Code

In this second part of a nine-part series on managing PL/SQL code, you’ll learn how to analyze argument information, recompile invalid code, and more. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O’Reilly; ISBN: 0596009771). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Use program size to determine pinning requirements

The USER_OBJECT_SIZE view gives you the following information about the size of the programs stored in the database:

   Size of the source in bytes. This code must be in
   memory during compilation (including
   dynamic/automatic recompilation).


   Size of the parsed form of the object in bytes. This
   representation must be in memory when any object
   that references this object is compiled.

   Code size in bytes. This code must be in memory
   when the object is executed.

Here is a query that allows you to show code objects that are larger than a given size. You might want to run this query to identify the programs that you will want to pin into the database using DBMS_SHARED_POOL (see Chapter 23 for more information on this package) in order to minimize the swapping of code in the SGA:

  /* File on web: pssize.sql * /
  SELECT name, type, source_size, parsed_size, code_size
    FROM user_object_size
WHERE code_size > &&1 * 1024
ORDER BY code_size DESC

Obtain properties of stored code

The USER_PLSQL_OBJECT_SETTINGS (introduced in Oracle Database 10g Release 1) view provides information about the following compiler settings of a stored PL/SQL object:

Optimization level that was used to compile the

Compilation mode for the object

Indicates whether or not the object was compiled for

Compiler warning settings that were used to compile
   the object

NLS length semantics that were used to compile the

Possible uses for this view include:

  • Identify any programs that are not taking full advantage of the optimizing compiler (an optimization level of 1 or 0):

      /* File on web: low_optimization_level.sql * /
      SELECT owner, name
        FROM user_plsql_object_settings 
      WHERE plsql_optimize_level IN (1,0);
  • Determine if any stored programs have disabled compile-time warnings:

      /* File on web: disable_warnings.sql * /
      SELECT NAME, plsql_warnings
        FROM user_plsql_object_settings 
      WHERE plsql_warnings LIKE ‘%DISABLE%’;

The USER_PROCEDURES view lists all functions and procedures, along with asso ciated properties, including whether a function is pipelined, parallel enabled, or aggregate. USER_PROCEDURES will also show you the AUTHID setting for a program (DEFINER or CURRENT_USER). This can be very helpful if you need to see quickly which programs in a package or group of packages use invoker rights or definer rights. Here is an example of such a query:

  /* File on web: show_authid.sql */
, p.object_name program_name
, procedure_name subprogram_name
FROM user_procedures p, user_objects o
    WHERE p.object_name = o.object_name
      AND p.object_name LIKE ‘<package or program name criteria>’
  ORDER BY AUTHID, procedure_name;

{mospagebreak title=Analyze and modify trigger state through views}

Query the trigger-related views (USER_TRIGGERS, USER_TRIG_COLUMNS) to do any of the following:

  1. Enable or disable all triggers for a given table. Rather than have to write this code manually, you can execute the appropriate DDL statements from within a PL/SQL program. See the section “Maintaining Triggers” in Chapter 19 for an example of such a program.
  2. Identify triggers that execute only when certain columns are changed, but do not have a WHEN clause. A best practice for triggers is to include a WHEN clause to make sure that the specified columns actually have changed values (rather than simply writing the same value over itself).

Here is a query you can use to identify potentially problematic triggers lacking a WHEN clause:

  /* File on web: nowhen_trigger.sql */
    FROM user_triggers tr
   WHERE when_clause IS NULL AND
         EXISTS (SELECT ‘x’
                   FROM user_trigger_cols
WHERE trigger_owner = USER
AND trigger_name = tr.trigger_name);

Analyze argument information

A very useful view for programmers is USER_ARGUMENTS. It contains information about each of the arguments of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information about arguments and a bewildering structure that is very hard to work with.

Here is a simple SQL*Plus script to dump the contents of USER_ARGUMENTS for all the programs in the specified package:

  /* File on web: desctest.sql * /
  SELECT object_name, argument_name, overload
       , POSITION, SEQUENCE, data_level, data_type
    FROM user_arguments
   WHERE package_name = UPPER (‘&&1′);

A more elaborate PL/SQL-based program for displaying the contents of USER_ ARGUMENTS may be found in the show_all_arguments.sp file on the book’s web site.

You can also write more specific queries against USER_ARGUMENTS to identify possible quality issues with your code base. For example, Oracle recommends that you stay away from the LONG datatype and instead use LOBs. In addition, the fixed-length CHAR datatype can cause logic problems; you are much better off stick ing with VARCHAR2. Here is a query that uncovers the usage of these types in argument definitions:

  /* File on web: long_or_char.sql * /
  SELECT object_name, argument_name, overload
       , POSITION, SEQUENCE, data_level, data_type
   FROM user_arguments
  WHERE data_type IN (‘LONG’,'CHAR’);

You can even use USER_ARGUMENTS to deduce information about a package’s program units that is otherwise not easily obtainable. Suppose that I want to get a list of all the procedures and functions defined in a package specification. You will say: “No problem! Just query the USER_PROCEDURES view.” And that would be a fine answer, except that it turns out that USER_PROCEDURES doesn’t tell you whether a program is a function or procedure (in fact, it can be both, depending on how the program is overloaded!).

You might instead, want to turn to USER_ARGUMENTS. It does, indeed, contain that information, but it is far less than obvious. To determine whether a program is a function or a procedure, you must check to see if there is a row in USER_ARGUMENTS for that package-program combination that has a POSITION of 0. That is the value Oracle uses to store the RETURN “argument” of a function. If it is not present, then the program must be a procedure.

The following function uses this logic to return a string that indicates the program type (if it is overloaded with both types, the function returns “FUNCTION,
PROCE DURE”). Note that the list_to_string function used in the main body is provided in the file.

  /* File on web: program_type.sf * /
     owner_in     IN   VARCHAR2
   , package_in   IN   VARCHAR2
   , program_in   IN   VARCHAR2
     TYPE overload_aat IS TABLE OF all_arguments.overload%TYPE

     l_overloads  overload_aat;
     retval       VARCHAR2 (32767);

     BULK COLLECT INTO l_overloads
FROM all_arguments
WHERE owner = owner_in
          AND package_name = package_in
          AND object_name = program_in
     GROUP BY overload;

     IF l_overloads.COUNT > 0
        retval := list_to_string (l_overloads, ‘,’, distinct_in => TRUE);
     END IF;

     RETURN retval;
  END program_type;

Finally, you should also know that the built-in package, DBMS_DESCRIBE, pro vides a PL/SQL API to provide much of the same information as USER_ ARGUMENTS. There are differences, however, in the way these two elements handle datatypes.

{mospagebreak title=Recompiling Invalid Code}

Whenever a change is made to a database object, Oracle uses its dependency-related views (such as PUBLIC_DEPENDENCIES) to identify all objects that depend on the changed object. It then marks those dependent objects as INVALID, essentially throwing away any compiled code. This all happens automatically and is one of the clear advantages to compiling programs into the database. The code will then have to be recompiled before it can be executed.

Oracle will automatically attempt to recompile invalid programs as they are called. You can also manually recompile your invalid code, and this section shows how you can do this. Manual recompilation is generally recommended over automatic recompilation, particularly when it involves a production application. Recompilation can take quite a long time; on-demand compilation caused by a user request will generally result in a high level of user frustration.

Recompile individual program units

You can use the ALTER command to recompile a single program. Here are examples of using this DDL command:


You should include the REUSE SETTINGS clause so that other settings for this pro gram (such as compile-time warnings and optimization level) are not inadvertently set to the settings of the current session.

Of course, if you have many invalid objects, you will not want to type ALTER COMPILE commands for each one. You could write a simple query, like the one below, to generate all the ALTER commands:

  SELECT ‘ALTER ‘ || object_type || ‘ ‘ || object_nam e
   FROM user_objects
  WHERE status = ‘INVALID’;

The problem with this “bulk” approach is that as you recompile one invalid object, you may cause many others to be marked INVALID. You are much better off relying on Oracle’s own utilities to recompile entire schemas or to use a sophisticated, third-party script created by Solomon Yakobson. These are described in the next section.


Starting with Oracle Database 10g Release 1, the UTL_RECOMP built-in package offers two programs that you can use to recompile any invalid objects in your schema: RECOMP_SERIAL and RECOMP_PARALLEL.

To use UTL_RECOMP, you will need to connect as a SYSDBA account. When run ning the parallel version, it uses the DBMS_JOB package to queue up the recompile jobs. When this happens, all other jobs in the queue are temporarily disabled to avoid conflicts with the recompilation.

Here is an example of calling the serial version to recompile all invalid objects in the SCOTT schema:

  SQL> CALL utl_recomp.recomp_serial (‘SCOTT’);

If you have multiple processors, the parallel version may help you complete your recompilations more rapidly. As Oracle notes in its documentation of this package, however, compilation of stored programs results in updates to many catalog structures and is I/O intensive; the resulting speedup is likely to be a function of the speed of your disks.

Here is an example of requesting recompilation of all invalid objects in the SCOTT schema, using up to four simultaneous threads for the recompilation steps:

  SQL> CALL utl_recomp.recomp_parallel (‘SCOTT’, 4);

Oracle also offers the DBMS_UTILITY.RECOMPILE_SCHEMA program to recompile invalid objects. One advantage of using this program over the UTL_RECOMP alternatives is that you do not need to connect as a SYSDBA account. I recommend, however, that you avoid using DBMS_UTILITY.RECOMPILE_SCHEMA altogether; in some cases, it does not seem to successfully recompile all invalid objects. This may have to do with the order in which it performs the compilations.

If you do not want to have to connect to a SYSDBA account to perform your recompilations, you might consider using a recompile utility written by Solomon Yakobson and found in the recompile.sql file on the book’s web site.

{mospagebreak title=Using Native Compilation}

In versions before Oracle9i Database Release 1, compilation of PL/SQL source code always resulted in a representation, usually referred to as bytecode or mcode, that is stored in the database and interpreted at runtime by a virtual machine implemented within Oracle. Oracle9i Database introduced a new approach: PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. (Note, however, that an anonymous PL/SQL block is never compiled natively.)

When would this feature come in handy? How do you turn on native compilation? This section addresses these questions.

PL/SQL is often used as a thin wrapper for executing SQL statements, setting bind variables, and handling result sets. For these kinds of programs, the execution speed of the PL/SQL code is rarely an issue; it is the execution speed of the SQL that determines the performance. The efficiency of the context switch between the PL/SQL and the SQL operating environments might be a factor, but this is addressed very effectively by the FORALL and BULK COLLECT features introduced in Oracle8i Database and described in Chapter 14.

There are many other applications and programs, however, that rely on PL/SQL to perform computationally intensive tasks that are independent of the SQL engine. PL/SQL is, after all, a fully functional procedural language, and almost any real-world code is going to include a certain amount of “low-hanging fruit” that a modern compiler can chomp through, resulting in at least some increase in speed. You should realize, however, that the way that Oracle has chosen to implement the native compilation feature is not simply “translate your PL/SQL source into C and then compile it;” instead, Oracle always runs the normal PL/SQL compiler to generate mcode, and in native mode it takes this mcode itself as its input into the C translation process. This architecture has several consequences:

  1. Generating natively compiled code is by “definition” slower than generating conventional code.
  2. Any optimizations taken by the PL/SQL compiler will be applied regardless of compilation mode.
  3. The generated C code is going to be incomprehensible to anyone other than a few rocket scientists who work at Oracle Corporation (normally, the C source code is automatically deleted).

The tasks expressed in C are primarily housekeeping tasks: setting up and destroying temporary variables; managing stack frames for subprogram invocation; and making calls to Oracle’s appropriate internal routines. Speedup from using C will be greatest in programs that spend more time processing the mcode relative to the time spent in Oracle’s internal routines. To be sure, that’s difficult or impossible for customers to predict, but there are even more factors in the speedup equation, including:

  1. Oracle version in use. Later versions tend to exhibit more efficient runtime engines, which suppress the relative benefit of native compilation, although the total speedup will be greater.
  2. Setting of PLSQL_OPTIMIZE_LEVEL (Oracle Database 10g). If you are using aggressive PL/SQL optimization, the relative speedup from native compilation will be lower.
  3. Selection of datatypes. For example, a compute-intensive program that makes extensive use of the new IEEE floating-point types may also exhibit less relative speedup from native compilation.
  4. Behavior and optimizing capabilities of the C compiler you are using, plus effects that may vary based on your particular hardware.
  5. Degree of mixing native and interpreted code. Callouts between native and interpreted program units involve a context switch that homogeneous callouts can avoid.

Native compilation gives a broad range of speed increase, quoted by some sources as “up to 40%,” but even higher in certain unusual cases. Fortunately—and this is significant—I have never seen native compilation degrade runtime performance. That means the only thing you stand to lose with native compilation is speed of the compilation itself.

So how do you turn on this nifty feature? Read on…

Please check back next week for the continuation of this article.

Google+ Comments

Google+ Comments