Oracle-L: RE: Excessive library cache latch contention

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 31 Jan 2003 06:40:03 -0800
Message-ID: <F001.00540536.20030131064003@fatcity.com>

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

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.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). Received on Fri Jan 31 2003 - 08:40:03 CST