Re: Performance issue - Library cache lock

Hello,

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

Here’s below a link to an article I wrote to summarize a Disk file operations I/O
<https://hourim.wordpress.com/2020/12/21/disk-file-operations-i-o/> where the triplet (p1,p2,p3) equals(8,0,8)
https://hourim.wordpress.com/2020/12/21/disk-file-operations-i-o/ In my case, it turned to be an excessive amount of actions audited by the database where the destination of the audit was set at the os level (audit_trail = os) You can also, as suggested by Stefan Koehler <http://www.soocs.de/> in the comment part of the above blog post, use Tanel Poder psn using *syscall *and *filename *as input parameters to get, eventually, the exact type of filename and operation your Disk file operations I/O is referring to.

[oracle_at_host1 ~]$ sudo psn -G syscall,filename

 Linux Process Snapper v0.18 by Tanel Poder [https://0x.tools]

Sampling /proc/syscall, stat for 5 seconds... finished.

• Active Threads

  ---

 samples | avg_threads | comm | state | syscall | filename

      13 | 1.00 | (oracle_*_cd) | Running (ON CPU) | [running] |

      11 | 0.85 | (rcu_sched) | Running (ON CPU) | [running] |

       1 | 0.08 | (ohasd.bin) | Running (ON CPU) | [running] |  Best regards

Mohamed Houri

Le ven. 12 févr. 2021 à 16:37, Pap <oracle.developer35_at_gmail.com> a écrit :

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

>

>

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

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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