RE: enqueue in statspack

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 
-------------------------------------------- ------------ ----------- -------- 
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 

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