Hi Tom,
We have a situation here, in one our tables a column's value is changing and we are not able to find out which batch job (there are thousands) is responsible for that or which query is doing that. Now i am creating a trigger which will be firing whenever that columns value is UPDATING to the problematic value. I am trying to fetch the required SQL executed by using the below query :
SELECT b.sid,
LISTAGG(TRIM(c.sql_text), ' ') WITHIN GROUP(ORDER BY c.PIECE) sql_txt,
b.SQL_ID,
a.SERIAL#,
a.PREV_EXEC_START,
a.module,
a.SCHEMANAME,
a.program,
a.machine,
a.osuser
FROM V$SESSION a, v$open_cursor b, v$sqltext c
WHERE a.AUDSID = Sys_Context('USERENV', 'SESSIONID')
and b.SID = a.SID
and c.ADDRESS = b.ADDRESS
and REGEXP_COUNT(REGEXP_REPLACE(upper(b.SQL_TEXT),'[/*+]',''),
'UPDATE table_name|UPDATE INDEX HINT table_name') > 0
GROUP BY b.sid,b.SQL_ID,a.SERIAL#,a.module,a.SCHEMANAME,a.program,a.machine,a.osuser,a.PREV_EXEC_START;
But above query is taking much time and not suitable for trigger.
I also tried your query :
select sql_text
from v$sql
where address in ( select address
from v$open_cursor
where sid = (select sid
from v$mystat
where rownum=1) )
and upper(sql_text) like '%'But this query is slower than the above given query as it involves v$sql view which is having much more column than v$sql_text.
Is there any way we can improve its performance. I also talked to my DBA but he says he can not improve it further.
Thanks.
If you have some code on a trigger which is only firing when the column in question is being updated, then isn't the performance of it not really an issue ? ie, I'm assuming the column update is a rare event, because if it wasn't, you would simply trace your whole system and wait for it to occur.
Or from a different perspective - from within the trigger, can you just go straight to v$session and look at PREV_SQL_ID (the current SQL_ID being the sql you are currently running).
Or other options could be:
1) maybe just log the sid, serial# and exact time in a table, and then go looking at v$active_session_history after the fact
2) when trigger fires, turn on a sql trace, and submit a job to turn off the trace for that session in (say) 60 seconds time.