Re: Occasional insert slow down with CBC waits.

Hi,

/Could that also be a contributing factor for the slowness that we are observing? /
Yes, of course. You need more blocks to store 200 GB than to store 100 GM. So that would mean more activity and consequently more time. /What could be the possible reason for this one database alone having the large LOB size? /
I have no idea, but maybe it helps if you check the content of some examples.
What comes to my mind is fragmentation, but that would not means different length.
Unless maybe the 200 GB are spread of a lot more blocks. There must be a overhead per block used. But twice as long seems unlikely... BTW: When dealing with LOBS it is sometimes beneficial to have enough free blocks.

Thanks

Lothar

Am 16.04.2022 um 13:03 schrieb Goti:
> 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