Re: need help designing a heavily concurrent reporting engine
Date: Tue, 4 Sep 2012 18:22:13 -0700
Message-ID: <CAGXkmis_UHOvaGBKFsd-tEwydwEBqpxV63CV8bcbOgCdkQRh2w_at_mail.gmail.com>
Partition exchange requires a table lock and invalidates cursors as well. In a environment with a larger number of relatively short running queries, this can be problematic. It falls under, there can be too much of a good thing (meaning partition exchange frequency). On Tue, Sep 4, 2012 at 6:05 PM, Pakhutkin, Maxim (Max) < maxim.pakhutkin_at_windstream.com> wrote:
> I forgot to mention that we've tried removing data via the exchange
> partition method. I probably didn't mention it because the result was
> similar to the TRUNCATE partition approach.
>
> The problem is that we observed heavy library cache lock/latching waits
> during exchange partition operation. For example, 35 processes may be
> querying the partitioned table, while 5 may be doing exchange partition
> operation. To soft-parse the query, oracle needs a library cache latch on
> the object, I believe. If the object is being modified (via exchange
> partition), the queries have to wait (because they'd need to hard-parse the
> query if the object gets modified while they are waiting). When another
> exchange needs to happen, it will stack up in the queue waiting for the
> queries that are waiting for the first exchange operation, and so on.
>
> We are hoping not to have to stop queries while we reload data in the
> segments, but because of the library cache latching, only a handful of
> processes are able to continue while dozens more wait for latches to be
> released/acquired.
>
> --
Regards,
Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> |
linkedin <http://linkd.in/gregrahn>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 04 2012 - 20:22:13 CDT