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


About these ads
  1. the DBA should not do the maintenance job at peak load period . :-)

    • 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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 462 other followers

%d bloggers like this: