Re: exchange partition waiting for library cache lock

Hi

I am querying historical ASH so the interval is 10 seconds, I see a different sql_id every 10 seconds so I guess they should take less than 10 seconds.

The concurrency is high, quite a few reports are running at that time and all are querying against the partition being exchanged.

Thanks

On Tue, Sep 12, 2023 at 9:15 AM Dominic Brooks <dombrooks_at_hotmail.com> wrote:

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