FW: Distinguising one SQL execution from another

  • From: Smiley John - IL <SMILEYJ@xxxxxxxx>
  • To: "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Oct 2004 11:35:48 -0500

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@xxxxxxx] 
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 **
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  • » FW: Distinguising one SQL execution from another