Oracle

library_cache_lock and library_cache_pin

Today suddenly an issue popped up where in users started saying that ‘APPLICATION SEEMS TO BE HANGING’, the front end users were not getting the output on there screens.Whenever i hear things like query is slow or seems to have hanged , the first thing i check is the SESSION WAITS, and it was interesting to see lots of Β  ‘LIBRARY CACHE LOCK and LIBRARYΒ  CACHE PIN’ .The database was onΒ  8.1.7.4.After few more investigation saw that dba has run analyze on a particular table and rest of the users session were waiting to acquire the lock on the same table.As soon as, the session which was analyzing the table was killed , the issue got resolved.
Some links to read
http://blog.tanelpoder.com/2008/01/08/updated-session-wait-script/
http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
http://dioncho.wordpress.com/2009/05/15/releasing-library-cache-pin/
Metalink DOCID:- 444560.1 and 122793.1

2 thoughts on “library_cache_lock and library_cache_pin

    1. Hi,
      Thanks for visiting the blog.Thats very true.On peak load period one should not do th emaintenance job, but the dba had run the analyze command is off-peak period and it continued to run in peak hours.So had to kill the session πŸ™‚
      Regards,
      Anand

Leave a Reply