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

Last updated on MAY 19, 2017

Applies to:

Oracle Database - Personal Edition - Version 9.2.0.1 and later
Oracle Database - Standard Edition - Version 9.2.0.1 and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

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.

 

Changes

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.

 

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms