Re: finding a session that is running a sql

On May 11, 9: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?

Just out of curiosity, what is the SQL statement that you are seeing executed 5.5 million times a day?

You might want to check the table level trigger code in the database for that SQL statement. The tip that David provided to you will tell you if the problem is caused by application level code or if it is caused by trigger code or the cost based optimizer based on the dep= statements in the 10046 trace file. dep=0 is application code, while dep=1, dep=2, dep=3, ... dep=n is trigger code, space management, or cost based optimizer code.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri May 11 2007 - 09:39:47 CDT