| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_gmail.com>
Reply-To: aleon68_at_gmail.com
CC: oracle-l <oracle-l_at_freelists.org>
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_at_il.proquest.com> 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_at_freelists.org namens Gogala, Mladen
> Verzonden: do 10/20/2005 5:35
> Aan: 'aleon68_at_gmail.com'; 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_at_gmail.com]
> 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
> -------------------------------------------- ------------ -----------
-- Alfonso Leon -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2005 - 11:43:49 CDT
 |
 |