Cache Buffer Chains Demystified

You must have seen sessions waiting on the event “latch: cache buffers chains” from time to time. If you ever wondered what this means and how you can reduce time spent on it, read on. Here you will learn how buffer cache works, how Oracle multi-versioning works, how buffers are allocated and deallocated, what hash chain is and how buffers are linked to it, what the role of cache buffer chain latch is and why sessions wait on it, how to find the objects causing the contention and how to reduce the time spent on that event.

While exploring the reasons for the slowness in database sessions, you check the wait interface and see the following output:

 SQL> select state, event from v$session where sid = 123;

STATE   EVENT
 ------- ---------------------------
 WAITING latch: cache buffers chains

This event is more common, especially in applications that perform a scan of a few blocks of data. To resolve it, you should understand what the cache buffers chains latch is and why sessions have to wait on it. To understand that you must understand how the Oracle buffer cache works. We will explore these one by one, and close with the solution to reducing the cache buffers chains latch waits.

OOW14 Session: Cache Buffer Chains Latches Demystified

Thank you all those who came to attend my session "Demystifying Cache Buffer Chains Latches" at Oracle Open World 2014. Much appreciated. I hope you got something out your time investment.

I have also written a paper to accompany this session. The paper explains the concepts I presented in greater detail. You can download a zip file containing the slide deck, the paper and the scripts here. Please note: this is a zip file.

And, yes, here is the blog entry that talka about why you need 7X memory to completely fit your database in buffer cache http://arup.blogspot.com/2011/04/can-i-fit-80mb-database-completely-in.html

As always, I would love to hear about your thoughts on the presentation.

One down at #oow14; five more to go :)

Collaborate14 Session: Cache Buffer Chains Demystified

Thank you all for coming to my session Cache Buffer Chains Demystified at Collaborate 14, especially for sticking around for a geeky topic like this to the very end. Much appreciated.

I was not aware that I would not be allowed to use my laptop; so I couldn't show all the demos I so carefully prepared. Please download the scripts and execute them yourself.

As promised, here are the materials I used in the session

The Slide Deck
The Whitepaper
The Scripts (this is a zip file; so right click and Save As ...)

Needless to say, your comments and feedback will be highly appreciated. And, yes, please don't forget to do the evaluation on the Collab Mobile App.