NYOUG Session: Latches Demystified

Thank you all those who came to attend my session on demystifying latches at New York Oracle Users Group in Manhattan. I hope you found the session useful and enjoyable.





In this session, you learned:


What are latches – the purpose
Buffer cache latches
Shared pool latches
Identifying latch waits


Here is the slide deck and the scripts to reproduce the demos I showed as well as those I couldn't.


As usual, I will highly appreciate hearing from you how you liked it, or didn't. Please feel free to comment here or, contact me directly.


Email: arup@proligence.com
Twitter: @ArupNanda
Facebook: ArupKNanda
Google+ ArupNanda

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: SQL Tuning Without Trying

Many thanks to all those who attended my session "SQL Tuning without Trying" at Oracle Open World 2014 #oow14. I hope you found the session useful.

If you want to download the slide deck, here it is. As always, I would really, really like to hear from you.

Collaborate 2012 Sessions and Select Article

Thank you all who came to my sessions at #IOUG Collaborate 2012 #C12LV on April 22-24 in Las Vegas. I had four full sessions, two panels and one bootcamp. Quite a busy schedule, as you can see. I also worked on some urgent performance issues at work during the week.

You can download the the slides and scripts here. They are available from the IOUG site but I thought I would put them for download here as well.

• Rise of the Machines slides
• Secure Your Database in a Single Day slides scripts
• Beginning Oracle Performance Tuning slides scripts
• How Oracle Locking Works slides

My article Cache Fusion Demystified on SELECT Journal won the Editor's Choice award for 2011. This article is available for free download here.

I hope you like them. As always, feedback - good, bad and something in between - will be appreciated.

Update as of April 29th, 2012

I usually add more stuff to my slides after the session is over. This may result from a direct attendee feedback that some content was not clear, inadequate, misleading or even incorrect. This time is no exception. I added some more content to make the concepts clearer. Also many thanks to Charles Hooper for pointing out an Oracle documentation bug which makes two scripts in my session incorrect. I have since corrected the slides and re-uploaded. Please redownload the slides for How Oracle Locking Works and specifically look at the slides 14, 18, 24, 25 and 26. Also please download the scripts for Beginning Oracle Performance Tuning. More specifically, the script lock1.sql has changed.