SRDC - Providing Supporting Information for PL/SQL Runtime Issues (Doc ID 2288591.1)

 Applies To

Product:

• PL/SQL - All Versions
• Information in this document applies to any platform

Privileges Required:

• End User - only basic access to the application/user interface is needed in order to perform this collection.

System Impact:

• None - following the collection instructions given will have no impact on the system.

What is being collected and why?

The process outlined below will guide the user through collecting diagnostic data, which will help Support narrow down the cause of your problem.

This SRDC specifically collects the following information, where applicable, for failing PL/SQL applications, other than those using UTL_MAIL, UTL_SMTP, UTL_HTTP and external procedures, and when there is no testcase available:

• Output and errors.
• Code snippets and object definitions.
• PL/SQL compilation parameters.
• NLS settings and database parameters.
• Version and patching information.
• Database trace files and the alert log.

The table below will help you determine which action plan items relate to your problem type.  Items in italics may not apply in all cases.

Problem Type	Applicable Action Plan Items
UTL_FILE issue	1a or 1b, 1c, 1d, 1e, 1f, 1g, 1j, 2b, 2e, 2g
Hanging or looping application	1a, 1c, 1d, 1e, 1f, 1j, 2b, 2d, 2g
Performance issue	1a, 1c, 1d, 1e, 1f, 1h, 1j, 2b, 2g
Memory leak or growth	1a, 1c, 1d, 1e, 1f, 1j, 2b, 2f, 2g
Native compilation specific issue	1a or 1b, 1c, 1d, 1e, 1f, 1i, 1j, 2b, 2g
ORA-4068, ORA-4065, ORA-6508 or PLS-907	1b, 1c, 1d, 1e, 1f, 1j, 2b, 2c, 2g
ORA-3113, ORA-7445 or ORA-600	1c, 1d, 1e, 1f, 1j, 2a, 2b, 2g
Any other error	1b, 1c, 1d, 1e, 1f, 1j, 2b, 2e, 2g
Other	1a, 1c, 1d, 1e, 1f, 1j, 2b, 2e, 2g

Safe Harbor Statement: Oracle will use this information to help diagnose the cause of the issue. In many cases this set of information will be sufficient for diagnosis - in some cases additional specific diagnostic data may be required at a later stage.

Action Plan

1. Create a file called plsql_runtime_info.txt containing the following information:

a) If the PL/SQL application is producing incorrect behaviour or unexpected results, details of what is happening and what you are expecting to happen.  Provide any snippets of the code you think may help Oracle Support to understand your issue, along with the definitions of any variables used there.  Where the failing program unit is small, alternatively include the entire source code as part of plsql_runtime_files.zip or plsql_runtime_files.tar.  See 2e) for a script to select stored PL/SQL code from the database.

b) If the PL/SQL application is failing with an error, the full error stack.  Provide a snippet of the code from each of the lines referenced in the error stack, along with the definitions of any variables used there.  Where the failing program unit is small, alternatively include the entire source code as part of plsql_runtime_files.zip or plsql_runtime_files.tar.  See 2e) for a script to select stored PL/SQL code from the database.

c) The SQL for, or a describe from SQL*Plus of, the objects referenced in any provided code.

d) If you think any of the settings may be relevant to your issue, the PL/SQL compilation parameters in use for the problem program unit(s).  If it is an anonymous block, you will first have to create a dummy procedure whose parameters you can query.  From SQL*Plus, as the owner of the program unit in question, issue the following, entering the PL/SQL object name as appropriate:

COLUMN level                FORMAT 99
COLUMN plsql_code_type      FORMAT A15
COLUMN plsql_debug          FORMAT A11
COLUMN nls_length_semantics FORMAT A20

SELECT type, plsql_optimize_level "LEVEL", plsql_code_type, plsql_debug,
       nls_length_semantics, plsql_warnings, plsql_ccflags, plscope_settings
FROM   user_plsql_object_settings
WHERE  name = '&object_name';

If your issue potentially relates to code being optimised away, also state whether it is affected by recompiling with the value of PLSQL_OPTIMIZE_LEVEL set to a lower level.

e) Any applicable database parameters and NLS settings, in particular the database characterset, NLS_CHARACTERSET, but also consider NLS_NCHAR_CHARACTERSET, NLS_LENGTH_SEMANTICS, NLS_DATE_FORMAT etc.  The server side database and national charactersets can be found by issuing the following from SQL*Plus as any user:

COLUMN parameter FORMAT A30
COLUMN value     FORMAT A30

SELECT parameter, value FROM nls_database_parameters
WHERE  parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

f) The value of the client side NLS_LANG environment variable, if set.

g) If your issue relates to the UTL_FILE package:

i) The type of connection you are making to the database - bequeath, dedicated or shared server.

ii) The value of the NLS_LANG environment variable when the database was started.

iii) If you are connecting to the database via the listener, the value of the NLS_LANG environment variable when the listener was started and which user started it.

