Re: show runtime for all active queries

"Chris Back" <chris.back_at_gmail.com> wrote in message news:1111265229.660869.222870_at_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
>

You can collect a lot of historical information with Enterprise Manager. This needs an agent on each server (Data Gatherer). Or you could schedule your own sql scripts and store the output in some home-made tables. Received on Sat Mar 19 2005 - 15:26:16 CST