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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

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