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).
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;