Re: finding a session that is running a sql

On May 11, 9:36 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On May 11, 8:31 am, Ben <bal..._at_comcast.net> wrote:
>
> > 9.2.0.5 EntEd AIX5L
>
> > I have a SQL that is consistently being executed 5.5 million times per
> > day and we can't figure out what it is that is running it. I'm
> > crossing v$session and v$sqlarea for the statements hash value and no
> > session ever comes up for it. I'm watching the executions in v$sqlarea
> > growing but can't match it to a session while it's growing. Any ideas
> > on a better method to figure out who/what is executing this sql?
>
> You might try setting event 10046 at level 8 and examining the
> resulting trace files. You'll probably need to do this via a logn.sql
> script, since you cannot find the session or sessions executing the
> offending code any other way.
>
> David Fitzjarrell

As an addendum to what David suggested, we have done this with an "alter system set event" for a *system* level 10046 trace. If the statement is executed 5.5 millions times per day, you would probably only need the trace on for about 15 minutes (if that long) to find it, as you are averaging almost 4K executions per minute.

/grep -i "string about which you are concerned" *trc/ in your udump directory...and don't forget to turn the trace off :)

HTH, Steve Received on Fri May 11 2007 - 09:51:34 CDT