Re: exchange partition waiting for library cache lock

Hi

This a simple range partitioned heap table, the data types used are normal varchar2, number, float, date, timestamp and char. I need to check about incremental stats because I saw some MOS note mentioning some issues with exchange partition.

From ASH I saw that the blocking sessions were running queries against the partitioned table being exchanged.

Thanks

On Tue, Sep 12, 2023 at 11:09 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Are the exchanges with a simple partitioned table or a composite
> partitioned table. Can multiple exchanges be executing at the same time.
>
> If with a composite partitioned table are you exchanging a single
> sub-partition, or exchanging and entire partition with a simple partitioned
> table. There may be different effects depending on the nature of the
> exchange. (As well as the options that Dominic suggested).
>
> Further - is the table a heap table or an IOT; does it hold LOBs (or
> extended varchar2() or other complex data types.
>
> How are statistics defined on the table - are you using incremental stats,
> are you using auto_sample_size and the "approximate NDV" mechanisms.
>
>
> Exchanging partitions can trigger stats collection activity, and stats
> collection can cause cursor invalidation - even the exchange is likely to
> cause (some) cursor invalidation. This would be a source of library cache
> latch activity; and concurrent exchanges might end up blocking each other
> if a query triggered by one exchange was invalidated by the action of the
> other exchange.
>
> It can be very hard to determine the cause of such anomalies unless your
> tests operate at the right volume of data with the correct degree of
> concurrency, as it may need very precise actions that need to occur within
> a window of 2 or 3 seconds for the problem to appear and test systems with
> small amounts of data (or small numbers of partitions) might never open a
> window for long enough.
>
> As a matter of course, I would wrap each "exchange" command with a few
> lines of code to enable SQL trace with wait stats with a meaningful and
> unique tracefile_identifier to leave a diagnostic trail that could be used
> whenever the problem appears.
>
> After the event (and without the diagnostic trail) you could try searching
> v$sql for any of the blocking statements, and you could generate a trace
> file for one exchange to see if any of the blocking statements look like
> versions of the recursive SQL executed by an exchange. That might give you
> some idea of how to proceed investigating the problem.
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Mon, 11 Sept 2023 at 19:14, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> Hi
>>
>> Running 19.15.
>>
>> I have some batch processes which run exchange partition commands then
>> run some ETL, sometimes the exchange partition takes minutes instead of
>> seconds and the ALTER is waiting for the library cache lock. From ASH we
>> can see that the blocking sessions are running queries against the
>> partition. I tried to reproduce the issue but in my tests the exchange
>> partition runs successfully and there is no library cache lock while the
>> table is being queried.
>>
>> So I wonder what is exactly causing library cache lock to the ALTER TABLE
>> exchange partition?
>>
>> Thanks
>>
>>
>>

libt.tld_type is 1, lib_t.tld_source_file is '/usr/local/lib/aarch64-linux-gnu/perl/5.30.0/auto/share/dist/Mail-DMARC-opendmarc/effective_tld_names.dat'

--
http://www.freelists.org/webpage/oracle-l