What is a Buffer in Oracle SQL & PLSQL??

In SQL, PL/SQL we all heard about one word regularly i.e.:- buffer but did anyone thought that:

• What exactly buffer is?
• Why it is so important?
• Why our faculty always use this term?

Here is the answer, Oracle started with LRU (Least Recently Used) algorithm in older versions. Conceptually LRU is nothing but a list of pointers to buffer blocks. When a buffer block is touched, the pointer moves towards the most recently end of LRU chain. Remember that it’s the pointer which moves up the chain, buffer blocks are never moved. The general idea is to keep the more popular blocks in buffer cache and ask less popular block to leave.

But, as we all know that nothing is perfect. For example a full table scans!! This will get all the buffers into the buffer cache making other important buffers to leave the cache. For example if the buffer cache is having 500 blocks and full table scan is getting 600 blocks in buffer cache, all the popular blocks will go away.

To overcome this problem, Oracle came with a modified LRU algorithm. This modified LRU algorithm takes care of full table scan by keep the buffers of full table scan at the LRU end of LRU chain and also it will allow only limited number of blocks to be put in LRU end of LRU chain. This will avoid flooding buffer cache with huge number of buffers from full table scan. Also along with this algorithm, Oracle implemented multiple buffer pools – KEEP BUFFER POOL, RECYCLE BUFFER POOL and DEFAULT BUFFER POOL which are used currently. As you must be knowing, the data which is used frequently and should remain in buffer cache should be placed in KEEP BUFFER POOL, buffer that should not stay for longer in buffer cache should be placed in RECYCLE BUFFER POOL and if we don’t specifically mention any buffer pool doing table creation, it will go in DEFAULT POOL.

Note: We will see Buffer Cache Management, Touch Count Algorithm, Touch Count Increment, the Buffer movements and the hidden parameters.

Buffer cache Management
First we understand why does Oracle need to have such a complex algorithm in place? We have already seen that modified LRU algorithm takes care of full table scan. But if you think again we have another issue – Like in servers we face large index range scan? Imagine large number of index leaf blocks flowing into the buffer cache. This issue won’t be addressed by modified LRU.

Also with growing sizes for buffer cache, better and better performance requirements and more control Oracle introduced touch count algorithm.

For smooth and effective buffer cache operation, a very fast and very flexible algorithm must be implemented that essentially forces every buffer to seriously earn the right to remain in buffer cache. Touch count algorithm makes it very difficult for buffer to simply remain in buffer cache. It’s almost cruel how many hurdles a buffer must continually jump to simply remain in buffer cache.

At the core of touch count algorithm are the harsh requirements placed on each buffer to not only remain in cache, but to remain in MRU end of LRU list. Conceptually touch count algorithm does this by assigning each buffer a counter. Each time a block is touched its counter is incremented. The value of this counter is used to assign popularity to the blocks. But it’s not the end. Keep reading!!

Touch Count Algorithm
In the beginning we will see Mid-point Insertion

Each LRU is divided into two basic areas or region; a hot region and a cold region. All buffers in hot region are called hot buffers and all buffers in cold region are called cold buffers. There is a mid-point marker moves in order to make sure that correct number of buffers are present in each regions. This mid pointer is not associated with any buffer.

By default Oracle divides LRU equally between hot and cold region. However we can change the default setting by changing a hidden parameter see the hidden parameters below. If we increase this parameter then buffers in hot region will increase, that is buffers above midpoint will increase.

Touch Count Increment
Now we know that when a server process reads a new block from disk into the buffer cache, where exactly it’s going to put the block. Let’s now consider how the touch count increases for blocks.

Theoretically whenever a buffer is touched, its touch count should increase. But practically Oracle does not let that happen. Think of a situation where some processes needs some blocks in buffer very frequently for some period and after that period that block is not so required. In that case Oracle process might access the block so frequently that within a second its touch count will grow huge and it will become eligible to be placed in hot region. Not only that, it might become eligible to stay in hot region. Since this block won’t be used later, we don’t want this block to stay just because it was used for initial period of time.

