Data dictionary find time of long running query
Howdi All,
I'm trying to figure out a way to find queries running longer than X minutes in the data dictionary. It's been difficult to limit it to a specific query. Sessions are no problem. Here's the latest query I've come up with.
SELECT sess.status,
sess.username,
sess.machine,
sess.program,
sess.process,
s.optimizer_cost,
ops.message,
round(ops.elapsed_seconds/60),
TRIM (s.sql_text),
ops.time_remaining
FROM v$sql s,
v$session sess,
v$session_longops ops
WHERE s.address = sess.sql_address
AND sess.status = 'ACTIVE'
AND ops.sid = sess.sid
AND sess.username not like 'SYS%'
Thanks.
-jim
Software/Hardware used:
Software/Hardware used:
ASKED:
October 8, 2004 10:53 AM
UPDATED:
October 18, 2004 10:20 AM
Answer Wiki:
Jim:
I'm not sure what you mean by 'limit it to a specific query'.
Do you mean search for specific query text with LIKE in v$sqltext or v$sqlarea?
To see all answers submitted to the Answer Wiki: View Answer History.
I mean that v$session tells how long a specific session has been running, not how long a particular query is running. I’m trying to find all queries running longer than a certain amount of time. I don’t care about sessions, because sessions that access our database in general run many queries. That’s why I join with the “long ops” table. However, the results from this join don’t seem very accurate.
Thanks.