Re: Occasional insert slow down with CBC waits.

HI Jonathan / All,

We have noticed that the DB where we are having this issue, the LOB segment size is more than 200GB. This database is part of Goldegate replication, and the replicated database also has the same table and LOB segment where the LOB segment size is less than 100GB. The record count for both the tables match and they are in sync. The retention settings for the securefiles on both the database is AUTO and the UNDO retention for both the DB is also set to 3 hours. What could be the possible reason for this one database alone having the large LOB size? Could that also be a contributing factor for the slowness that we are observing? Also I have suggested the application team have their inserts moving to one instance of RAC node to avoid the cross chats between the RAC instances.

Thanks,

Goti

On Thu, Apr 14, 2022 at 10:23 PM Goti <aryan.goti_at_gmail.com> wrote:

>
> Yes Jonathan , you are right. This is a RAC database.
>
> On Thu, 14 Apr 2022 at 10:19 PM, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> One of the buffers has state PI, which means you must be running RAC, and
>> at some recent point in the past another instance has been calling for this
>> block. (If you're not running RAC then seeing a PI is probable the problem
>> you need to address first).
>>
>> It's possible that the CBC latch wait is a side effect of having more
>> than one instance inserting into the same LOB segment at the same time.
>> Waiting for a latch, though, often means that your CPU is overloaded - so
>> perhaps the LMS processes are getting too busy and putting pressure on the
>> system when the rate of insert gets too high.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>> On Thu, 14 Apr 2022 at 16:41, Goti <aryan.goti_at_gmail.com> wrote:
>>
>>> Hi,
>>>
>>> ENV: Oracle 12.1.0.2 on RHEL 7.
>>>
>>> There is one single pconventional insert which slows down occasionally.
>>> The wait event during the slow down is "latch: cache buffer chains". Using
>>> Tanel Poder's latchprofx.sql , it was identified that the wait event was
>>> mainly for the LOB segment with 1 level bmb as the block class. Details are
>>> there in the gists below. Is there any way to identify what is causing this
>>> and how the issue can be fixed?
>>>
>>> https://gist.github.com/aryangoti/06bcd2ead5efbea910afa4e7e5af5e4a
>>>
>>> https://gist.github.com/aryangoti/7d462225877c648f8a88b669b4b68b08
>>>
>>>
>>> Thanks,
>>>
>>> Goti
>>>
>> --
> Thanks,
>
> Goti
>

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