Re: latch: cache buffers chains
Date: Sun, 3 Oct 2021 11:26:35 -0400
Message-ID: <c151b306-0252-8495-2645-3696c178936c_at_gmail.com>
On 10/3/21 08:55, Laurentiu Oprea wrote:
> My particular issue looks to be related with the oracle execution
> engine and not necessary with optimizer. I was trying different hints
> just to see if I can improve the execution.
>
Cache buffers chain latch protects database buffer structures in SGA.
The wait you're experiencing means that several sessions are operating
on the same table and causing what is known as "hot blocks". You can
identify hot blocks, here is a good recipe how to do it:
http://ksun-oracle.blogspot.com/2014/03/hot-block-identification.html
As per parallel sessions and GTT, you can disable parallel execution on the table using ALTER TABLE NOPARALLEL or, if you don't want to do that, you can do alter session disable parallel query and voila, your queries will not be executed in parallel.
Another, more complex, trick is to switch to threaded executions. Latches are much cheaper in threaded environments but there are some downsides: dbstart and dbshut scripts will no longer work, you cannot use "/ as sysdba" and monitoring looks entirely different.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 03 2021 - 17:26:35 CEST