Scripts to check Long Running Sessions

Scripts to check Long Running Sessions

This one shows SQL that is currently "ACTIVE" :-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text

from v$sqltext_with_newlines t,V$SESSION s

where t.address =s.sql_address

and t.hash_value = s.sql_hash_value

and s.status = 'ACTIVE'

and s.username <> 'SYSTEM'

order by s.sid,t.piece

/

This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:

select

 object_name,

 object_type,

 session_id,

 type,            -- Type or system/user lock

 lmode,           -- lock mode in which session holds lock

 request,

 block,

 ctime            -- Time since current mode was granted

from

 v$locked_object, all_objects, v$lock

where

 v$locked_object.object_id = all_objects.object_id AND

 v$lock.id1 = all_objects.object_id AND

 v$lock.sid = v$locked_object.session_id

order by

 session_id, ctime desc, object_name

/

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent

FROM v$session_longops

WHERE sofar/totalwork < 1

/

Try this, it will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s

join v$sqltext_with_newlines q

on s.sql_address = q.address

where status='ACTIVE'

and type <>'BACKGROUND'

and last_call_et> 60

order by sid,serial#,q.piece;

 

Thanks

Srini