Re: ALTER TABLE ... DROP or TRUNCATE PARTITION leading to *many* LOCK TABLE cursors
Date: Thu, 31 Jan 2013 21:41:50 -0600
Message-ID: <CAPJkHXHO5-K_TuyvVOZ+kTzQbPiFvyq0XXEq0dSmDjrNpAFg=g_at_mail.gmail.com>
John,
Check Patch 14600407. It might or might not help in your case, but I think it's worth to take a look. Oracle created this patch for us, we were having a lot of issues on a couple of tables w/ 100,000+ subpartitions when doing subpartition adds/exchanges. The patch definitely helps a lot in our case. Thanks,
Gunawan
On Thu, Jan 31, 2013 at 4:02 PM, Tornblad, John <JTornblad_at_emdeon.com>wrote:
> 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
>
>
>
-- Only the wisest and the stupidest of men never change - Confucius -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 01 2013 - 04:41:50 CET