Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Excessive library cache latch contention
Just to clarify...
If you upload something to our site, and you haven't purchased a license to run the Hotsos Profiler, then make sure you send a note to matt.seaton_at_hotsos.com, letting him know that you're interested in seeing a demo profile of your data.
We're glad to do demos of our software, especially if your trace file is particularly interesting. Bear with us, though: if everyone responds at once, then there'll be some queueing delay for assistance :).
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London
-----Original Message-----
Michael
Sent: Friday, January 31, 2003 3:13 PM
To: Multiple recipients of list ORACLE-L
Correction on this ... I should have said www.hotsos.com would have some additional insights on dealing with 10046 data for you if you are interested.
-----Original Message-----
Sent: Friday, January 31, 2003 10:57 AM
To: Multiple recipients of list ORACLE-L
Harvey,
Personally, I would go right to the user(s) who has complained about the slowdown and have them run the application at the peak hour/period where things seem very slow.
Set a 10046 event level 8 trace on that session(s) after they log on and then take a look at the trace file in the udump area after they are finished executing the queries.
There should be some strong clues in there about why the session is the waiting.
My bet is on some poorly written SQL and these session competing for blocks.
Check out Oracle Performance Tuning 101 Book and you can send your 10046 event data to www.hotsos.com for review if you are confused by it
fwiw. good luck. mike
-----Original Message-----
Sent: Friday, January 31, 2003 3:09 AM
To: Multiple recipients of list ORACLE-L
We've got about 30 sites all running the same application, and I'm
consistently seeing large numbers of 106 (library cache) latch free
waits.
They tend to happen at peak times during the day, and in the worst case
I
saw 12 sessions all on a 106 latch free wait event, spread across 3
P1RAW
addresses.
Running Steve Adams latch_sleeps scripts, yields the following:
LATCH TYPE IMPACT SLEEP RATE WAITSHOLDING
library cache 1281502 0.11%2399666
cache buffers chains 273556 0.00%23049
shared pool 73893 0.04%91633
cache buffers lru chain 12236 0.01%70756
session allocation 10639 0.06%19969
row cache objects 7835 0.00%29816
cache buffer handles 3646 0.00%2575
transaction allocation 2344 0.01%4341
enqueue hash chains 1831 0.01%13722
redo writing 778 0.01%17328
session idle bit 714 0.00%0
The results above are from an instance which has been up for 5 days
As you can see, library cache latch has a big impact (though I must
admit,
I'm not sure what Steve's IMPACT formula actually tells me). When I
check
across other sites, I see a similar pattern - large numbers of 106 latch
misses and sleeps.
I guess what I'd like to know is where these latches are happening,
which
objects / cursors etc are causing the contention. I've grappled with SQL
against x$kglob, trying to join back to the P1RAW but am not getting
very
far.
Any ideas?
TIA. Neil.
--
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 servicesto: 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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary Millsap
INET: cary.millsap_at_hotsos.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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). Received on Fri Jan 31 2003 - 16:35:08 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
![]() |
![]() |