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;
Next: Analyze and modify trigger state through views >>
More Oracle Articles
More By O'Reilly Media
|
 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.
|
|
