In this Document
Goal
Solution
Introduction
Gathering Errorstacks
Gathering Errorstacks on Your Current session
Command Explanation
APPLIES TO:
Oracle Server - Enterprise Edition - Version: 8.0.3.0 to 11.2.0.3 - Release: 8.0.3 to 11.2Information in this document applies to any platform.
GOAL
This article outlines how to collect Errorstack trace for use in diagnosing performance issues.Errorstack is an internal event and should only be set on the advice of Oracle Support.
SOLUTION
Introduction
The commands shown below provide the most useful set of commands to capture session related performance data for various problems under the guidance of Oracle Support.Gathering Errorstacks
To dump trace and errorstacks, either the Operating system process id or Oracle process id for a slave process must be determined.Assuming the Oracle SID for the process is known then the following select can be
used to find the Operating system process id :
SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;
SPID is the operating system identifier
SID is the Oracle session identifier
PID is the Oracle process identifier
and then do the following:
Lets assume that the slave process id to be dumped has an o/s pid of 9834
and an Oracle pid of 34
login to SQL*Plus:
connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
To us the Oracle Pid instead, replace the :
oradebug setospid 9834
with
oradebug setorapid 34
Remember to change the PIDs to the actual values on your system!
The final line outputs the trace file name which will include the string 'STACK_10046' for easy identification.
To disable the tracing once tracing is finished:
oradebug event 10046 trace name context off
This will produce a trace file in the relevant trace destination matching the process traced.
Gathering Errorstacks on Your Current session
If you are trying to gather stacks on your current session, you can determine the PID values by running selects such as the following in that session:
SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.audsid = userenv('SESSIONID') ;
or
SELECT p.pid, p.SPID,s.SID
FROM v$process p,v$session s
WHERE s.paddr = p.addr
AND s.SID =
(SELECT DISTINCT SID
FROM V$MYSTAT);
Command Explanation
The following is a brief outline of the purpose of the commands:- connect / as sysdba
This provides the simplest way of gathering the required access to dump the information.
- ALTER SESSION SET tracefile_identifier = 'STACK_10046';
Adds a search-able string to the trace file for easy identification later.
- oradebug setospid XXXX
Connects the oradebug tool to a particular O/S (operating system) PID (Process ID)
- oradebug setorapid XXXX
Connects the oradebug tool to a particular oracle PID (Process ID)
- oradebug unlimit
Unrestricts the trace file size so as not to lose valuable diagnostics.
- oradebug event 10046 trace name context forever,level 12
Collects 10046 trace showing waits and binds. This trace is ofter useful when collected in conjunction with errorstacks and providing context. This is optional.
- oradebug dump errorstack 3
Dumps the process stack and process state for the current process.
- oradebug tracefile_name
Writes the identifier to the trace file.