Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Distinguising one SQL execution from another
I know that I can get all of this from 10046 traces, including the recursive
SQL. I've been using them for years and thanks to Cary and others, I know
how to interpret them pretty well. The issue is that gathering these traces
is expensive - you can't expect to run them all of the time and certainly
not for all sessions on a production database. What I'm not convinced of
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 fit the bill.
I'd like to be able to do this with SQL against V$ and/or X$ tables, but I'd consider using a direct SGA attach method such as described by Kyle Hailey. I just need to know where to look in the SGA for the information.
John Smiley
-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com]
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 still 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 covers recursive SQL btw. I agree - what we need is a tool that can parse a bunch 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!
John
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 12 2004 - 12:04:09 CDT
![]() |
![]() |