RE: Distinguising one SQL execution from another

  • From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
  • To: "'Oracle-L (E-mail)'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Oct 2004 19:47:38 -0500

If you find it, John, please let me know.

Cary Millsap
Hotsos Enterprises, Ltd.
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary
- SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit for schedule details...

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
On Behalf Of Smiley John - IL
Sent: Tuesday, October 12, 2004 12:08 PM
To: 'John Kanagaraj'
Cc: Oracle-L@Freelists. Org (E-mail)
Subject: RE: Distinguising one SQL execution from another

I know that I can get all of this from 10046 traces, including the =
SQL.  I've been using them for years and thanks to Cary and others, I =
how to interpret them pretty well.  The issue is that gathering these =
is expensive - you can't expect to run them all of the time and =
not for all sessions on a production database.  What I'm not convinced =
yet is that this is the only way to get the information I'm looking for.

What I'm looking for is a light weight method to get a rough idea of how
much time (elapsed and CPU) each session spends executing (broken out by
parse, execute and fetch if I can get it) each SQL statement it submits,
taking recursive SQL into account.  This would be a monitor that runs
continuously on heavily loaded production systems.  10046 traces do not =
the bill.

I'd like to be able to do this with SQL against V$ and/or X$ tables, but =
consider using a direct SGA attach method such as described by Kyle =
I just need to know where to look in the SGA for the information.

John Smiley =20

-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj@xxxxxxx]=20
Sent: Tuesday, October 12, 2004 11:48 AM
To: Smiley John - IL
Subject: RE: Distinguising one SQL execution from another

Hi John,

I think we agree this can be obtained _only_ from a 10046. The issue =
is that you will need to turn on/off the trace for all such sessions and
spend time collecting/analyzing the trace files. A 10046 trace even =
recursive SQL btw. I agree - what we need is a tool that can parse a =
of tracefiles and spit out that figures you want - not impossible, given
that we have the likes of Perl, but just too much to analyze...

Let us know if you get to do this!


Other related posts: