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:
a. 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.
b. 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).
c. 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.)
Please let us know how that pans out for you, or if you discover your
difficulty is something else.
Thanks,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mark J. Bobak
Sent: Wednesday, September 28, 2016 4:41 PM
To: Vadim Keylis
Cc: Oracle-l
Subject: Re: INSERT statement hangs due to library cache lock
Hi Vadim,
Are your inserts doing 'INSERT /*+ APPEND */'?
Append implies an exclusive, rather than shared lock.
-Mark
On Wed, Sep 28, 2016 at 4:36 PM, Vadim Keylis <vkeylis2009@xxxxxxxxx> 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
