Re: finding a session that is running a sql

On May 11, 11:59 am, Mladen Gogala <mgogala.SPAM..._at_not-at- verizon.net> wrote:
> On Fri, 11 May 2007 06:31:58 -0700, Ben 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?
>
> I would try V$OPEN_CURSOR. It has SID, ADDRESS, HASH and SQL_ID (10g)
> columns. If you have SQL ADDRESS & HASH, it should be trivial to identify
> SID for sessions that have that cursor open. Of course, V$OPEN_CURSOR
> table describes the SQL statements that cause you to curse, thus the
> name.
>
> --http://www.mladen-gogala.com

LOL about the "Of course, V$OPEN_CURSOR table describes the SQL statements that cause you to curse, thus the name."

Unfortunately, often true.

• Mark D Powell --