//
you're reading...
Uncategorized

Cache Buffer Chain Latches

The “cache buffer chain” latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a freelist shortage on an index or table segment header.

v$latch_children

Usually latch contention for these buffer caches  is due to poor disk I/O configuration. Reducing contention with these latches involves tuning the logical I/O for the associated SQL statements as well as the disk subsystem.

Another factor for latch contention with buffers chain latches could possibly be hot block contention.

Oracle Metalink Note # 163424.1 has some useful tips on tuning and identifying hot blocks within the Oracle database environment.

Latch requests come in two flavors, willing to wait and no-wait modes. In willing to wait mode, when a latch cannot be acquired, the acquiring session will go into a spin mode, attempting to acquire the latch over and over a specified number of times. After the number of spins has reached a specific threshold, the session will sleep for a specified period of time, wake up and try the latch again. Spin mode is bad, but sleep mode is worse!

Very interesting articles :

http://www.dba-oracle.com/t_high_cache_buffer_chain_waits_contention.htm

http://www.toadworld.com/platforms/oracle/w/wiki/1302.how-to-identify-a-buffer-cache-chain-latch-problem.aspx

http://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/

https://sites.google.com/site/embtdbo/wait-event-documentation

http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block

http://www.pythian.com/news/1135/tuning-latch-contention-cache-buffers-chain-latches/

http://www.oaktable.net/content/latch-cache-buffer-chains-small-index-primary-key-caused-concurrent-batch-scripts-select-sta#comment-6

http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-latch-cache-buffers-chains

http://docs.oracle.com/database/121/TGDBA/pfgrf_instance_tune.htm#TGDBA94516

http://arup.blogspot.com/2014/11/cache-buffer-chains-demystified.html

Advertisements

Discussion

Comments are closed.

%d bloggers like this: