Tracing Sessions in Oracle Using the DBMS_SUPPORT Package (文档 ID 62160.1)

原创 2013年12月02日 09:56:11


This is part of a set of articles which give an introduction to tuning Oracle. This particular article gives information about tracing user sessions using the DBMS_SUPPORT package. 

NOTE This package should only be installed when requested by Oracle Support. It is not documented in the server documentation. As such the package functionality may change without notice. It is to be used only as directed by Oracle Support and its use otherwise is not supported.

Session tracing can be useful in any of the following circumstances:

  • One or more sessions seem to be using a disproportionate amount of some resource (from V$SESSTAT, V$SESSION_EVENT or V$SQLAREA)
  • To determine how time is divided for typical users of an online system.
  • To isolate more information about particular waits that are occurring.

It is important that you have read Note:61998.1 before continuing with this article.


Please note the following limitations of tracing user sessions:
  • The output from sessions using MTS, XA or Oracle8 OCI may be spread across several trace files making it very difficult to interpret. If possible run the session to be traced using a DEDICATED connection.
  • The DBMS_SUPPORT package can only be used with Oracle 7.2 onwards. It is assumed this is installed and execute permission has been granted to any user wishing to use the package. See Note:62294.1 for details of the DBMS_SUPPORT package.
  • Output trace files are limited to MAX_DUMP_FILE_SIZE blocks of output. Typically a block is 0.5K .
  • There is no warning if the trace file is truncated.
  • Parameter:TIMED_STATISTICS should be set to TRUE otherwise the output will be of limited use.
  • As tracing has to write the trace output to disk there may be a small impact on the sessions performance. This is not usually noticeable.
  • The output is rather cryptic - Although TKPROF can be used to summarise the SQL executed there is no standard tool to interpret the WAIT or BIND information which may be written to the raw trace file. There are some support tools which can help with this which are described later
  • It is not advisable to trace background processes unless explicitly asked to do so by Oracle support.
  • On some platforms (notably UNIX) if you delete a sessions trace file it does NOT get recreated when you stop/start tracing.

Identifying sessions to Trace

It is expected that the session/s to be traced have already been identified using information elsewhere in this set of articles. Below is a reminder of some of the forms of statement which may be used when isolating a session to be traced:

Finding user/s of a particular SQL statement given the "ADDRESS" and "HASH_VALUE" from V$SQLAREA:

  SELECT s.sid , s.serial#, s.status, s.server, s.username
    FROM v$session s
   WHERE s.sql_address='&ADDRESS'
     AND s.sql_hash_value='&HASH_VALUE'

Finding the SID/SERIAL# of a user showing a particular type of wait:

  SELECT s.sid , s.serial#, s.status, s.server, s.username, 
	e.event, e.time_waited
    FROM v$session_event e, v$session s
   WHERE e.sid=s.sid
     AND e.event like '&WAITEVENT_TO_CHECK'
     AND e.time_waited > '&WAIT_TIME_THRESHOLD'
  ( or use e.total_waits > '&NUM_WAITS_THRESHOLD')

  Eg: Replace &WAITEVENT_TO_CHECK with "buffer busy waits" to show
      sessions exhibiting lots of buffer busy waits.

Finding the SID/SERIAL# of a user showing a particular count for a given statistic:

  SELECT s.sid , s.serial#, s.status, s.server, s.username,, t.value
    FROM v$statname n, v$sesstat t, v$session s
   WHERE t.sid=s.sid
     AND t.statistic#=n.statistic#
     AND t.value > '&VALUE_THRESHOLD'
  Eg: Replace &STATISTIC_TO_CHECK with "sorts (disk)" to show sessions 
      exhibiting lots of disk sorts.

Tracing your own Session

To start tracing your own session simply execute the PL/SQL call:

Tracing another Session

