Home arrow Oracle arrow Troubleshooting PL/SQL Code

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.

  1. Troubleshooting PL/SQL Code
  2. Analyze and modify trigger state through views
  3. Recompiling Invalid Code
  4. Using Native Compilation
By: O'Reilly Media
Rating: starstarstarstarstar / 8
October 25, 2007

print this article



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 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 */
, 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;

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: