Document Display
"library cache lock" Reference Note
[
Definition:
]()
- [
7.3 – 12.1
]()12.1
– A library cache lock controls the concurrency between sessions accessing an object in the library cache by acquiring a lock on the object handle. A library cache lock may is be requested when locating an object in the library cache.
[
Individual Waits:
#
]()
- [
This is the address of the object handle in the library cache. It is specific to the local instance. The object handle address uniquely identifies the object in this instance at this time. The object handle is an address in the SGA and so can change over time if an object is flushed / purged from the cache and then reloaded later. > To be waiting for a lock means that there is a blocker with a higher or incompatible mode. We determine compatibility from the following matrix, where X represents an incompatibility.
Blocker
Waiter
KGLM0 KGLMN KGLMS KGLMX
KGLM0 - X X X
KGLMN - - X X
KGLMS - - - X
KGLMX - ? X X]()
- [
The address of the lock on the above handle. Multiple sessions can hold different locks on the same handle.
]()
- [ (Oracle 9.0 onwards)
P3 combines the lock request mode and object namespace in a single parameter as the value 100*Mode + Namespace. (> ) >
The lock request > is a value from 0 to 3:
Mode
KGLM0 0 no lock/pin held
KGLMN 1 null mode
KGLMS 2 share mode
KGLMX 3 exclusive modeThe > is a number that indicates the namespace of the object that the lock is being requested on. You can convert the namespace id to a descriptive name (as seen in V$LIBRARYCACHE, V$DB_OBJECT_CACHE etc..) using SQL like below: >
]() [
#
Waits up to 3 seconds ( 1 second if PMON) at a time for the LOCK to become available then polls to check it didnt miss being posted and waits again if required. The maximum wait time depends on the code requesting the lock.
#
You can use SQL like below as a SYSDBA user to find information about current blocking sessions, or see ]()Note:122793.1 >
[
Systemwide Waits:
]()> – [Run ADDM and implement any recommendations (see ]()Note:1680075.1 for details of how to run ADDM)
– Check for SQLs with a high version count and address those
eg: See the "SQL ordered by Version Count" section of AWR reports
– Check ASH or hanganalyze or similar data to check if the waits
are focused on specific objects or are more general
– See the documents in the Troubleshooting section below
[
Reducing Waits / Wait times:
– For issues with high version counts recode to use bind variables or use CURSOR_SHARING to help reduce contention
– For other causes the action depends on the object type and reason for the
contention
]()[
Troubleshooting
]()> [ See the following documents for help troubleshooting issues relating to high "library cache lock" waits ]()> Document:444560.1
[
Known Issues / Bugs:
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
]()> [
—
—
| | 5650841 | | Hang / deadlock from ANALYZE of cluster index
| | 24326444 | 12.1.0.2.181016, 18.1 | drop edition blocking scheduler create_job
| | 16232803 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | Library cache lock / mutex contention with fix 8508078 enabled
| | 16191248 | 12.1.0.1.1, 12.1.0.2, 12.2.0.1 | Hang from concurrent drop of on-commit materialized views or using DBMS_REDEFINITION
| | 16042673 | 11.2.0.3.BP25, 11.2.0.4.4, 11.2.0.4.BP08, 12.1.0.1.5, 12.1.0.2, 12.2.0.1 | Database hang when system trying to add interval partition to the table
| | 13884774 | 12.1.0.2, 12.2.0.1 | Deadlock from concurrent select/ALTER SUMMARY and ALTER TABLE if query_rewrite_enabled set to true/force – superseded
| | 12608451 | 11.2.0.3.BP24, 11.2.0.4.BP07, 12.1.0.2, 12.2.0.1 | Deadlock from concurrent DDL with "library cache lock" / "library cache load lock" wait – superseded
| | 14117976 | 11.2.0.3.BP14, 11.2.0.4, 12.1.0.1 | Database hangs when executing multiple MV DDL's in parallel
| | 13496395 | 11.2.0.4, 12.1.0.1 | Hang / deadlock involving ACCOUNT_STATUS object for concurrent LOGON and ALTER USER
| | 9075665 | 11.2.0.4, 12.1.0.1 | Hang from concurrent drop / create of materialized views
| | 24385983 | 12.1.0.2.180116, 12.2.0.1, 12.2.0.1.170418 | ORA-4020 or Hang waiting for Library Cache Lock with a Self Deadlock if Fix for Bug 12608451 is Present
| | 23507258 | 12.2.0.1 | "ORA-4021 timeout occurred" while running CREATE TRIGGER on Editioning view
| | 23003919 | 12.2.0.1 | Excessive 'library cache lock' & 'cursor: pin s wait on x' waits by Query with binds, PARALLEL hint and session parallel execution disabled
| | 21949743 | 12.2.0.1 | Drop materialized view execution hang after base table deleted
| | 21659726 | 12.1.0.2.161018OJVMPSU, 12.1.0.2.161018WINOJVMBP, 12.2.0.1 | Library cache lock waits for sys.jox_cbrls_lock object after upgrade to 12.1.0.2 in Java
| | 20413540 | 12.2.0.1 | Excessive executions of SQL frjd8zfy2jfdq
| | 19822816 | 12.2.0.1 | High parse time for SQL with PIVOT and binds (can block LGWR on "library cache lock")
| | 19790972 | 12.2.0.1 | "library cache lock" waits due to DBMS_STATS gather of stats for a subpartition
| | 19490852 | 12.2.0.1 | Excessive "library cache lock" waits for DML with PARALLEL hint when parallel DML is disabled
| | 19180394 | 12.2.0.1 | ADG: LGWR is blocked by the fg session waiting for 'standby query scn advance'
| | 18515268 | 11.2.0.4.BP20, 12.1.0.2, 12.2.0.1 | ORA-4020 in ADG Standby Database causing instance crash by LGWR
| | 17793460 | 12.1.0.2, 12.2.0.1 | DROP MATERIALIZED VIEW execution may deadlock with other processes
D | | 17018214 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | ORA-600 [krdrsb_end_qscn_2] ORA-4021 in Active Dataguard Standby Database with fix for bug 16717701 present – Instance may crash
| | 16864042 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | Unnecessary library cache operations for DDLs on partitions in exadata
D | | 16717701 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | Active Dataguard Hangs waiting for library cache lock on DBINSTANCE namespace with possible deadlock – Superseded
| | 16448569 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | PQ hang/deadlock possible – "cursor: pin S wait on X" waits
| | 16237313 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | Long Parse/Execution of dynamic sampling query made with UNION ALL
| | 15858022 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | Frequent invalidation of SQL Plan Baselines
| | 14380605 | 12.2.0.1 | ORA-904/mutex contention from recursive 'SELECT 1 FROM DUAL WHERE' done by "MERGE INTO"
| | 13622515 | 11.2.0.4, 12.1.0.2, 12.2.0.1 | library cache <-> row cache deadlock / hang when altering constraint with MV involved – superseded
| | 19867671 | 11.2.0.4 | "library cache lock" caused by wrong password login – superseded
| | 18141472 | | Session spin in/under kksObsoleteCursor can cause "library cache lock" waits and/or ORA-4021
D | | 18238463 | 12.1.0.1 | Slow performance during exchange partition with incremental statistics
| | 14356507 | 11.2.0.3.BP12, 11.2.0.4, 12.1.0.1 | Deadlock between partition maintenance and parallel query operations
| | 13916228 | 11.2.0.4, 12.1.0.1 | Enabling/disabling constraints makes DML timeout in RAC – superseded
| | 13887875 | 11.2.0.3.BP11, 11.2.0.4, 12.1.0.1 | Deadlock between parallel query and resource manager call
| | 13728397 | 11.2.0.4, 12.1.0.1 | Drop materialized view hangs
| | 13624984 | 11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 | IMPDP or IMP hangs on compilation of PLSQL containing a pipelined function
| | 13454210 | 11.2.0.2.BP17, 11.2.0.3.5, 11.2.0.3.BP04, 11.2.0.4, 12.1.0.1 | BUILD lock is not released on error ('library cache lock' waits)
| | 13387978 | 11.2.0.4, 12.1.0.1 | Sessions running TRUNCATE causing a deadlock, even if the constraints are disabled
| | 12702123 | 11.2.0.2.BP12, 11.2.0.3, 12.1.0.1 | LGWR of active data guard hang with "library cache lock" / lms0 spins
| | 12377691 | 11.2.0.3, 12.1.0.1 | hangs with 'library cache lock' and errors with on-commit materialized views
| | 12351027 | 11.2.0.4, 12.1.0.1 | Redefinition causing deadlock between "row cache lock" and "library cache lock"
| | 11693365 | 11.2.0.3, 12.1.0.1 | Concurrent Drop table and Select on Reference constraint table hangs (deadlock)
| | 10213073 | 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1 | CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects
| | 10126219 | 11.2.0.1.BP08, 11.2.0.2.BP02, 11.2.0.3, 12.1.0.1 | Undetected deadlock 'library cache lock'/'row cache lock' with concurrent DDLs on partition tables.
| | 10018789 | 11.2.0.1.BP07, 11.2.0.2.2, 11.2.0.2.BP01, 11.2.0.3, 12.1.0.1 | Spin in kgllock / DB hang with high library cache lock waits on ADG
| | 9952554 | 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1 | Undetected deadlock 'library cache lock'/'row cache lock' with a session modifying a constraint
| | 9694101 | 10.2.0.5.7, 11.2.0.2, 12.1.0.1 | Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects
| | 9675816 | 11.2.0.2, 12.1.0.1 | Self deadlock with 'library cache lock' waits / OERI:17059
| | 9454385 | 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1 | ORA-4020 may not be reported when expected
| | 8428523 | 11.2.0.2, 12.1.0.1 | Alter Table Rename causes wrong results/ora-8103/hangs on ADG Standby.
| | 16299727 | 11.2.0.4.BP14 | Process may spin in kglLock() when using Editions in ADG environment. Waits for library cache lock or ORA-4021 and/or ORA-1555
| | 13652757 | 11.2.0.4 | "library cache lock" waits from many concurrent DBMS_JOB.submit() operations if JOB$ is large
| | 12633340 | 11.2.0.2.6, 11.2.0.2.BP13, 11.2.0.3 | Heavy "library cache lock" and "library cache: mutex X" contention for a "$BUILD$.xx" lock
| | 12432089 | 11.2.0.3 | library cache lock/cursor: pin S wait on X with parallel partition stats gathering
| | 12326358 | 11.2.0.3 | Mutex contention from many concurrent "bad" SQLs that get parse errors
| | 17292239 | 11.2.0.4 | Active Dataguard Hangs waiting for library cache lock on DBINSTANCE. Tracking bug to deliver backports to remove fix for 11664426
| | 9327929 | 11.2.0.2 | ORA-600 [kglLockOwnersListDelete] / DIRECTORY object waits using EXTERNAL tables
| | 8441239 | 11.2.0.1 | Library cache lock waits if long running TRUNCATE in progress
| | 8348464 | 11.1.0.7.2, 11.2.0.1 | CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects – superseded
| | 7143299 | 10.2.0.5, 11.2.0.1 | OCI Logon is slow if the process is enabled for HA event notification
| | 6761624 | 10.2.0.5, 11.2.0.1 | ALTER INDEX .. SHRINK SPACE can deadlock with concurrent sessions
| | 6618312 | 11.1.0.7, 11.2.0.1 | Deadlock between ON COMMIT materialized view refresh and query rewrite (library cache pin V lock deadlock)
| | 6496514 | 10.2.0.4, 11.1.0.7, 11.2.0.1 | RAC deadlock between "libary cache lock" and TX enqueue
| | 6475688 | 11.1.0.7, 11.2.0.1 | Concurrent rewrite and on-commit refresh can deadlock (library cache pin <–> lock)
| | 6644122 | 10.2.0.5, 11.1.0.7 | ON COMMIT refresh deadlock (library cache pin V lock)
| | 5604698 | 10.2.0.4, 11.1.0.6 | Deadlock between 'library cache lock' and 'library cache pin' using replication
| | 6011045 | 10.2.0.5.5 | DBMS_STATS causes deadlock between 'cursor: pin S wait on X' and 'library cache lock'
| | 6051177 | 10.2.0.4.1, 10.2.0.5 | Hang / deadlock between coalesce and DBMS_STATS.gather_table_stats
| | 4275733 | 9.2.0.8, 10.1.0.5, 10.2.0.1 | Deadlock between library cache lock and row cache lock from concurrent rename partition
| | 4285240 | 9.2.0.8, 10.1.0.5, 10.2.0.1 | Cannot quiesce replication group with readonly JDBC connections
| | 4137000 | 10.1.0.5, 10.2.0.1 | Concurrent SPLIT PARTITION can deadlock / hang
| | 3852108 | 9.2.0.7, 10.1.0.4, 10.2.0.1 | Concurrent truncates on global temporary table cause library cache lock waits
| | 3605165 | 9.2.0.6, 10.1.0.4, 10.2.0.1 | Hang/deadlock between sessions concurrently loading a cursor with INVALID trigger
| | 3424721 | 9.2.0.6, 10.1.0.3, 10.2.0.1 | Hang/deadlock from ALTER INDEX REBUILD on partition with concurrent SQL
| | 3381218 | 9.2.0.6, 10.1.0.4, 10.2.0.1 | Deadlock involving 'library cache lock' X mode request
| | 3374640 | 10.1.0.3, 10.2.0.1 | Create local partitioned domain index can hang
| | 3312874 | 9.2.0.6, 10.1.0.3, 10.2.0.1 | USE_STORED_OUTLINES causes increased "library cache lock" requests in RAC
| | 2997330 | 9.2.0.6, 10.1.0.4, 10.2.0.1 | Hang on library cache pin/lock
| | 3288732 | 9.2.0.6 | AQ propagation can hold library cache locks longer than needed
]()> [
- '*'
indicates that an alert exists for that issue.
- '+'
indicates a particularly notable issue / bug.
]()> – [See ]()Note:1944526.1 for details of other symbols used