Re: show runtime for all active queries

Note that the v$session.machine columns tells you the machine the session connected from and is not the host name of the database server.  You can get that from v$database.

Some v$ tables do have fairly significant overhead involved in accessing them. This overhead generally takes the form of latch requests/waits as the shared pool is protected by latches. You can query your session latch statistics, query a v$ view, and recheck the statistics to see which views have a relatively high cost of access. Some of the sql area views are among the high cost access views.

Generally speaking you can query most v$ views with very little latching. I would avoid querying the actual SQL text unless you really need it. If you have an OLTP application the majority of SQL should run so fast that there is no point in trying to capture the current statement unless you are actively trying to tune that specific process.  In this last case you should be using the Oracle session trace facility.

For statements that run more than a couple of seconds you can use the column, last_call_et to get the time of the last SQL command in seconds. When the status is ACTIVE this is the time the current SQL statement has been running. When the status column value is INACTIVE then this is the time since an SQL statement was issued, not the time since the last statement completed.

See the Oracle version# Reference manual for information on the Dictionary views and the v$ views.

HTH -- Mark D Powell -- Received on Sun Mar 20 2005 - 14:28:53 CST