Re: Library cache latch contention

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

You could compare v$system_event with v$latch to see if the number of 'latch free' events seems reasonable compared to the number of sleeps in v$latch - and there are a couple of micro-second time columns in v$latch, v$latch_children, v$system_event, v$session_event which you could cross-check for 'reasonableness'. And there are a couple of 'max time' columns that you could check in the event views. This MIGHT give you a clue about whether the high sleep figures are genuine, or a bug in the v$latch(_children) statistics.

--
Regards

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

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


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

____USA_(CA, TX)_August


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


"dias" <ydias_at_hotmail.com> wrote in message
news:55a68b47.0303011427.12f689bc_at_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