Re: enqueue in statspack
- From: Alfonso León <aleon68@xxxxxxxxx>
- Date: Fri, 21 Oct 2005 11:00:58 -0500
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
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
Alfonso Leon
--
//www.freelists.org/webpage/oracle-l
Other related posts:
