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
To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

ref from : Page 10-34 of Oracle Database Performance Tuning Guide 12C

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s