Re: enqueue in statspack

I have observed that after applying 9206 patchset it requires more enqueue_resources then before. Most of the time default enqueue_resources 968 were suffecient for most of the databases.

Is there any reason for that? There is no change in application code.

Regards
Rafiq




From: Alfonso León <aleon68@xxxxxxxxx> Reply-To: aleon68@xxxxxxxxx CC: oracle-l <oracle-l@xxxxxxxxxxxxx> Subject: Re: enqueue in statspack 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>



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


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

Other related posts: