ALTER TABLE ... DROP or TRUNCATE PARTITION leading to *many* LOCK TABLE cursors
Date: Thu, 31 Jan 2013 22:02:39 +0000
Message-ID: <8A354F8DD7A2CF489BC375F536A4BF45132C96_at_zbnaaeex012.na.webmd.net>
We have an application with several hundred tables that are partitioned by date (and they are DAY-sized partitions over years = a LOT of partitions). When we want to do various types of partition maintenance operations (TRUNCATE, DROP, SPLIT) it seems our shared pool is being abused by some recursive SQL that is generated during a PMOP:
For every:
ALTER TABLE ... [DROP, TRUNCATE, etc.] PARTITION ...
We see one of these get executed:
LOCK TABLE ... PARTITION (...) IN EXCLUSIVE MODE NOWAIT
If we want to TRUNCATE, DROP, MOVE or SPLIT some partitions, we have thousands of these LOCK TABLE cursors piling up [no ALTER TABLE cursors though... those seem to vanish into the ether]. "Cursor leak"? It's so bad we've eventually received errors (ORA-04031: unable to allocate 32 bytes of shared memory) regarding being unable to expand the shared pool any further.
I understand the need for the LOCK TABLE statements, I just don't understand why they are piling up and flooding the shared pool. Their status is "INVALID_UNAUTH" which I suppose means that they are no longer valid, but they seem to be hanging around, not being reaped or purged and if we execute too many PMOPs (or perhaps too quickly?), we have problems. This appears to be adding up to many GBs of memory allocated to the shared pool, and we cannot sustain that for very long.
Wondering how to deal with "large" numbers of partitions, when you need to perform a large number of PMOPs.
Observed in 11.2.0.1, 11.2.0.3.
-john
</pre>This message is confidential, intended only for the named
recipient(s) and may contain information that is privileged or
exempt from disclosure under applicable law. If you are not
the intended recipient(s), you are notified that the
dissemination, distribution, or copying of this message is
strictly prohibited. If you receive this message in error or
are not the named recipient(s), please notify the sender by
return email and delete this message. Thank you.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 31 2013 - 23:02:39 CET