Re: Performance issue - Library cache lock

From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 12 Feb 2021 20:18:53 +0530
Message-ID: <CAEjw_fg595H15BtD8bfjHNZseGQ=8nq3nH_7D64bzpXE0td=jA_at_mail.gmail.com>

Thank You Shane.

This is version 12.1.0.2.0 of Oracle Exadata X3 machine. And it's a two node RAC.

We are now seeing this node(on which the data pump process was running and we killed) is showing another sql spending time on "reliable message wait". But Node-1 shows lots of "disk file operation I/O" wait events which are going beyond the limit of total cpu core( which is ~20) as it's showing up in OEM. And we have many application processes just failed with error "failed: ORA-02002: error while writing to audit trail ORA-09925: Unable to create audit trail file". Can this all be related ?

DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE :=

:mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) :=
:job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30)
:= :job_owner; job_start TIMESTAMP WITH TIME ZONE :=
:job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE :=
:job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE :=
:window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id

VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE); END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; On Fri, Feb 12, 2021 at 7:31 PM Shane Borden <sborden76_at_yahoo.com> wrote:

> What version is this? Lots of bugs with datapump in 12.1 and 12.2. Look
> at MOS for any patches.
>
> Shane Borden
> sborden76_at_yahoo.com
> Sent from my iPhone
>
> On Feb 12, 2021, at 7:31 AM, Pap <oracle.developer35_at_gmail.com> wrote:
>
> 
>
> We have stream_pool_size set as ~256MB.
>
> We killed the specific session and now one of the other sessions which
> was showing "enq:TQ DDL contention" just before entering into "reliable
> message wait" and executing below sql. What we are wondering about is that
> the export used to run daily without any issue , so why is it experiencing
> such an odd wait today and making the database flooded with concurrency?
>
> BEGIN sys.kupc$que_int.create_queues(:1, :2, :3, :4, :5); END;
>
> On Fri, Feb 12, 2021 at 5:25 PM Shane Borden <sborden76_at_yahoo.com> wrote:
>
>> This is some sort of a datapump job going on. Do you have enough
>> streams_pool configured?
>>
>> Shane Borden
>> sborden76_at_yahoo.com
>> Sent from my iPhone
>>
>> On Feb 12, 2021, at 5:00 AM, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>> 
>>
>> Hello All,
>>
>> We are seeing one of our database nodes flooding with "concurrency" wait
>> i.e "library cache lock" and when going back to the start of the wait in
>> ASH we are seeing the blocking session actually seems to be from one of the
>> export sessions and it's from SYS too. The two sqls which were captured in
>> ASH from that session are below. The second one is the one spending time on
>> an event "reliable message" with current_obj# showing s '-1' and blocking
>> others now. When we see the start of the issue, that session was showing
>> current_obj# as index "AQ$_PROPAGATION_STATUS_PRIMARY" for quite a long
>> time. Are there any bugs associated with such an issue? This table seems to
>> be streams related but we don't use streams in this database, so not sure
>> from where it's popping up.
>>
>> BEGIN
>>
>> SYS.KUPM$MCP.MAIN('SYS_EXPORT_TABLE_02', 'SYS', 0, 0);
>>
>> END;
>>
>> BEGIN sys.kupc$que_int.delete_queues(:1, :2); END;
>>
>>
>>

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

Received on Fri Feb 12 2021 - 15:48:53 CET