Oracle
  Home arrow Oracle arrow Troubleshooting PL/SQL Code
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ORACLE

Troubleshooting PL/SQL Code
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 2
    2007-10-25

    Table of Contents:
  • Troubleshooting PL/SQL Code
  • Analyze and modify trigger state through views
  • Recompiling Invalid Code
  • Using Native Compilation

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Dell PowerEdge Servers

    Troubleshooting PL/SQL Code
    (Page 1 of 4 )

    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:

    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;

    More Oracle Articles
    More By O'Reilly Media


       · This article is an excerpt from the book "Oracle PL/SQL Programming, Fourth...
     

    Buy this book now. 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). Check it out today at your favorite bookstore. Buy this book now.

       

    ORACLE ARTICLES

    - Tuning PL/SQL Code
    - Debugging PL/SQL Code
    - Testing PL/SQL Code
    - Working With PL/SQL Code
    - Conditional Compilation for Oracle Database ...
    - Compile-Time Warnings for Oracle DB 10g
    - Compiling PL/SQL Code for an Oracle Database
    - Troubleshooting PL/SQL Code
    - Managing PL/SQL Code
    - Data Manipulation and More for HTML DB Appli...
    - Oracle Database Fundamentals
    - Adding Processes to HTML DB Applications
    - Adding Computations, Processes, and Validati...
    - Sub-templates and More with Oracle HTML DB
    - Focusing on Templates in Oracle HTML DB

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway