RE: INSERT statement hangs due to library cache lock
From: Randolf Geist <info_at_www.sqltools-plusplus.org>
Date: Wed, 5 Oct 2016 01:31:30 +0200
Message-ID: <cef8d2a5-983b-593d-cc58-c4be0475b7ab_at_www.sqltools-plusplus.org>
It's probably worth to point out that "enqueue" (DML) locks are different from locks on Library Cache level, so changing the INSERT to APPEND or conventional has nothing to do with what is happening here, which is the collision of concurrent parse and DDL activity on Library Cache level - DDL (which includes gathering stats) takes an exclusive lock on the Library Cache global object, and hence collides with any other activity on Library Cache level that also attempts to take a lock which is typically parsing. It's also important to understand that the granularity on Library Cache level is global in this case, so again it doesn't matter if the INSERT specified a particular partition or not for inserting. This would all be important if it was about DML locks on partition / global level, but this is not the case here.
Date: Wed, 5 Oct 2016 01:31:30 +0200
Message-ID: <cef8d2a5-983b-593d-cc58-c4be0475b7ab_at_www.sqltools-plusplus.org>
It's probably worth to point out that "enqueue" (DML) locks are different from locks on Library Cache level, so changing the INSERT to APPEND or conventional has nothing to do with what is happening here, which is the collision of concurrent parse and DDL activity on Library Cache level - DDL (which includes gathering stats) takes an exclusive lock on the Library Cache global object, and hence collides with any other activity on Library Cache level that also attempts to take a lock which is typically parsing. It's also important to understand that the granularity on Library Cache level is global in this case, so again it doesn't matter if the INSERT specified a particular partition or not for inserting. This would all be important if it was about DML locks on partition / global level, but this is not the case here.
So if partition level DDL is applied to a partitioned table any SQL statement that references the table (no matter on partition (and what partition) or global level) will be affected / invalidated immediately and collide with ongoing DDL when parsing - in principle such kind of concurrent activity of parsing and DDL is something that the Oracle engine at present by design doesn't cope very well with (not sure though if that has been improved in 12.1 or 12.2).
Randolf
> Mark’s note is the odds-on favorite to explain your lock.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 05 2016 - 01:31:30 CEST