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.
These queries are filtering the sessions based on logon times less than 4 hours and the last call occurring within 30 minutes. This is to find more currently relevant sessions instead of long running ones that accumulate a lot of time but aren't having a performance problem. You may need to adjust these values to suit your environment.
Get sessions with Highest CPU consumption
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
Get Current sql statement executed by sid
SELECT s.sid, s.status, sq.sql_text
FROM v$session s, v$sql sq
WHERE s.sid = &ssid
and s.status = 'ACTIVE'
and s.sql_hash_value = sq.hash_value
and s.sql_address = sq.address;
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