iv) The value of any ORA_NLS* environment variable, typically ORA_NLS10, that was set when the database and listener were started.  If making a local, i.e. bequeath connection, then any ORA_NLS* client side environment variable.

For ii), iii) and iv), if your database is running on a UNIX platform, OS tools are available for looking at environment variables in use by a running process.  Find the process id, pid, of the database process for your session then use the appropriate tool for your platform to print its environment variables:

Platform	Command	Comments
Linux	strings /proc/<pid>/environ | grep NLS
Solaris	pargs -e <pid> | grep NLS
HP	Use a debugger, e.g.:   gdb $ORACLE_HOME/bin/oracle <pid>   (gdb) p ((char**)_environ)[0]@50	This will print the first 50 entries of an array containing the environment variables.  If there are more than 50, increase the number as appropriate.
AIX	ps ewww <pid> | tr ' ' '\n' | grep NLS

 v) If the problem is related to file or directory permissions and the database is running on UNIX, the results of executing the following OS commands:

ls -ld <name of directory UTL_FILE is trying to access>
ls -l $ORACLE_HOME/bin/oracle
id <Oracle user>
id <user who started the listener, if the connection is via the listener>
id <user running the application, if the connection is local>

vi) If the problem is related to accessing a network directory and the database is running on Windows, as which user the Oracle Service running and whether that user has access to the directory.

If the application is failing to open, read from or write to a file but you do not know why and the database is running on UNIX, attach an appropriate OS tracing tool to the database process for your session and reproduce the issue.  On Linux platforms you can use strace, on Solaris and AIX truss and on HP tusc.  Each command is run as follows:

<OS trace tool name> -o <output file name> -p <OS pid of database process>

If using Linux and strace you can additionally pass the "-v", verbose, option so that lstat commands will show the file properties including user and group.  Call the output file utl_file_trace.log and include it as part of plsql_runtime_files.zip or plsql_runtime_files.tar.

h) If your application is experiencing performance problems, details of what it is doing when running slowly, e.g. executing a query, manipulating multi-level collections etc.  Where applicable, provide timings and details of when performance is poor and when it is acceptable. If you are unsure at which points the application is running slowly, consider using the profiling packages, DBMS_PROFILER or DBMS_HPROF, to provide detailed timings.  For further information on how to use these packages see the relevant sections of the Oracle Database PL/SQL Packages and Types Reference manual and:

Note:243755.1 Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data
Note:763944.1 How to Tune PL/SQL Applications and Identify Hot Spots using DBMS_HPROF

If performance apparently drops significantly at a particular point in the application but you do not know exactly what it is doing at the time, you can use the ORADEBUG command, run from SQL*Plus as a DBA, to generate an errorstack for the database shadow process to which the application is connected. An errorstack dump includes the current SQL statement and PL/SQL stack.  The result will be written to the trace file for the session in question.  From SQL*Plus, run:

ORADEBUG SETOSPID <OS pid of database process>
ORADEBUG UNLIMIT
ORADEBUG DUMP ERRORSTACK 3

Include any trace files, profile reports and other supporting files as part of plsql_runtime_files.zip or plsql_runtime_files.tar, referencing them here.

i) If your issue relates to the files generated by native compilation, the value of database parameter _ncomp_shared_objects_dir, if set, and whether the database is RAC or not.

j) The versions and platforms where the problem reproduces or does not reproduce if it works in some environments but not others.

2. Zip or tar the following files, where applicable, into an archive called plsql_runtime_files.zip or plsql_runtime_files.tar as appropriate:

a) If your application is failing with an ORA-3113, ORA-600 or ORA-7445 error, the database alert log plus the trace file(s) indicated there for your error at the time it occurred.  Alternatively package up the corresponding incident, e.g. via adrci (see the "ADRCI: ADR Command Interpreter" chapter in the Oracle Database Utilities manual for information about using adrci), and include that.  You may require assistance from your DBA to collect these files. See also the note at the end of 2b).

b) Any database trace files created around the time of the problem, e.g. for errorstack or SQL trace events when the application was executing.  If the application is failing with an error but you are unsure to which SQL statement it relates for instance, you can turn on an errorstack event for the error in question, either by issuing:

ALTER SESSION SET EVENTS = '<error no> TRACE NAME ERRORSTACK LEVEL 10';

before running the PL/SQL program unit, or by doing similar at database level via ALTER SYSTEM.  Remember, if an event is set at database level it will affect all database sessions.  You could also embed the ALTER SESSION command within the PL/SQL code by using dynamic SQL.  To enable SQL tracing within a session, use:

ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT FOREVER, LEVEL <n>';

where n is 4 if you want to see all SQL statements and their bind variables, and 12 if you additionally want to see waits.  In most cases level 4 should be sufficient.  This event can also be set at database level but that is rarely advisable.  If either type of event is set at database level, or you wish to disable an event after a certain point in the application, use a similar statement again but ending with either ERRORSTACK OFF or CONTEXT OFF as appropriate.

