Wednesday, June 9, 2010

Identify Oracle Long Running sessions --Moid

My notes on long running sessions


1) The following query will identify any session which are running for more than 10 seconds.


select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_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> 10 --time in seconds
order by
sid,
serial#,
q.piece;




2) The following query will identify any session which are running for more than 10 seconds and the SQL statement to kill that session.

select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_running,
q.sql_text,
'Alter system kill session '''||s.sid||','||serial#||''' immediate;' as SQL_to_kill_long_running_sqls
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> 10 --time in seconds
order by
sid,
serial#,
q.piece;



More to come..





Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383
http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries

No comments:

Post a Comment

Followers