show runtime for all active queries

From: Chris Back <chris.back_at_gmail.com>
Date: 19 Mar 2005 09:08:47 -0800
Message-ID: <1111252127.673423.316470@l41g2000cwc.googlegroups.com>

Hi,

I am relatively new to Oracle, having spent a great deal of time with MySQL. I am looking for a way to get a quick snapshot of the current activity against the database (instance?). The ideal information I need is:

client (user + host)
sql of the current query
runtime for current query

In MySQL I could just do a simple "SHOW FULL PROCESSLIST" to get this information.
I have tried google, without much luck. It looks like I can get cummulative runtime for a session, but not at the individual query level.

Here is what I have so far:

SELECT ses.sid AS SID,

ses.serial# AS SERIAL_NUMBER,
ses.username AS USERNAME,
ses.status AS STATUS,
ses.blocking_session AS BLOCKING_SID,
ses.state AS STATE,
ses.type AS TYPE,
sql.sql_text AS SQL_TEXT,
sql.elapsed_time AS ELAPSED_TIME

FROM v$session ses, v$sql sql
WHERE ses.sql_id = sql.sql_id
AND STATUS = 'ACTIVE' Thanks for any help/direction you can offer.

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