If you find it, John, please let me know. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * 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 www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx = [mailto: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 = 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 =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 = 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l