ss

Oracle Tips by Burleson

How to Use ASH

The primary reason for using the current ASH tables like v$active_session_history and v$session is troubleshooting a problem that is currently going on. In this case, the use of the term history in v$active_session_history may be misleading since the history is time-limited. This time limitation is directly related to the amount of data stored there. If the database is active; the time that data is kept in the history will be shorter, and if the database is idle, the data will be kept longer.

v$session_wait can be queried to get information on which sessions are currently waiting. The information available includes:

SID
EVENT
P1TEXT
P1
P2TEXT
P2
P3TEXT
P3
WAIT_TIME
SECONDS_IN_WAIT
STATE

If v$active_session_history is queried instead, the TEXT information for the P1, P2 and P3 fields is lost, but CURRENT_OBJ#, CURRENT_FILE# and CURRENT_BLOCK# are gained. These three new fields allow the querying of v$session using row_wait_obj#, row_wait_file# and row_wait_block#. Using a query like session_ash.sql, which joins v$session and v$active_session_history, the v$session information can be reviewed for sessions that are in the ASH views.

Au: Below is not an ASH script.

* session_ash.sql

-- *************************************************

-- This script is free for non-commercial purposes

-- with no warranties. Use at your own risk.

-- To license this script for a commercial purpose,

-- contact rtp AT rampant.cc

-- *************************************************

/* session_ash.sql */

/* This reports information on object being waited on */

/* when the wait is I/O related */

select a.CURRENT_OBJ#, a.CURRENT_FILE#, a.CURRENT_BLOCK#,