Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Library cache Latch / Lock / Pin

538022Sep 20 2007 — edited Sep 23 2007
Hello guys,
i already thought that i have understand the library cache latch / lock and pinning topic ... but now i have read some different metalink notes and i am a little bit confused...

Metalinknote #444561.1
===============================================================
-> Library cache lock: This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either: One client can prevent other clients from accessing the same object.

-> Library cache pin: This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.
===============================================================

Oh ok... but until now i thought, if i open a cursor i am holding a lock ... and other processes can also have a look at the object. If i am executing my cursor it is pinned and nobody can change it. But in the metalinknote stands "modify or examine" for pinning. I thougt if i examine a object i am holding only "a lock"... i am only a interested party..


Metalinknote #62143.1
===============================================================
-> The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool.

-> The library cache latches (and the library cache pin latch in Oracle 7.1) protect operations within the library cache itself.
===============================================================

Ok the shared pool latch is clear. But the description of the "library cache latch" is the same as the locking and pinning. The locks and pins are also protecting objects in the library cache... so why are 2 mechanisms needed?

For example:
If we have a lot of parsing and no cached cursor...so the library cache has to be searched for the sql statement... in this case i can see library cache latches in the STATSPACK... why not seeing locks (or like the description in the metalinknote -> pinning)?

What is the difference and which event is shown in which case in the STATSPACK or AWR?

EDIT:
Now i have found the following presentation:
http://www.perfvision.com/papers/unit6_shared_pool.ppt

On Page 18 stands "Library Cache Latch" broken out into
"library cache pin allocation
library cache lock allocation
library cache lock
library cache
library cache pin
library cache load lock"

So are all these events a library cache latch?
If yes... when is a lock shown instead of a latch: library cache in the AWR?

Regards
Stefan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2007
Added on Sep 20 2007
9 comments
6,893 views