Re: Statspack Report

At the event level, Oracle records a timeout on an enqueue every three seconds and keeps on waiting. At the statistics level, an enqueue timeout is recorded only when the enqueue is finally acquired.

Your statistics suggest you have a small number of enqueue waits which wait (and keep timing out) for a long time.

Given the lengths of the wait, and the relatively low numbers you have for total enqueues in the snapshot anyway, one option is simply to try to spot the problem in real time.

select * from v$lock
where request != 0
;

(I may have got the name of the REQUEST
column wrong - do a describe of v$lock before trying this).

Repeat this query every 10 seconds or so for a few minutes until you see an example of a request with a non-zero CTIME. Then track down (from the SID) who it is and what they are doing.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Oradba Linux wrote in message ...


>Hi

>On my Statspack report ( oracle 8.1.7.2 on hp-ux 11.0) , Top 5 wait

event


>section , report shows enqueue as most waited event . How do i

proceed


>from here to find out which enqueue it waited for ?

>

> Event                                               Waits  Time (cs)

Wt Time


>-------------------------------------------- ------------ -----------

- -------


>enqueue                                             1,409

392,916


>87.81

>

>Also from the details section of the report .

>Event                               Waits   Timeouts  Time (cs)

(ms)   /txn


>---------------------------- ------------ ---------- ----------- ----

-- ------


>enqueue                             1,409      1,204     392,916

2789    0.7


>

>this is from the instance activity stats

>

>enqueue conversions                              0          0.0

0.0


>enqueue releases                            42,164          2.0

20.4


>enqueue requests                            42,162          2.0

20.4


>enqueue timeouts                                 0          0.0

0.0


>enqueue waits                                  115          0.0

0.1


>

>Any pointers to documentation as to how to investigate from here ?

>

>Thanks

>