Library cache latch contention

From: dias <ydias_at_hotmail.com>
Date: 1 Mar 2003 14:27:54 -0800
Message-ID: <55a68b47.0303011427.12f689bc@posting.google.com>

Hi,

I observed the following stats on a 9.0.1 (os is tru64) database about "libary cache latch". The application is an industrial one with only small stored PL/SQL procedures.

The first report:

LATCH_NAME                GETS      MISSES   HIT_RATIO      SLEEPS

SLEEPS/MISS
------------------ ----------- ----------- ----------- -----------

---

library cache 1021637 126 1 108618601 862052.389

After setting session_cached_cursors = 100, and cursor_sharing = FORCE (the application does not use bind variables):

LATCH_NAME                GETS      MISSES   HIT_RATIO      SLEEPS

SLEEPS/MISS
------------------ ----------- ----------- ----------- -----------

---

library cache 988150 34 1 31887719 937874.088

There is no reloads. The v$sgastat reports 50 Mo of free memory, the shared pool is about 100 Mo. The time waited for latch free is very small (the average wait is 0).

The question is, what causes a lot of sleeps over this latch ?

Thanks Received on Sat Mar 01 2003 - 16:27:54 CST