Watch How To Cause Oracle Free Buffer Waits
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!
- First, there has got to be the need for buffer replacement. Without the need for buffer replacement, the LRU will not be scanned. And if the LRU is not scanned, there can't be a free buffer wait! As mentioned above, buffer replacement is required when a foreground process reads a block from disk and needs a place to put it in the buffer cache. Also, if a consistent read (CR) buffer must be created to have a copy of the buffer back in time, a buffer will also need to be replaced by the CU buffer.
- Second, there will be a shortage of non-popular free buffers. This shortage is what frustrates the foreground process until is gives up looking and yells, "free buffer wait." DML is the easiest way to cause a shortage of free buffers... because DML changes, that is, "dirties" free buffers!
- Third, it's the database writer's (DBWRs) responsibility to move non-popular dirty buffers from an LRU into a dirty list, so it then write them to disk (making them free again). There will be a point where the DML activity is so intense, even with a super fast IO subsystem, the DBWR simply will not be able to pull those non-popular dirty buffers out of an LRU and onto its dirty list and then write them to disk. In short, the database writer is falling behind.
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.
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
 |
 |
 |
|||
| 11 Ways To Get Your Oracle Database Conference Abstract Accepted | Which Is Better; Time Model Or ASH Data? | IOUG 2016 Is Different: Here's How To Get Your Abstract Accepted | |||
