Re: Occasional insert slow down with CBC waits.

Hi Goti,

"latch: cache buffer chains" is an activity driven event. It means, that the same blocks receive to many requests for access simultaneously to serve them all.
We can think about possible reasons.
One thing would be to find out, over how many blocks your blob is spanning, average, maximum etc. Maybe when you insert one row, many blocks need to be touched.
Bigger blocks could help.
One other idea:
Why are you inserting row per row?  Can you bulk up and use the append_values hint?
More efficient than Do a straight insert is an Insert of an empty Blob  followed by an update.
The reason being that you skip a temporary copy of the blob. I explained that here: https://www.dbconcepts.com/en-how-to-copy-lobs/ You might also check the article of Marek Läll  I refer to in my blog.

Thanks

Lothar

Am 14.04.2022 um 17:40 schrieb Goti:
> 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

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