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@xxxxxxxxx> 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
