Re: finding a session that is running a sql

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