Re: enqueue in statspack

[Alfonso León <aleon68@xxxxxxxxx>]

thanks for your answers.

This is the explanation I got from the oracle forum

From: Kristian Myllymäki 21-Oct-05 15:32
Subject: Re : enqueue difference between system events and enqueue stat




If a session is waiting for an enqueue request, the TIME_WAITED_MICRO
in v$system_event is incremented for every three seconds (when an
enqueue timeout occurs). However, v$enqueue_stat is only incremented
after the session has acquired or cancelled the enqueue resource
request.

This means that during the enqueue request, v$system_event will show
higher values than v$enqueue_stat. So if you have had long running
enqueue requests that haven't been acquired in your statspack snapshot
window, the snapshot of v$system_event will show higher values than
v$enqueue_stat.

You could try to span more statspack snapshots in your report, and see
if the values still differ.

/Kristian



On 10/20/05, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:
> I very much agree w/ what Mladen has to say here.
>
> To take it a step further, though, once the TX enqueue wait has occurred, 
> it's impossible to determine what the root cause was, which of course is what 
> you need to do to solve the problem.  It is probably worth actively 
> monitoring the process that's giving you problems, trying to catch it in the 
> act.  Some of the information you'll want to make a note of is:
>  - What mode (S, X, something else?) is the TX lock waiting on?
>  - What SQL statement caused your session to start waiting?
>  - What session are you waiting on?
>
> There are a couple of ways to capture this info.  You may try a 10046 trace 
> if you're comfortable with that, or you may query V$LOCK, V$SESSION_WAIT and 
> V$SQL.  Once you have that info, you may be able to piece together what's 
> happening and where things are going wrong.
>
> Hope that helps,
>
> -Mark
>
> ________________________________
>
> Van: oracle-l-bounce@xxxxxxxxxxxxx namens Gogala, Mladen
> Verzonden: do 10/20/2005 5:35
> Aan: 'aleon68@xxxxxxxxx'; oracle-l
> Onderwerp: RE: enqueue in statspack
>
>
>
> Alfonso, you're referring to the fact that there is an outrageous difference 
> in the time waiting and the time spent in locks. As anybody can tell you, 
> STATSPACK is not very useful. What STATSPACK will give you is a crude
>
> pointer where to look. To actually determine the cause of the problem (and I 
> assume that there is one) you will still have to locate the problem session 
> (sessions?) and see what is it (what are they)  waiting for and which locks 
> are problematic. The top 5 events section in the SP-report is constructed by 
> querying V$SYSTEM_EVENT at the stime of each snapshot and then subtracting 
> one from another. There is O'Reilly book called "Optimizing
>
> Oracle For Performance" which explains how time accounting can be problematic 
> even within a single trace file, and, of course, even more so in
>
> a thing like STATSPACK which essentially queries tables unprotected by any
> relational integrity mechanism and computes something that should be an 
> overall picture of a system over a period of time. Relating data from 
> V$SYSTEM_EVENT and V$LOCK from an overview provided by STATSPACK is a waste 
> of time.
>
> --
> Mladen Gogala
> Ext. 121
>
> -----Original Message-----
> From: Alfonso León [mailto:aleon68@xxxxxxxxx]
> Sent: Thursday, October 20, 2005 11:03 AM
> To: oracle-l
> Subject: enqueue in statspack
>
> Hello:
> I have a question about enqueues, we have oracle 9.2.0.6 on a HP UX..
> here is a extract on an statspack report
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~                                  % Total
> Event                                       Waits Time (s) Ela Time
> -------------------------------------------- ------------ ----------- --------
> enqueue                                      4,515 10,582 36.10
>
> but on the enqueue details there is no indication of a significant wait.
>
>
>         Avg Wt         Wait
> Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)     Time (s)
> --        ------------       ------------    -----------
> ----------- ------------- ------------
> TX      435,219      435,219           0                  154
> 156.29           24
> SQ        7,062        7,062           0                    760
>  3.69            3
> CU        6,743        6,743           0                        1
>   10.00            0
> HW          770          770           0                         1
>      .00            0
>               -------------------------------------------------------------
>
> so how can i know what was the DB waiting for
>
> TIA
> --
> Alfonso Leon
> --
> http://www.freelists.org/webpage/oracle-l
>
>


--
Alfonso Leon
--
http://www.freelists.org/webpage/oracle-l

• Follow-Ups:
  • Re: enqueue in statspack
    • From: M Rafiq

• References:
  • RE: enqueue in statspack
    • From: Gogala, Mladen
  • RE: enqueue in statspack
    • From: Bobak, Mark

Other related posts:

enqueue in statspack

Re: enqueue in statspack

Re: enqueue in statspack

Re: enqueue in statspack

Re: enqueue in statspack

RE: enqueue in statspack

RE: enqueue in statspack

Re: enqueue in statspack

Re: enqueue in statspack