RE: Excessive library cache latch contention

Jonathan,

I think you've got a point on all three counts. I am seeing an imbalance of gets / misses and sleeps on one of the latch children, and it does get worse when the CPU's are being hammered.

Public synonyms could be an issue in certain areas of the application but not all, though that should be resolved in the next release.

Thanks for your input - useful as ever!

N.

-----Original Message-----
Sent: 31 January 2003 16:15
To: Multiple recipients of list ORACLE-L

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).


--
This correspondence is confidential and is solely for the intended recipient(s). If you are not the intended recipient, you must not use, disclose, copy, distribute or retain this message or any part of it. If you are not the intended recipient please delete this correspondence from your system and notify the sender immediately. 

No warranty is given that this correspondence is free from any virus. In keeping with good computer practice, you should ensure that it is actually virus free. E-mail messages may be subject to delays, non-delivery and unauthorised alterations therefore, information expressed in this message is not given or endorsed by Sx3 unless otherwise notified by our duly authorised representative independent of this message.

Sx3 is a trading name of Service and Systems Solutions Limited, a limited company registered in Northern Ireland under number NI 32979 whose registered office is at 120, Malone Road, Belfast, BT9 5HT.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Harvey Neil
  INET: Neil.Harvey_at_sx3.com

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).