Latch: Cache Buffers Chains contention

problem: query hang on wait event "Latch: Cache Buffers Chains"

cause: one of the cause if the index root block access contention in nest loop.

solution:
it might be resolved by create a new index.


reference:
http://www.oaktable.net/content/oracle-waits-latch-cache-buffer-chains
http://blog.tanelpoder.com/2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/
http://blog.tanelpoder.com/2008/07/23/advanced-oracle-troubleshooting-guide-part-8-even-more-detailed-latch-troubleshooting-using-latchprofx/
http://blog.tanelpoder.com/2009/03/20/another-latchprofx-use-case/