RE: Performance issue - Library cache lock

how many files are in the destination directory? It shouldn’t be a problem up to an obscene number, but some folks never clean that out. I recommend creating subdirectories (date named) and moving them down so the files don’t actually have to move, just the directory entries.  

good luck.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Friday, February 12, 2021 10:37 AM To: Shane Borden
Cc: Oracle L
Subject: 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