Watch How To Cause Oracle Free Buffer Waits

Posted on 20-Jan-2016 by Craig Shallahamer, craig@orapub.com

Oracle free buffer waits is one of the most feared of all Oracle wait events. It requires just the right workload mix and configuration to appear. When it does appear, it affects the entire application.

An interesting way to learn how to eliminate free buffer waits is to try and CAUSE them. Yes, you read correctly! Try causing free buffer waits and you will learn how to eliminate free buffer waits. How to do this, including a video of me doing this, is what this article is all about.

What Is A Free Buffer Wait?

Supposed an Oracle foreground process must find a replaceable buffer in the buffer cache, but it's taking "too long." When this occurs, the foreground process will stop trying to find a free buffer and yell, "Free Buffer Wait!" It will wait around 10ms then try again.

Here's an example with a little more detail. Suppose a foreground process needs to access a data block. It references the cache buffer chain structure and discovers the block is not in the buffer cache. Therefore, it must retrieve the block from an Oracle database file, which of course causes a physical read.

However, before the foreground process does the physical IO read, it must first find a suitable buffer in the buffer cache to replace with the block to be read from disk. The suitable buffer needs to be a non-popular free buffer.

So, the foreground process will search its associated least recently used (LRU) chain for a non-popular free buffer. If during this search it takes "too long," it will stop searching, yell "free buffer wait," wait for around 10ms, and restart its search once again.

Obviously, there is a lot more detail. And, the details are very interesting from both an Oracle internals and performance tuning perspective. But, all the details are not the objective of this post. However, detailing some of the causes for a free buffer wait is the objective of this post!

What Causes A Free Buffer Wait?

What is so fascinating about free buffer waits, is that it takes a kind of perfect storm to occur. It's like a stool with three legs. Without all three legs, the stool will not stand.

So, what are these three legs? Read on!

Seeing is believing, right? Then watch on!

Demonstrate What Causes A Free Buffer Wait

In this video, I'm going to cause a free buffer wait. How do I do it? It's actually pretty simple. I create a very small buffer cache, create a table that will not fit within the buffer cache, run SQL that touch so many table blocks that physical reads are required and then I add into the mix a DML statement. That is enough to set up the free buffer wait "three legged stool." Take a look!

You just had to ask, didn't you!

How To Eliminate Free Buffer Waits

The focus of this article is how to cause free buffer waits, not eliminate them. However, I do have a number of "solution focused" resources any DBA can immediately download for free and others that are for OraPub members only. Just do a search at www.orapub.com and you can see them all.

The general strategy is to focus on "kicking" one of the three stool legs... and watch it fall down! For example, identify SQL that is doing lots of block reads (physical IO) and tune it. This will reduce the need for buffer replacement, which will then reduce the likelihood of a foreground process not finding a free buffer quick enough.

All the best in your Oracle performance tuning work!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Creating Realistic User Think Times For Research Projects Oracle Database Hard Parsing is Bad! ...But How Bad? Four Options For Oracle DBA Tuning Training