Re: show runtime for all active queries

From: Chris Back <chris.back_at_gmail.com>
Date: 19 Mar 2005 12:47:09 -0800
Message-ID: <1111265229.660869.222870@o13g2000cwo.googlegroups.com>

I have likely already answered my own question, my new query looks like this...

SELECT ses.sid AS SID,

 ses.username AS USERNAME,
 ses.machine AS HOST,
 ses.command AS COMMAND_NUMBER,
 ses.status AS STATUS,
 ses.blocking_session AS BLOCKING_SID,
 ses.state AS STATE,
 ses.type AS TYPE,
 ses.wait_time AS LAST_WAIT_TIME,
 ses.seconds_in_wait AS CURRENT_WAIT_TIME,
 ses.wait_class AS WAIT_CLASS,
 sql.sql_text AS SQL_TEXT

FROM v$session ses, v$sql sql
WHERE ses.sql_id = sql.sql_id
AND TYPE <> 'BACKGROUND'

Having done more research, it still seems there may be a better way. I have seen in one thread where querying the V$ views was discouraged because of potential performance issues?

How are other people proactively monitoring their databases for runnaway queries, abnormal number of connections, etc?

Thanks again,

Chris Back
Sr. Systems Administrator Received on Sat Mar 19 2005 - 14:47:09 CST