Monday, January 10, 2011
The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046.
Run following sqls in the order
@sess_waits <-- shows sid on clock
@sql_text <-- show the sql for sid
@sqlplan <-- show sqlplan for sql_id
@wait_sess <-- list all the waits in db
@wait <-- waits for sid
-- sess_waits.sql
set pages 1000 lines 132
column username format a20
column USER_PROGRAM format a40
column osuser format a10
select sid,serial#,username, NVL(program,machine) USER_PROGRAM,last_call_et from v$session
where sid in (select sid from v$session_wait where sid not in
(select sid from v$session where username in ('SYS')
or username is null) and event not in ('SQL*Net message from client','null event')
and event != 'SQL*Net message to client') order by upper(USER_PROGRAM) desc;
Get sql address for the sid list ( from above)
-- get sql addresses
SELECT sid, state, event, seconds_in_wait siw,sql_id, sql_address,
sql_hash_value hash_value, p1, p2, p3
from v$session where sid=&1;
get sql plan for the sql using sql address ( from above )
-- sqlplan.sql
set lines 132
COLUMN access_plan format a45
SELECT UNIQUE ( id ),
parent_id,
LPAD(' ' , 2 * parent_id + 1 ) || operation || ' ' ||
DECODE( object_name, NULL, '', ' <<' ) || LOWER( object_name ) ||
DECODE( object_name, NULL, '', '>>' ) ACCESS_PLAN,
cost,
cardinality,
options
FROM v$sql_plan
WHERE address=UPPER('&address');
Get all the waits in the databases
-- wait_sess.sql
select * from v$session_wait where sid not in
(select sid from v$session where username in ('SYS')
or username is null) and event != 'SQL*Net message from client'
and event not in ('SQL*Net message to client','null event');
get the wait event for sid
-- wait.sql
select * from v$session_wait where sid = &1;
Hope this helps! Rupam
