Re: need help designing a heavily concurrent reporting engine
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: maxim.pakhutkin@xxxxxxxxxxxxxx
- Date: Tue, 4 Sep 2012 18:22:13 -0700
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@xxxxxxxxxxxxxx> 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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
