Re: Library cache latch contention

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
>
> 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
Received on Mon Mar 03 2003 - 02:30:10 CST