Thread: Library cache pin


Permlink Replies: 2 - Pages: 1 - Last Post: Aug 27, 2007 9:29 AM Last Post By: Jonathan Lewis
user504347

Posts: 134
Registered: 04/19/06
Library cache pin
Posted: Aug 27, 2007 1:10 AM
Click to report abuse...   Click to reply to this thread Reply
What means having libray cache pin as first on Top 5 Timed Events?

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
library cache pin 3,719 8,268 2,223 51.1 Concurrency
CPU time 7,300 45.2
db file scattered read 22,249 46 2 .3 User I/O
gc cr multi block request 285,107 33 0 .2 Cluster
db file sequential read 14,427 27 2 .2 User I/O

Thanks a lot
orawarebyte

Posts: 3,648
Registered: 10/02/00
Re: Library cache pin
Posted: Aug 27, 2007 1:39 AM   in response to: user504347 in response to: user504347
Click to report abuse...   Click to reply to this thread Reply
Datafile Scattered and Sequential Reads.

Both means reads of datafile physical blocks on disk, where scattered means potentially random (full physical scans) in scattered noncontiguous parts of the buffer and sequential means more focused (index reads).

Library Cache Pin

A wait on this latch occurs when an seqeul code statement already in the library cache is executed again.

For more detail about library cahche go through tom's link very nice demo..you will find library cache pin there

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9496983726463

For gc cr multi block request dont know exactly..

Khurram

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07
Re: Library cache pin
Posted: Aug 27, 2007 9:29 AM   in response to: user504347 in response to: user504347
Click to report abuse...   Click to reply to this thread Reply
It would help us to interpret the significance of these figures if you told us the Oracle version, the number of CPUs and the length of the snapshot. (It also helps if you use the code tags to put the output into fixed font - check the FAQ link at top right, I can never remember the details).

Given that you are running RAC, and this looks like 10g, and the CPU usage is a significant fraction of the reported time, and you have a lot of scattered reads (with associated gc cr multiblock requests) going on I'd take a guess that you have a problem with excessive CPU usage causing a library cache problem as a side effect.

The library cache pin is simply a session marking a library object (e.g. cursor) for use - either shared (because it wants to use an existing cursor, say) or exclusive (because it needs to invalidate and recompile a cursor).

This should be a very rapid action but in your case it isn't - your average wait is more than 2 seconds. It isn't possible to say why this is happening, but if you are doing cross-instance invalidations (e.g. truncating tables regularly) AND you have a very busy system (lots of CPU in use) that can be enough to cause these symptoms.

On a long shot - the scattered reads (possible tablescans) may be causing some remastering (gc remaster or similar), and this can cause a freeze (gcs drm freeze or similar): this shouldn't cause problems to the library cache - which operates under the 'global enqueue' processes rather than 'global cache' processes - but maybe there is some connection.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
Legend
Guru Guru : 2500 - 1000000 pts
Expert Expert : 1000 - 2499 pts
Pro Pro : 500 - 999 pts
Journeyman Journeyman : 200 - 499 pts
Newbie Newbie : 0 - 199 pts
Oracle ACE Director
Oracle ACE Member
Oracle Employee ACE
Helpful Answer (5 pts)
Correct Answer (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums