Re: Occasional insert slow down with CBC waits.

Thanks Lothar and Jonathan.

Hi Jonathan,

We perform just INSERTS to the table and LOBS. There are no DELETES and UPDATES. You are correct from the GG perspective, there is only one process that is handling the INSERTS. After checking the SQL response time from AWR, I did notice that we had increased response time for the insert statement post Jan'21 PSU patching for this database alone. This PSU patching was only done on the problematic database. A quick search on the MOS shows the below, which matches what we are facing.

Bug: 26729494
<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1924126.1&id=26729494.8> :
Securefile LOB size grows significantly after applying 12.1.0.2.170418 DBBP

Also, reg: "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" Are you referring to "_securefiles_concurrency_estimate" parameter ?

Thanks again for your time and help!

Thanks,

Goti

On Sat, Apr 16, 2022 at 5:52 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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