-
capture start of query submited by user SQL_ADDRESS in V$session
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|