Re: Library cache latch contention

From: Yong Huang <yong321_at_yahoo.com>
Date: 4 Mar 2003 14:59:25 -0800
Message-ID: <b3cb12d6.0303041459.18442d32@posting.google.com>

Hi, Dias,

Please keep us posted. I'd like to see how Oracle responds to your problem. I can't explain what you see. I just want to say that I looked at a similar database:

v$version:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production PL/SQL Release 9.0.1.2.0 - Production
CORE 9.0.1.2.0 Production
TNS for Compaq Tru64 UNIX: Version 9.0.1.2.0 - Production NLSRTL Version 9.0.1.2.0 - Production

uname -a: OSF1 spe155 V5.1 732 alpha

that everything looks normal:

ADDR                 LATCH#     LEVEL#
---------------- ---------- ----------
NAME                                                                  

GETS

---

---

    MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN

---------- ---------- -------------- ---------------- -------------
WAITS_HOLDING_LATCH  SPIN_GETS     SLEEP1     SLEEP2     SLEEP3    

SLEEP4

------------------- ---------- ---------- ---------- ----------
----------
    SLEEP5     SLEEP6     SLEEP7     SLEEP8     SLEEP9    SLEEP10   

SLEEP11
---------- ---------- ---------- ---------- ---------- ----------

---

 WAIT_TIME

---

000000005801AF80        142          5
library cache                                                      
1953745
      1490        732              0                0         20760
                596        894        483         97         16       
  0
         0          0          0          0          0          0     
    0
         0

No anomaly is seen in v$latch_children either. This publicly accessible instance ORAPORT on 192.233.54.155 was started about 25 hours ago.

Yong Huang

ydias_at_hotmail.com (dias) wrote in message news:<55a68b47.0303030030.4d4caebf_at_posting.google.com>...
> Thanks Jonathan and Ricky,
>
> I'll check with Oracle Support.
>
> Dias
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b3sps7$40m$1$8302bc10_at_news.demon.co.uk>...
> > Your results do look odd - after all, you can only
> > start accumulating sleeps after a miss, and with a
> > minimum sleep of 1/100 of a second (which is what
> > it used to be in v8 and below), you seem to be averaging
> > more than three hours of wait time every time you
> > miss a library cache latch (and that isn't allowing
> > for the roughly exponential increase in the length
> > of successive sleeps).
> >
> > I suspect an anomaly somewhere in the code where
> > Oracle 9 has an option to yield immediately (without
> > spinning) and use a much shorter sleep time. This type
> > of strategy change in the low-level code is likely to cause
> > all sorts of upsets and anomalies (or perhaps things that
> > look like anomalies) to appear in the migration from 8.1
> > through 9.0 to 9.2
Received on Tue Mar 04 2003 - 16:59:25 CST