Library Cache Locks , Library Cache Pin , Row Cache Lock
947991Sep 8 2012 — edited Sep 8 2012Hi,
Want to understand on Library Cache Locks , Library Cache Pin , Row Cache Lock ?
I will tell what I have Understand. Please Correct me IF I am on other side .
Library Cache Locks : These locks are acquired in shared mode during the parsing/compilation of the sql/PLSQL and release after the parse/compilatation.
Library Cache Pin : If I want to change the structure of Table/Modify a packeage/procedure, Library Cache Pin Must be acquired after the Library cache Lock.If a session is executing an SQL, then library cache pins will not be available and there will be waits for library cache pins.
A doubt : Suppose A sql is hard parsed and its a long running. I want to change the definition of Object that is referenced in the SQL. A library Cache lock and Cache PIn should Be Acquired. Where is the information that this object is refrenced , Since the lock is relesed eariler.
Row Cache Lock : To prevent the data Dictionary. Suppose IF Any DDL(Alter Table) need to be run , then this lock is required. When ever Such a Event Occurs , We should look at stats od DC like DC_USERS , dc_rollback etc. I require some more explanation on this for better understanding.
----> So If I require to change the table definition , I must acquire Libarary Cache Lock /Pin and row cache Lock.
Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! " [ID 278316.1]
Troubleshooting Library Cache: Lock, Pin and Load Lock [ID 444560.1]