Thanks for the question, abhishek.
Asked: May 30, 2006 - 12:11 pm UTC
Last updated: May 31, 2006 - 3:00 pm UTC
Version: 9i
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
I want to fetch those Session which are running for last 30 mins having Status ACTIVE.
I am using below pasted Queries, i am too sure whether my quries will give the correct result or not.
Query-1
set timing on
set feedback on
set echo on
column opname format a15 wrap
SELECT * FROM (select
username,opname,sid,serial#,round(sofar/totalwork*100,2) "% Complete",a.sql_text
from v$session_longops s ,v$sqltext a
where
s.sql_hash_value= a.hash_value and
elapsed_seconds > 1800)
WHERE "% Complete" != 100
Query-2
select a.username,a.sid,a.status,a.last_call_et,b.sql_text
from v$session a, v$sqlarea b, v$process c
where a.sql_address=b.address and a.sql_hash_value=b.hash_value
and a.paddr=c.addr
and a.username is not null
and a.last_call_et>(30*60)
and a.status='ACTIVE';
Actually my requirement is to know the sessions which are connected to DB for more than 30 mins having STATUS as ACTIVE and after knowing the sid,and serial# use the alter system kill session command.
I tired using profile (CPU_PER_CALL) but didn't get the desired result.
I know tom killing a session is not a way to do, but i have no choice.
Thanks a TON
Regards
Abhi
and Tom said...
query-1 will definitely not work. Only "long running operations" are in v$session_longops - a query that runs for days - but uses a nested loop join would never appear in v$session_longops (it RUNS for a long time, but it does billions of tiny things)
A query that runs for hours and does two full scans and a hash join - it would appear in v$session longops on two different occasions. First there would be a full scan and hash of table ONE (that is ONE long running operation) then there would be a full scan and hash join of table TWO (that is the SECOND, distinct long running operation). You would miss many long running things using this technique (what if the first full scan takes 29 minutes and the second takes 29 minutes - each long running operation is LESS THAN 30 minutes - you would never get it)
query-2 is the way to go. last_call_et is maintained about every 3 seconds and tells you how long the session has been in its current "status" (active).
CPU per call seems the way to go - it is true that in 30 minutes, you might not get 30 minutes of CPU time, but it seems more logical.
Is this answer out of date? If it is, please let us know via a Comment