Re: Excessive library cache latch contention

The 'objects on hot latches' is likely to help only if there is a significant imbalance in the latch activity on the latch children (as you probably know).

The fact that 'waits holding' is rather high may be a hint. The most likely latches that you would be holding whilst waiting for the library cache are (I think) the shared pool and the library cache. And one of the more likely reasons for holding (one of) the library cache latches whilst waiting for another is the excessive use of public synonyms in
a system with a large number of distinct users.

The other thing to note is that the latch pressure occurs when the system is at
peak usage. It is possible that excessive CPU usage is causing the latch problem - the more CPU being used by 'other users' the longer it is before I get a chance to get back on the run queue and release the latch that someone else is waiting for. Possibly you could 'fix' the latch problem by finding opportunities to reduce the level of CPU used. The 'small number of specific forms' may be exactly the place where there is
some relatively inefficient SQL that uses CPU too aggressively.

Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 31 January 2003 15:55

>I've got access to Metalink and the doco you mentioned, but its too
generic.
>
>Its a forms application, but the developers are generating dynamic
where
>clauses on blocks, as well as the standard block.item notation in
>predicates, so there is a degree of literal SQL in the mix, and
multiple
>versions of SQL. I don't believe a larger shared pool will help as
there is
>a good chunk of free space there in v$sgastat.
>
>cursor_sharing=FORCE may be an option, but I'll need to test and
observe the
>results.
>
>The 106 latches we get seem to be in a small number of specific
forms, so
>I'll start with those.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).