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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