To overcome this problem, Oracle only allows buffer’s touch count to be incremented, at most, once every 3 seconds. Again, the 3 seconds is default timing by Oracle, which can be changed using hidden parameter see the hidden parameters below.

When a touch count is incremented buffer pointer should move. But movement of buffer pointer is independent of touch count increment. Also for any activity in memory area oracle needs a latch for assuring cache consistency. But there is an exception here!!

For updating touch count, Oracle does not use latch and buffer block can be modified while touch count is getting incremented. But more interesting is that, two processes may increment the touch count to same value, and when this happens Oracle assures the worst that could happen is the touch count is not actually incremented every time a buffer is touched and that no cache corruption will result.

Buffer Movement
As mentioned previously, when a buffer is brought into the buffer cache it’s placed in the middle of hot region and cold region. Unlike LRU algorithm, touch count algorithm will not move the block to hot region just because it’s touched. Yes, its touch count will probably be incremented.

When a server process if looking for the free buffer to place the content of data file block into buffer cache or when the DBWR is looking for dirty buffer to write to disk, and if the buffer’s touch count is observed to be greater than 2, its moved to MRU end of LRU list. This default threshold of block movement is controlled by hidden parameter _db_aging_hot_criteria.

Oracle touch count implementation is tough on buffers!! When buffer is moved to MRU end of LRU list, its touch count is set to zero. So this buffer which is newly brought into the hot region has hit touch count reset to 0 immediately. For this block to remain in the hot region its touch count should be incremented significantly. So if this block is really accessed frequently, its touch count will increase automatically and it will survive in hot region.

Hot and Cold Movement
If a buffer is moved from cold region to hot region, the midpoint marker has to shift to accommodate correct number of blocks in hot a cold region. So 1 block from hot region will be forced into the cold region which is least frequently used and which belongs to LRU end of LRU list. When this happens the touch count of that block is reset to 1. Even if the buffer’s touch count is 250, after moving to cold region its touch count is reset to this threshold crossing touch count reset value is controlled by the instance parameter see the hidden parameters below. This means the buffer must be all touched again to make its move to the hot region.

Hidden Parameters:-
(1) _db_percent_hot_default:- It is used to change the default setting of hot and cold region.
(2) _db_aging_hot_criteria:-It is used to control the block movement.
(3) _db_aging_cool_count:-It is used to control threshold crossing touch count reset value.
(4) _db_aging_touch_time:-It is used to change default timing by Oracle of touch count buffer.

Conclusion
This article has shown you how buffer is used in SQL & PLSQL. After completing this article you will get an increase in your understanding & knowledge in buffers. By no means does this article provide an exhaustive list of the Oracle SQL & PLSQL buffers. Review the documentation for more details

Author, Prashant Jain is a student of Red Circle and freelancer trainer. He has deep knowledge in Programming Concepts and Algorithms.

About the Red Circle
Red Circle Technologies Pvt. Ltd. is an IT company that will rely on the proven skills of its founder to take advantage of the growing need for Oracle Training, Staffing, & Consulting. Red Circle differentiates itself from its competitors through focused area (Oracle Technologies) and lower pricing. By specializing in just oracle software products, Red Circle gains access to the largest segment of a growing market. This tightly focused approach also makes it easier for Red Circle to establish and retain a position as an industry leader. We are most sought of company when Oracle solutions have to be addressed.

Comments

1) There is no hot and cold region “actually” . Oracle implements this behavior using two separate lists, a MAIN and AUX list. So for the LRU, there is going to be a LRU-MAIN and LRU-AUX list. And now, the lists are called REP -REPLACEMENT lists

2) Your point of mentioning that the TCH would be reset to 1 is wrong. Its going to be reset to 0 not 1 as doing so would throw the buffer or in better words, would relink it to the LRU – AUX list.