|
|
 |
 |
 |
 |
finding a session that is running a sql
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?
On May 11, 8:31 am, Ben <bal @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
-----------------------------------------------Reply-----------------------------------------------
On May 11, 9:31 am, Ben <bal @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.
-----------------------------------------------Reply-----------------------------------------------
On May 11, 9:36 am, "fitzjarr @cox.net" <fitzjarr @cox.net> wrote:
> On May 11, 8:31 am, Ben <bal @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
-----------------------------------------------Reply-----------------------------------------------
On May 11, 10:39 am, Charles Hooper <hooperc2 @yahoo.com> wrote:
> On May 11, 9:31 am, Ben <bal @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.
If the SQL is not a select, then you can also look into auditing the table(s). -----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
On May 11, 11:59 am, Mladen Gogala <mgogala.SPAM...@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 --
-----------------------------------------------Reply-----------------------------------------------
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?
V$OPEN_CURSOR contains ADDRESS, HASH and SQL_ID (10g) as well as the SID column. So, if you know ADDRESS and HASH, it shouldn't be that hard to extract the information from V$OPEN_CURSOR. That table containts the information about the sessions causing you to curse because of the !@#$ SQL they execute. --
http://www.mladen-gogala.com
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc