 |
|
ss
Oracle Tips by Burleson |
Digging in to cause of
slowness
ash_top_10.sql is a query of
v$active_session_history joined with dba_users to get username and
v$sql to obtain the SQL statement. Figure 9.5 shows some of the
records from a sample system.
* ash_top_10.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
--
*************************************************
/* ash_top_10.sql */
column sql_text format a120 WRAP
column session_id form 9999 head SID
column session_serial# form 9999 head
SERIAL#
column sql_text format a120 WRAP
column sample_time format a10
column username format a10
column session_state format a10 head
SESSION|STATE
column event format a30
column p1 format 999999999
column p2 format 9999999
column p3 format 9999999
select
trunc(SAMPLE_TIME),
SESSION_ID,
SESSION_SERIAL#,
USERNAME,
EVENT,
P1,
P2,
P3,
WAIT_TIME,
TIME_WAITED,
SESSION_STATE,
SQL_TEXT
from
v$active_session_history a,
dba_users b,
v$sql c
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 |
