Re: exchange partition waiting for library cache lock

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Tue, 12 Sep 2023 07:15:08 +0000
Message-ID: <DBAPR02MB6470560324507FC55076CA3AA1F1A_at_DBAPR02MB6470.eurprd02.prod.outlook.com>



How long are these queries tending to hard parse for?

Sent from my iPhone

On 12 Sep 2023, at 00:43, Ls Cheng <exriscer_at_gmail.com> wrote:


Hi

Looks like it was hard parsing, session 1186 was the session running the exchange partition and was blocked by session 325 who was running PQ against the table being exchanged

SAMPLE_TIME                 SESSION_ID EVENT                 SQL_ID        SQL_OPNAME   BLOCKING_SESSION BLOCKING_INST_ID IN_PARSE IN_HARD_PARSE IN_SQL_EXECUTION

--------------------------- ---------- --------------------- ------------- ------------ ---------------- ---------------- -------- ------------- ----------------
04-SEP-23 07.09.25.426 AM 325 9rkcnmvu2srm0 SELECT Y Y Y 04-SEP-23 07.09.25.426 AM 1186 library cache lock 71vzptnahb00h ALTER TABLE 325 4 N N Y

Tim, I will get the statement later today and share.

Thanks

On Mon, Sep 11, 2023 at 8:57 PM Dominic Brooks <dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com>> wrote: Are these blocking queries just executing or parsing? See the various IN% flags in ASH output.

Sent from my iPhone

On 11 Sep 2023, at 19:22, Ls Cheng <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> wrote:


Forgot to mention that the queries are all parallel queries

On Mon, Sep 11, 2023 at 8:13 PM Ls Cheng <exriscer_at_gmail.com<mailto: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 Received on Tue Sep 12 2023 - 09:15:08 CEST

Original text of this message