Re: latch: cache buffers chains
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 3 Oct 2021 11:26:35 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
