ALTER TABLE ... DROP or TRUNCATE PARTITION leading to *many* LOCK TABLE cursors
- From: "Tornblad, John" <JTornblad@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 Jan 2013 22:02:39 +0000
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
