Re: Performance issue - Library cache lock

Seems to be many of the SYS/Background processes are getting stuck with wait "Disk file operations I/O" now. Below is one of the sql is also stuck on "Disk file operations I/O" now. Wondering what the reason is? The P1,p2,p3 in v$session for the sql suffering from wait "Disk file operations I/O" showing 8,0,8 respectively. And seems to be its miscellaneous I/O. Anybody has seen such an issue?

SELECT LAST_ARCHIVE_TIMESTAMP FROM SYS.DAM_LAST_ARCH_TS$ WHERE RAC_INSTANCE# = 1 AND AUDIT_TRAIL_TYPE# = 4 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