Once a session has been isolated then you need to know the SID of the target session (and possibly the SERIAL#). To start tracing the target session simply call:

If it is important to capture an exact session you should specify both the SID and SERIAL# . Eg:


This PL/SQL procedure will ask the target session to start writing trace output. The trace may NOT start immediately as there are certain points in the Oracle code where a session checks to see if it has been asked to do something (like writing trace output) - the target session only starts tracing once it has seen the request.

Stopping Trace

To stop tracing in your own session call:

To stop tracing another session call:


Trace Output File Format

Output files contain RAW trace output and can be found in the USER_DUMP_DEST directory for user sessions and BACKGROUND_DUMP_DEST for background processes. A summary of the RAW format can be found in Note:39817.1 . In this section we mention some of the important points about the trace file along with some tools which can be used to interpret the output for you.

Raw Trace File

The raw trace file is described in Note:39817.1. Important points to note are:
  1. Most lines refer to a CURSOR number. Eg: PARSING IN CURSOR #3
  2. EXECutes, FETCHes and WAITs are recorded against a cursor. The information applies to the most recently parsed statement within that cursor. 
    Eg: FETCH #3 indicates a fetch from the most recently parsed and executed statement in cursor 3.
  3. All timing information in the trace file is in 1/100ths of a second.
  4. A time of ZERO in the trace file indicates LESS THAN 1/100th of a second.
  5. The information in the WAIT lines is the same as is found in V$EVENT_NAME (or V$SESSION_WAIT). 

    Eg: WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25 

    implies a wait while using cursor #1 for "db file scattered read" V$EVENT_NAME shows P1 is "file#", P2 is "block#" and P3 is "blocks" so in this case this indicates a wait for a read to complete from file# 4, block#1435 for 25 blocks. The WAIT took 5/100 seconds or 50mS to complete.
  6. STAT lines indicate that the cursor has been closed. From Oracle 7.3.3 onwards the STAT lines give the ACTUAL execution plan for the statement.
There are many ways to process a raw trace file but most require text processing filters such as GREP, AWK, NAWK, SED or PERL to extract the required information. As an example consider that several sessions have been showing waits for 'buffer busy waits'. By tracing a session the actual waits are written to the trace file. These could then be processed to list the file# and block# being waited for.
  Eg: In Unix one could process the waits using a command like: 

	grep 'buffer busy waits' trace_file | \
	 awk ' { print substr($0,9); } ' | \
	 sort > sorted_buffer_wait_list
The actual waits may then be mapped back to an object using SQL like:
  SELECT segment_owner, segment_name
    FROM dba_extents 
   WHERE file_id=&FILE_ID 
     AND &BLOCK_ID between block_id and block_id+blocks-1


There are numerous articles which describe the TKPROF some of which are listed below. If there are large gaps between the CPU and Elapsed time columns then the WAIT information in the raw trace file shows what resources were being waited on which make up the difference. TKPROF does not report any details of the WAIT information so is most useful for identifying statements which are expensive in terms of both:
  1. CPU intensive statements
  2. Statements with long elapsed times

Articles describing TKPROF include:


Note:377204.1 How to Install the DBMS_SUPPORT Package 




The DBMS_SUPPORT Package (文档 ID 62294.1)

The DBMS_SUPPORT Package ~~~~~~~~~~~~~~~~~~~~~~~~ This package is an add on to Oracle releases 7.2...
  • mybluetiankong
  • mybluetiankong
  • 2014年01月03日 10:27
  • 366

[Oracle] How to Use DBMS_SUPPORT Package

 整理:Fenng 日期:24-Oct-2004 出处:http://www.dbanotes.net版本:0.9DBMS_SUPPORT是Oracle提供的一个软件包。供内部支持人员使用以更有效地跟...
  • Fenng
  • Fenng
  • 2004年10月26日 18:24
  • 2006

Troubleshooting Failed Requests Using Tracing in IIS 7

Introduction Request-based tracing provides a way to determine what exactly is happening with you...
  • ddxkjddx
  • ddxkjddx
  • 2012年11月14日 11:52
  • 719

Tracing datapump sessions

Tracing datapump is not straight forward, you can trace the datapump worker(dw00) or datapump mast...
  • jlhnxly
  • jlhnxly
  • 2014年09月25日 13:28
  • 827

[Script]Re-Compile All Invalid Package/Package Body

How to Re-Compile All Invalid for Oracle EBS Package/Package Body set serveroutput on size 1000...
  • pan_tian
  • pan_tian
  • 2012年06月27日 22:29
  • 2908

Oracle 修改sessions和processes

先备份spfile 1.通过SQLPlus修改 Oracle的sessions和processes的关系是 sessions=1.1*processes + 5 使用sys,以sysdba权限...
  • lotusyangjun
  • lotusyangjun
  • 2012年09月13日 21:16
  • 2490

Oracle11gr2 sessions,processes 和 transactions 参数 关系 说明

Oracle sessions,processes 和 transactions 参数 关系 说明
  • orion61
  • orion61
  • 2011年11月18日 18:47
  • 724

Tracing the LSNRCTL Control Utility (文档 ID 737530.1)

适用于: Oracle Net Services - Version: to11.2.0.3 - Release: 10.1 to 11.2 Information in t...
  • qq_21127313
  • qq_21127313
  • 2017年01月24日 10:42
  • 83

Oracle sessions,processes 和 transactions 参数 关系 说明

一.官网说明1.1 processes11gR2 的文档:  Property Description Parameter type Integer Default v...
  • tianlesoftware
  • tianlesoftware
  • 2011年10月21日 12:54
  • 8861

MOS 文档 ID 1212703.

为没有MOS的网友提供方便 Grid Infrastructure Startup During Patching, Install or Upgrade May Fail ...
  • u010587433
  • u010587433
  • 2014年12月08日 10:29
  • 936
您举报文章:Tracing Sessions in Oracle Using the DBMS_SUPPORT Package (文档 ID 62160.1)