Hi everybody,

I am trying to capture the estimated COST and estimated completion time for a query submitted by a user. See query below. I get results back from this only when a query is running. I need to capture somehow the start of a query submitted by an user.
IN V$SESSION there is a field that gets assigned a Hex address during the time the query is running. SQL_ADDRESS after the query completes this is set to 00. How can I capture the value of this field? when users submit a query? Is there an event trigger I could use?



ANY help will be appreciated.

Cristina DUTTA


SQL> select
2 substr(b.terminal,1,12),
3 substr(b.username,1,12),
4 a.cost,
5 a.CPU_COST,
6 a.IO_COST,
7 (a.elapsed_time/a.starts)/1000000,
8 a.active_time
9 from
10 v$SQL_PLAN_STATISTICS_ALL a, v$session b
11 where
12 a.address = b.sql_address and
13 a.id = 1 and a.cost is not null and starts > 0



SUBSTR(B.TER SUBSTR(B.USE COST CPU_COST IO_COST (A.ELAPSED_TIME/A.STARTS)/1000000
------------ ------------ ---------- ---------- ---------- ---------------------------------
CDUTTA RISKONUS_V3 13268 13268 48.921357

SQL> /
truncating (as requested) before column ACTIVE_TIME

***********


SUBSTR(B.TER SUBSTR(B.USE COST CPU_COST IO_COST (A.ELAPSED_TIME/A.STARTS)/1000000
------------ ------------ ---------- ---------- ---------- ---------------------------------
CDUTTA RISKONUS_V3 13268 13268 51.1788053

SQL> /
truncating (as requested) before column ACTIVE_TIME


no rows selected (*****NOTE THAT THE QUERY SELECT COMPLETED)

SQL> /
truncating (as requested) before column ACTIVE_TIME


no rows selected