My Oracle Support Banner

Library Cache Lock Held For Long Time For Queries Involving Too Many Binds (Doc ID 1315929.1)

Last updated on MARCH 18, 2022

Applies to:

Oracle Database - Personal Edition - Version and later
Oracle Database - Standard Edition - Version and later
Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.


Generally, the Library cache lock is held up during parsing of the sql and the lock is held up on the object in shared mode and released after the parse. This is the expected behaviour.
Mostly, The Library cache lock would not be held up in the execution phase.
Example: A query with joins going for merge join cartesian taking long time in execution would not block others with Library cache lock. Because Library cache lock would have been released after the parse.
This is the behaviour of Library cache lock.



Should the SQL has bind variables specified in the where clause, then the Library cache lock is held up even beyond the parse phase.Because, the bind replacement happens after the parse phase and before the execute phase. There is a intermediate stage called "BIND" which comes after the parse and thus Library cache lock has to be held even after the parse and till all the binds have been replaced.

Should the SQL has too many bind variables in the where clause or huge inlist with bind variables, then the Library cache lock would get held up for long time till the bind replacement finishes. In those circumstances, Library cache lock will be seen till execute phase.



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.