Troubleshooting PL/SQL Code

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:

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

PARSED_SIZE

   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 
   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:

PLSQL_OPTIMIZE_LEVEL
   Optimization level that was used to compile the
   object

PLSQL_CODE_TYPE
   Compilation mode for the object

PLSQL_DEBUG
   Indicates whether or not the object was compiled for
   debugging

PLSQL_WARNINGS
   Compiler warning settings that were used to compile
   the object

NLS_LENGTH_SEMANTICS
   NLS length semantics that were used to compile the
   object

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 associated 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 */
  SELECT   AUTHID
         , p.object_name program_name
         , procedure_name subprogram_name
     FROM user_procedures p, user_objectso
    WHERE p.object_name = o.object_name
      AND p.object_name LIKE '<package or program name criteria>'
  ORDER BY AUTHID, procedure_name;