latch: library cache in 10.2.0.4

From: John Darrah <darrah.john_at_gmail.com>
Date: Fri, 29 Jun 2012 09:55:48 -0600
Message-ID: <CANGO=rn9PYvTbASUsWiMnk2mG1smC82PR=vBiRDBdnd2o2Hw2Q_at_mail.gmail.com>

I am seeing periods of extreme contention on shared pool and library cache latches in one of my production databases. The periods are intermittent with long gaps between incidents. This contention usually only lasts a few seconds but on one occasion required a bounce of the database to clear the contention. I was not present for any of these incidents unfortunately but looking back in the AWR for the period requiring a bounce does show some interesting information.

Taken from dba_hist_active_sess_history:

sample time

                 event                       p1
       p2            distinct number of sessions       sum of time

waited (seconds)
18-JUN-12 10.35.59.463 AM

     latch: library cache 12875402112             215          2
                                          61.78762
18-JUN-12 10.35.59.463 AM
     latch: library cache 12875401152             215          5
                                        144.05647
18-JUN-12 10.35.59.463 AM
     latch: library cache 12875401792             215          8
                                        278.460855
18-JUN-12 10.35.59.463 AM
     latch: library cache 12875404032             215         12
                                       373.605342
18-JUN-12 10.35.59.463 AM
     latch: library cache 12875402432             215         13
                                       416.581586
18-JUN-12 10.35.59.463 AM
     latch: shared pool   4295924840              214          22
                                         791.216218
18-JUN-12 10.35.59.463 AM
     latch: library cache 12875400512             215         32
                                       907.177485

The output above shows there were 32 sessions all trying to get the library cache latch at address 12875400512 and in that sample spent a total of 907 seconds trying for that latch. Most of the sql_ids listed are NULL and I’m not really sure what that means. I would think that for a library cache latch I should have an sql_id but I could just be completely misinterpreting when library cache latches are acquired.

So my questions are: Why woud there be so many sessions apparently not parsing SQL statements contending for library cache latches and has anyone seen this behavior elsewhere?

Thanks,

John

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 29 2012 - 10:55:48 CDT