Oracle Database Buffer Cache Visualization And Tool
Oracle's buffer cache is fascinating. And monitoring what's going on inside can be mesmerizing. But trying to convey something so complex using numbers can be, well… complex. So a common technique I use with my clients and students is to use simple graphics and to role play. But lately I've gone a step further and created a tool to visualize the buffer cache.
You can download my Buffer Cache Visualization tool and use it for free. It is based on Mathematica from Wolfram, but you only need their free player to use my tool.
Note: Remember this is a model, an abstraction, a learning tool used to focus on specifics aspects of interest. Abstractions have been made and many of them are not detailed in this blog. In most cases, the models you create using the visualization tool are massively smaller than any real Oracle system. And in fact, the Oracle instance would probably not even start! But we can learn a lot by removing much of the complexity, abstract, and focus on the area of interest.
If you spend some time experimenting with the Buffer Cache Visualization tool, I can guarantee you'll achieve a much better understanding of the buffer cache and will visualize how various performance killers manifest. Plus it's a lot of fun!
There are a number of controls/options you can apply to the visualization. There are technical controls for the number of buffer headers, cache buffer chains, least recently used lists, the percentage of dirty buffers, and if cloning has manifest. There are visual controls that allow you to show only the least recently used lists, the write lists, or the cache buffer chains. Or you can see all the lists together or if you want add in all the buffer header details as well. Because the visualization can become very crowded, I also included options to only show points for the buffer headers, arrows for the links, or you can see the full nodes. There is even an option to change the visual orientation from top to bottom, bottom to top, center, left to right, and right to left. You can even alter the size of the buffer header coloring to make it easier to see. And finally, to allow you to exactly duplicate the structure you create, you can set the random number used to create the visualization. As you can see, you'll be able to create an infinite number of unique visualizations enabling you to find just the right one for your purposes.
Below is a screen shot of the version 1d controls.
Buffer Cache Introduction
The buffer cache only contains the buffers. The buffers contain the block information and have an associated block residing in a database file. There is another structure call a buffer header that is at the center of the list management. Each buffer has an associated buffer header and all the buffer headers reside in the shared pool, not the buffer cache. While the buffers are an image of the blocks (of course when they are dirty there is a difference), but the buffer headers are not images.
The Buffer Header
The purpose of the buffer headers are to efficiently map the relationships between the buffers, the actual data blocks, the LRUs, the cache buffer chains, and the write lists. Plus each buffer header contains other control information, such as the last time the block was read from disk. So the buffer header is actually at the center of buffer cache management, even though it physically resides in the shared pool. Using OraPub's buffer cache visualization tool, you can get a close look at a buffer header by setting the number of buffer headers, cache buffer chains, and least recently used lists to only one, then choosing the node option. Here is an example:
Above you'll see that buffer header 101 (BH 101) is connected with multiple lists. Looking closely you'll see a to/from pointer with the actual buffer (BUF 101), next/prev pointers related to the cache buffer chain list (CBC 1B and CBC 1E), next/prev pointers to either an LRU or a write list (W 1B and W 1E), and a pointer to block 101 (BLK 101) on disk.
You can learn quite a bit by focusing on a buffer header without it being connected to other buffer headers via the various lists. But let's shift our focus on how the buffer headers are related to the cache buffer chains.
The Cache Buffer Chain
Oracle uses the cache buffer chains to locate a buffer in memory, that is where it resides in the buffer cache. Without getting into the details, a hashing algorithm is used to direct the searching process to a specific cache buffer chain. If the buffer's associated buffer header is on the given chain then the server process knows the buffer is indeed residing in the buffer cache. If the buffer header is not found on the chain, then the server process knows the buffer is not in the buffer cache and must make a call to the IO subsystem to retrieve the buffer. Below is an example showing four cache buffer chains: two chains containing all eight buffer headers and the remaining two cache buffer chains containing no buffer headers.
To create the above image using the visualization tool, set: Buffer Headers to 8, CBC Buckets to 4, LRUs to 1, Dirty % to 10, Cloning to No, Focus to CBC, Visualization to Nodes, Orientation to Center, Circle Size to 0.06, and the Random Seed to 1000.
While the picture above contains eight buffer headers linked to a possible four cache buffer chains. In real Oracle systems, there will be thousands of buffer headers and potentially over a million cache buffer chains. So if you tried to physically create what you see above, the Oracle instance would not start! Remember this is a model, an abstraction, a learning tool by focusing on specific aspects of interest.
Referencing the image above, if I was a server process and needed to access buffer 106, I would hash to cache buffer chain 2 (CBC 2B) looking for the buffer header 106... and I would find it. Since the buffer header contains a link to the physical buffer in the buffer cache, I would now know where to find it!
The LRU and Write Lists
The least recently used lists (LRUs) are a dual purpose list. They are used to help keep popular buffers in the buffer cache, to help server processes quickly find a non-popular free buffer, and for database writers to find non-popular dirty buffers. Each buffer header will be on either an LRU or a write list, but not both. And dirty buffers can reside on the LRU if they are deemed popular or have yet to be discovered as not popular. But regardless of the buffer's status of free, pinned, or dirty, if they are not deemed not popular then eventually they will be replaced by another buffer. (Something no buffer ever wants, because the party is in the buffer cache!)
Continuing our example, below is an example showing two LRUs (LRU 1 and LRU 2).
To create the above image, the only change from the previous image was the Focus was changed to LRU. The full settings are: Buffer Headers to 8, CBC Buckets to 4, LRUs to 2, Dirty % to 10, Cloning to No, Focus to LRU, Visualization to Nodes, Orientation to Center, Circle Size to 0.06, and the Random Seed to 1000.
The image above contains only six buffer headers linked to two LRUs. In real Oracle systems, there will be thousands of buffer headers linked to probably less than 100 LRUs. You may be wondering where the other two buffer headers are? (Remember our examples contains eight buffer headers.) In addition to residing on a cache buffer chain, each buffer header resides on either an least recently used list or a write list. In this case, six buffer headers reside on the two LRUs and the remaining two buffer headers reside on the two write lists (W1 and W2). (There is an associated write list for every least recently used list.)
Looking at the image above, the only thing I changed in the visualization is the Focus was reset to Write. The full settings are: Buffer Headers to 8, CBC Buckets to 4, LRUs to 2, Dirty % to 10, Cloning to No, Focus to Write, Visualization to Nodes, Orientation to Center, Circle Size to 0.06, and the Random Seed to 1000.
Notice that buffer header BH 107 resides on write list W 1B and cache buffer chain CBC 2, but is no where to be found on any LRU! Eventually the database writer will write buffer 107 to disk making it free again and then buffer header BH 107 will be placed back into one of the least recently used lists.
Less Clutter and More Information
If you play with this tool for a while, you'll begin to realize by chaining the Visualization setting from Nodes to Points you can cram a lot more information into the visualization. Yes, much of the detail is lost, but you'll begin to notice how complex the buffer cache structures can become and how amazing it is that there aren't more ORA-600 errors! It's not like looking at Yosemite, but you may get a limited sense of awe and wonder.
If you mouse over the nodes (when there are not too many displayed) Mathematica will display the node name. The image below shows this.
I moused over the far left point (with a to/from link) and Mathematica displayed, "LRU 1B" which is the beginning of LRU 1. The above picture was created with these settings: Buffer Headers to 8, CBC Buckets to 1, LRUs to 1, Dirty % to 10, Cloning to No, Focus to Full, Visualization to Points, Orientation to Center, Circle Size to 0.03, and the Random Seed to 1000.
If you look closely at each buffer header node (they are the center nodes of the main flowing list), you will notice that each node, which is a buffer header, has a number of branches. The single branch (that is, link) is to the block on the database file (notice each node has one "to" pointer), the remaining to and from links are so the buffer header can be correctly placed on a cache buffer chain, a least recently used or a write list, and the pointer to/from the buffer in the buffer cache.
The image above consists of a single least recently used list. Notice that the main trunk looks crowded! And crowding means that the buffer headers are residing on some of the same lists which, is more likely to cause memory related contention, such as some type of latching contention. With this type of visualization it's easy to see that if every server process needs to access this single least recently used list, contention could easily manifest. In fact, we would probably see CPU consumption dramatically increase resulting in a CPU bottleneck along with the Oracle wait event, latch: cache buffers lru chain be the top wait event. The image below is exactly the same above with the single exception that there are now two least recently used lists.
I purposely moused over least recently used list one's endpoint, LRU 1E. It's actually the node with the to/from links just to the upper left of the upper left mouse over box corner. Least recently used list LRU 1 starts at the upper and far left of the image and links all the way down to its endpoint, LRU 1E. Notice that when LRU 1 ends, to the list's right the main trunk is not quite as crowed and in fact further right the main trunk becomes even less crowded. By adding an additional least recently used list, we have reduced the crowding, that is, we have reduced the likelihood of latch related contention. I could go on and on about this, but that's the purpose of this blog entry.
And of course with the point visualization option, you can create all sorts of interesting images. Here is just one such smiley creation:
The above picture was created with these settings: Buffer Headers to 100, CBC Buckets to 10, LRUs to 6, Dirty % to 10, Cloning to Yes, Focus to Full, Visualization to Points, Orientation to Center, Circle Size to 0.03, and the Random Seed to 1063. Try changing the Random Seed and you'll see a number of variations to the basic structure.
One final picture makes use of the Arrows Visualization option. This is another way to pack lots of information into a single visualization. This is kind of a halfway image between the full nodes and the points. I personally like the color because it adds information to the image making it easier for me to understand. The cache buffer chains are green and when multiple lists cross over, they are also shown in green. (I'm still trying to change/fix that!)
The above picture was created with these settings: Buffer Headers to 10, CBC Buckets to 8, LRUs to 4, Dirty % to 10, Cloning to Yes, Focus to Full, Visualization to Arrows, Orientation to Center, Circle Size to 0.03, and the Random Seed to 1014. Again, have some fun by messing with the Random Seed values.
I hope you take the opportunity to download the buffer cache visualization tool and have some fun yourself! I think you'll be amazed and how much you can learn. Plus next time you need to help someone understand buffer cache internals or explain why a buffer cache performance situation is occurring, you can actually show them what you're talking about!
And if you enjoy the buffer cache visualization, you'll really enjoy the library cache visualization tool. The images it can create are absolutely stunning.
Thanks for reading,
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Oracle Database IO Read Wait Occurrence Mismath - Part 1||Do Active Oracle Database Background Processes Have A SQL_ID?||Understanding How An Intense And Diverse SQL Workload Causes Parsing Problems|