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 Received on Thu Oct 20 2005 - 10:39:00 CDT