The customer has a data guard environment, everyday, they will convert the standby to physical standby at 2:00 am and back to snapshot standby at 3:30 am, the job_queue_processes parameter will be changed when the database is converted to snapshot standby.

 

 

All the wait events related to Library Cache can be put in this category. Actually there are the 3 following wait events:

Library cache lock
Library cache pin
Library load lock

The Library Cache wait events typically occur in the following 4 circumstances:

A. During compilation of PL/SQL, views, packages, functions, procedure, triggers, types, etc.
B. During ALTER, GRANT, REVOKE privileges and roles on packages, functions, etc.
C. During loading object into the memory.
D. During calculating statistics.

Library cache lock
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that:

1.One client can prevent other clients from accessing the same object.
and
2. The client can maintain a dependency for a long time (for example, no other client can change the object).

During the parsing or compilation of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym) a library cache lock is required for the referenced objects, which is released at the end of parse or compilation. All this operation is synchronous and deadlock sensitive.

Tip: To avoid Library cache wait events to occur you can try two things,
1. First try not to compile source code or grant/revoke/alter privileges while batch programs run at the database
2. Second try not to calculate statistics while batch programs run at the database

Finally what is the conclusion:
========================
Library cache pins and load locks may occur during PL/SQL, views, types, etc. compilation or recompilation. The compilation is always explicit (application installation, upgrades, applying patches), but object recompilation may happen transparently due to object invalidations.

Dealing with slow downs related to “mysterious” library cache pins and load locks we should look for the reason of the database object invalidations. They are likely to be triggered by actions causing changes to “LAST_DDL” attribute of database objects that have other dependent ones. Typically they are the object maintenance operations – ALTER, GRANT, REVOKE, replacing views, etc. "" This behavior is described in Oracle Server Application Developer’s Guide as object dependency maintenance.""

After object invalidation, Oracle tries to recompile the object at the time of the first access to it. It may be a problem in case when other sessions have pinned the object to the library cache. It is obvious that it is more likely to occur with more active users and with more complex dependencies (eg. many cross-dependent packages or package bodies). In some cases waiting for object recompilation may even take hours blocking all the sessions trying to access it.

对象不能编译导致锁

 

Attachments :

 

library cache lock is due to invalid "SYS.SCHEDULER$_EVENT_QUEUE