NOTE: If you are providing a database trace file generated for, or containing, a runtime error, it should include a PL/SQL call stack.  If so, provide a snippet of the code from each of the lines referenced in the call stack, along with the definitions of any variables and objects used there.  Include this information as part of plsql_runtime_info.txt.  Where the program units are small, alternatively include the entire source code as part of plsql_runtime_files.zip or plsql_runtime_files.tar.

c) If your problem is to do with runtime invalidations of previously valid PL/SQL objects, the files collected while working through note:2298084.1 - Troubleshooting PL/SQL Program Unit Invalidation Related Errors (e.g. ORA-4068, ORA-4065, ORA-6508, PLS-907).

d) If the application is apparently hanging or seems to be looping but you are unsure what it is doing at the time, use the ORADEBUG command, run from SQL*Plus as a DBA, to generate 3 or 4 errorstack dumps, taken some seconds or minutes apart, for the database shadow process to which the application is connected. An errorstack dump will show the current SQL statement, PL/SQL stack and C stack.  The result will be written to the trace file for the session in question.  From SQL*Plus, run:

ORADEBUG SETOSPID <OS pid of database process>
ORADEBUG UNLIMIT
ORADEBUG DUMP ERRORSTACK 3

repeating the third command as appropriate.

e) If your problem is similar to any of the following:

• • values in PL/SQL variables are apparently being swapped around (if this is happening only when binding data to SQL statements use SQL tracing as in 2b) instead)
  • incorrect or older copies of stored program units are being executed
  • wrong program units or lines of code are being executed

the problem is reproducible, there is a relatively small amount of code and the code is running in interpretted mode, a dump of the MCODE (machine specific execution code) for the problem application.  This will show what is actually being called, how variables are being assigned, manipulated and accessed, etc.  To do this, issue:

ALTER SESSION SET EVENTS = '10928 TRACE NAME CONTEXT FOREVER, LEVEL 1';

before running the PL/SQL program unit.  The output will be written to a database trace file for your session.  When providing trace files containing MCODE you must also provide the corresponding source code for the program units in question.  Make sure you have the correct copy of the source code.  If there is any doubt, select it from the database using a SQL*Plus script based on the following:

SET HEADING OFF
SET RECSEP OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON

SPOOL &output_file_name

SELECT   text
FROM     user_source
WHERE    name  = '&program_unit_name'
AND      type  = '&program_unit_type'
ORDER BY line;

SPOOL OFF
EXIT

Run the script as the owner of each program unit (alternatively, as a DBA, run a similar select from DBA_SOURCE, adding an "AND owner = '&owner'" to the statement).  The program unit name will usually be in uppercase and the program unit type will be one of PROCEDURE, FUNCTION, PACKAGE (meaning the specification), PACKAGE BODY, TYPE (meaning the specification), TYPE BODY or TRIGGER.

f) If your problem is a memory related issue such as running out of memory, unexpectedly high memory usage, memory continually growing, the database process for the session using an increasing amount of memory etc., generate a heapdump showing the 5 largest subheaps and their 5 largest subheaps.  This will allow Oracle Support to determine what is using the memory and whether it is expected based on the application logic.  While the problem application is running, use the ORADEBUG command, run from SQL*Plus as a DBA, to generate a heapdump for the database shadow process to which the application is connected. The result will be written to the trace file for the session in question.  From SQL*Plus, run:

ORADEBUG SETOSPID <OS pid of database process>
ORADEBUG UNLIMIT
ORADEBUG EVENT IMMEDIATE TRACE NAME HEAPDUMP LEVEL <n>

where n is 536870912 plus the level for the type of memory you want to interrogate - 1 for PGA, 2 for SGA and 4 for UGA. For PL/SQL issues it is usually the PGA and UGA memory heapdumps that are required so use  level 536870917.  If you know the problem relates to PGA memory specifically, use level 536870913.  Repeating the third command two or three times, a few seconds apart as applicable, should show which subheaps are growing as the application runs.

If you do not have DBA access and are able to issue an ALTER SESSION command after the memory has been allocated but before it has been freed, generate a heapdump from the problem session using:

ALTER SESSION SET EVENTS = 'IMMEDIATE TRACE NAME HEAPDUMP LEVEL <n>';

If the application is failing with a memory related error, you can ask the database to perform a heapdump as the error is generated.  Issue:

ALTER SESSION SET EVENTS = '<error no> TRACE NAME HEAPDUMP LEVEL <n>';

before running the PL/SQL program unit, or by doing similar at database level via ALTER SYSTEM.  Remember, if an event is set at database level it will affect all database sessions.

g) If you are unsure if the problem is caused by a patch you have installed, the database patch inventory, run from the command line for the database ORACLE_HOME via "opatch lsinventory".  Save the output to a file called opatch.log.

Upload the above files to the SR as applicable, namely:

• plsql_runtime_info.txt 
• plsql_runtime_files.zip / plsql_runtime_files.tar