Re: Subpartition exchange and library cache lock wait time

From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 11 Feb 2011 13:30:21 -0800
Message-ID: <AANLkTikg7okwvif_ZwRWpabMnNLRmMKC8=z3JzXJt5JO_at_mail.gmail.com>

Partition exchange takes an X lock out on both tables and invalidates cursors on both of them. That does not change with the # of [sub]partitions. However, there is row cache overhead (for obj$) that does increase linearly.

On Fri, Feb 11, 2011 at 12:57 PM, <paul.baumgartel_at_ubs.com> wrote:
> Oracle11g 11.2.0.2. on Exadata.
>
> Many waits for library cache locks are observed during a run in which many
> processes exchange subpartitions in one particular table.
>
> The application ensures that two sessions will not try to exchange the same
> subpartition at once.  We understand that the waits for library cache locks
> are normal and expected.
>
> The table in question contains 90,000+ subpartitions.  The question has come
> up of whether the time that Oracle spends acquiring and releasing the
> library cache locks is in any way proportional to the number of
> subpartitions.
>
> Thoughts?

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 11 2011 - 15:30:21 CST