Re: Occasional insert slow down with CBC waits.

Very hard to comment - 12.1.0.2 is an old version of Oracle and there are likely to be segeval bugs mixing LOBs (whether basicfile or securefile) and ASSM and RAC which have probably been ironed out in newer versions. I'd search MOS for key combinations like:
LOB RAC
LOB ASSM 12.1.0.2
I'd also look for the "bugs fixed in" documents for version 19c because that might mention a fix to something that looks related.

I don't have any time to play around with it at present, but I'm wondering if the size variation is due to concurrency and the problems of timing when concurrent updates and deletes cause bitmap changes. There were several of those in even simple cases in older versions of Oracle. Is the number of processes insert on production significantly larger than the number of goldengate processes transferring the inserts to the secondary database; is there, perhap, just one GG process handling the LOB inserts. Do you insert, update, and delete; or insert/delete in the production system.

Unfortunately 12.1.0.2 doesn't have all the statistics about ASSM you get in 12.2.0.1 - which give plenty of clues about "lost space", "bad space hints", "aborted allocations" etc.

It's just occurred to me that there used to be a (possibly hidden) parameter setting that was supposed to limit the side-effects of concurrency on LOB activity - I don't have a note of it to hand but perhaps someone else will remember it and have a reference to the relevant MOS note.

Regards
Jonathan Lewis

On Sat, 16 Apr 2022 at 12:03, Goti <aryan.goti_at_gmail.com> wrote:

> 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