RE: enqueue in statspack
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <MGogala@xxxxxxxxxxxxxxxxxxxx>, <aleon68@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Oct 2005 11:43:00 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
