FW: Distinguising one SQL execution from another

Forgot to forward to the group...

-----Original Message-----
From: Smiley John - IL
Sent: Tuesday, October 12, 2004 11:33 AM To: 'John Kanagaraj'
Subject: RE: Distinguising one SQL execution from another

I considered this, but last_call_et only tells me the execution time of the current call (if the session status is ACTIVE, or session idle time if INACTIVE). It doesn't tell me anything about calls that occurred between peeks at v$session. Also, last_call_et is expressed in seconds, which makes it of questionable utility in measuring execution time of short duration calls.

The information in v$sesstat on executions, parse cpu, parse elapsed, etc, is an aggregate of all calls made by the session. There is no way that I can see to extract information about how many of those executions/parses were for a given SQL statement. Then there's the whole issue of recursive SQL. Even if I could somehow determine how many of the executions, parses, cpu time, and elapsed time shown in v$sesstat were for a given hash_value and address, it would still be necessary to determine which SQL statements were executed recursively on behalf of the SQL submitted by the application.

What I'm looking for is a way to profile each session so that I can see how much elapsed time and CPU time is spent executing each SQL statement for that session. Sure, I can get this from an extended trace (event 10046), but as wonderful as these are, they are expensive. What I want is to have a light weight monitor that gives a rough measure of how much elapsed/cpu time each session spends executing each SQL statement it submits. I don't care about individual executions (although min/max/mean/median would be nice), but I would like to see an aggregate for all of the executions of a SQL statement for each session.

As I mentioned, 10g does this on a per statement basis (not sure if it takes recursive SQL into account though), which aggregates all of the time spent by all sessions executing the SQL. What I'm looking for is a way to break this out per session.

John Smiley

-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com] Sent: Tuesday, October 12, 2004 11:03 AM To: Smiley John - IL
Subject: RE: Distinguising one SQL execution from another

John,

>Is there any way to tell if the session is executing on the
>same call or if they are two different executions of the same SQL?

Look at V$SESSION.LAST_CALL_ET for session having that same address/hv... This may help.

>How might I determine how many times the same SQL was executed by the
session
>between peeks at v$session? Better yet, how might I determine how much
time
>was spent during each execution. Assume that this is 8i or 9i.

This is impossible without a 'Cary' trace, but you _could_ look at differences in V$SQL.EXECUTIONS and the V$SESSTAT stats for that session which has the same address/hv.  

Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Fear connects you to the Negative, but Faith connects you to the Positive! I Jn 4:18

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- http://www.freelists.org/webpage/oracle-l