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. The problem I have is that when looking at open cursors for a session spinning on 106, I get a very long list. Ideally I'd like to pin down the specific cursor or object which its waiting for. I can sort of imply this by checking v$sql based on the hash_value of the session, but I'd like to be a bit more precise. I've also got another of Steve's scripts (objects_on_hot_latches) which sort of helps, but only really identifies a hot hash bucket, rather than a specific object. From this, I reckon I could do with a larger _kgl_bucket_count, but I'll attack the form specific issues first. Thanks, Neil. -----Original Message----- Sent: 31 January 2003 14:40 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Neil, Does this application use a lot of literal SQL? Do the users tend to use the same Appl processes during the peak times? And do they really complain about poor performance during the peak times? The contention could be due to the excessive parsing. Since you are noticing this at all your sites, it sounds like more of a SQL issue. Sharing of cursors, use of bind variables will help. Think of increasing SHARED_POOL_SIZE only when all other avenues to reduce this contention have been explored (and rejected). But a large shared_pool_size may worsen this situation. If you have access to Metalink, Note# 1012049.6 (and other notes referenced in there) may be of some help. You can also consider use of Statspack, during the peak times (if the version of your database supports Statspack). - Kirti -----Original Message----- Sent: Friday, January 31, 2003 5: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 WAITS HOLDING LEVEL ------------------------------------- ----------- ---------- ------------- ----- library cache 1281502 0.11% 2399666 5 cache buffers chains 273556 0.00% 23049 1 shared pool 73893 0.04% 91633 7 cache buffers lru chain 12236 0.01% 70756 3 session allocation 10639 0.06% 19969 5 row cache objects 7835 0.00% 29816 4 cache buffer handles 3646 0.00% 2575 3 transaction allocation 2344 0.01% 4341 8 enqueue hash chains 1831 0.01% 13722 4 redo writing 778 0.01% 17328 5 session idle bit 714 0.00% 0 1 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: [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).
