 |
|
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
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- 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#,
b.SID, b.SERIAL#, b.USERNAME, b.OSUSER
from v$active_session_history a, v$session b
where:
See Code Depot
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |