RE: INSERT statement hangs due to library cache lock

Mark’s note is the odds-on favorite to explain your lock.  

Now, what can you do about it?  

A few things come to mind (which I think I’ve tested in the past, but which you should re-test in your environment which is likely many versions since my tests.)  

1. Change the way you insert:
   1. Insert directly into the current partition or subpartition (If this would be the creation of an interval partition, you can do the first approximate blockfull via the table name so it still gets automagically created). If memory serves this makes the lock on the partition, and since the drops on different partitions you shouldn’t have a problem.
   2. Insert into a matching template table and use partition exchange. (Since you apparently only have local indexes, this is probably a very good option for you).
   3. Unless your inserts are very small, you probably do *NOT* want to switch to conventional inserts. (If you are already conventional and you’re getting the long duration exclusive lock, please let us know.)
2. Change the way you drop: Since you apparently only have local indexes, exchanging an empty partition in for the old one and then dropping the partition should decrease the duration of any locks to a couple of dictionary operations. Oh, and then you can relocate and compress the old partition to create keep forever images, back up, and/or destroy to your heart’s content without affecting locks on the production table. (And only affecting much at all in the case of the read load on the existing tablespaces’ file(s) and some cpu during the relocation and/or compress.)

On Wed, Sep 28, 2016 at 4:36 PM, Vadim Keylis <vkeylis2009_at_gmail.com> wrote:

Good afternoon. We have partition table that is partition by day with two local indexes. One index non unique the other is unique, but both indexes are local to a partition. We never dropped partitions since table was created in 2012. We were dropping all partition through 9/24. Looking at the Enterprise Manager Cloud Control we observed the spike in insert statements into that table. The spike was caused by library cache lock. None of the insert statements would go into partitions we were dropping. Will appreciate an advise why dropping partition would cause library cache lock on the table?  

Thanks so much in advance,

Vadim  

--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 01 2016 - 14:37:06 CEST