Re: Library cache latch contention

From: dias <ydias_at_hotmail.com>
Date: 2 Mar 2003 02:25:07 -0800
Message-ID: <55a68b47.0303020225.cf91c3a@posting.google.com>

Hi Ricky,

I didn't give more details, because I'm not trying to tune the database. The waits are very small, and there no problem for end users.

I was just suprised by the number of sleeps over the "library cache latch". I tought that all the necessary parameters was set and there is enough space in the shared pool.

I also examined v$sqlarea, and there is no sql with multiple versions.

There just on other latch that has sleeps :

Report 1

LATCH_NAME                GETS      MISSES   HIT_RATIO      SLEEPS

SLEEPS/MISS

------------------ ----------- ----------- ----------- -----------
-----------
cache buffers chai    10788558         177           1         206    
  1.164
library cache          1021637         126           1   108618601 

862052.389

Report 2

LATCH_NAME                GETS      MISSES   HIT_RATIO      SLEEPS

SLEEPS/MISS

------------------ ----------- ----------- ----------- -----------
-----------
cache buffers chai     8793247         207           1         217    
  1.048
library cache           988150          34           1    31887719 

937874.088

Finally, I have used the very old utlbstat/estat, beacause it was not possible to setup Statspack.

Dias

Ricky Sanchez <rsanchez_at_more.net> wrote in message news:<3E618AE7.8A7EFAC3_at_more.net>...
> Dias-
>
> You really are not providing enough information here to offer serious
> analysis. You don't tell us how latch free waits relate to other wait
> events. Nor do you compare library cache latch sleeps to other latches.
> The raw numbers by themselves really don't say much.
>
> And, how does the first sampling period compare to the second?
>
> Why not zip up and post before and after statspacks? Makes a meaningful
> opinion possible.
>
> - ricky
>
>
> dias wrote:
> >
> > 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 Sun Mar 02 2003 - 04:25:07 CST