Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.
Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.
Step 1: Identify the amount of memory that you can allocate to block buffers. Create buffer pool without inducing significant paging or swapping.
Deciding the size of block buffer is critical to the overall system performance. A large block buffer will provide more memory space for caching data blocks in the SGA. In such a case, most SQL statements will find data blocks in the memory and a less time consuming IO will be performed by the database. The more the data cached in the memory, the faster is the database response. Life of DBAs would have been much simpler if we could cache the entire database (or even 50% of the database) in the block buffers, but we are limited by the physical memory available on the server. If we allocate a very large block buffer, the server may swap / page the SGA into a swap space on the disk (If SGA in not locked in the physical memory). In such circumstances, data will no longer be accessed from the memory, and CPU will have to work harder to manage the virtual memory of the server, thus degrading the overall system performance. A very large block buffer may defeat the purpose of caching data in memory for faster access, if the operating system is swapping / paging SGA pages. Within the limitations of the physical memory, the DBA should allocate block buffers to cache data in the SGA without inducing paging or swapping on the server. Hence, understanding the amount of memory available for block buffers is key to tuning the block buffers.
You can set the buffer cache by specifying DB_CACHE_SIZE (or DB_BLOCK_BUFFERS) initialization parameter as:-
DB_CACHE_SIZE = 128 M
Step 2: Identify how frequently data blocks are accessed from the buffer cache (a. k. a Block Buffer Hit Ratio).
Once you have allocated the available memory to block buffers, the next step is to identify the efficiency of block buffer usage. Oracle database maintains dynamic performance view V$BUFFER_POOL_STATISTICS with overall buffer usage statistics. This view maintains the following counts every time a data block is accessed either from the block buffers or from the disk:
NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT
DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads
Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio:
SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
Before measuring the database buffer hit ratio, it is very important to check that the database is running in a steady state with normal workload and no unusual activity has taken place. Buffer hit ratio can be skewed depending on the type of SQL statements running in the database since startup. For example, when you run a SQL statement just after database startup, no data blocks have been cached in the block buffers. At this point, Oracle reads the data blocks from the disk and will cache the blocks in the memory. If you run the same SQL statement again, then most likely the data blocks will still be present in the cache, and Oracle will not have to perform disk IO. If you run the same SQL statement multiple times you will get a higher buffer hit ratio. On the other hand, if you either run SQL statements that rarely query the same data, or run a select on a very large table, the data block may not be in the buffer cache and Oracle will have to perform disk IO, thereby lowering the buffer hit ratio.
A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.
Step 3: Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.
If you have a low buffer hit ratio and you cannot increase the size of the database block buffers, you can still gain some performance advantage by tuning the block buffers and efficiently caching the data block that will provide maximum benefits. Ideally, we should cache data blocks that are either frequently used in SQL statements, or data blocks used by performance sensitive SQL statements (A SQL statement whose performance is critical to the system performance). An ad-hoc query that scans a large table can significantly degrade overall database performance. A SQL on a large table may flush out frequently used data blocks from the buffer cache to store data blocks from the large table. During the peak time, ad-hoc queries that select data from large tables or from tables that are rarely used should be avoided. If we cannot avoid such queries, we can limit the impact on the buffer cache by using RECYCLE buffer pool.
A DBA can create multiple buffer pools in the SGA to store data blocks efficiently. For example, we can use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA to store data blocks for current SQL statement / transaction that we do not intend to hold in the memory after the transaction is completed. Similarly, we can use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks. You can create the RECYCLE and KEEP pools by specifying the following init.ora parameters:
DB_KEEP_CACHE_SIZE =
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>
When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.
Step 4: Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.
By default, data blocks are cached in the DEFAULT pool. The DBA must configure the table to use the KEEP or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE TABLE or the ALTER TABLE statement. For example, you can assign a table to the recycle pool by using the following ALTER TABLE SQL statement.
ALTER TABLE TABLE_NAME STORAGE (BUFFER_POOL RECYCLE)
The DBA can take help from application designers in identifying tables that should use KEEP or RECYCLE pool. You can also query X$BH to examine the current block buffer usage by database objects (You must log in as SYS to query X$BH). The following query returns a list of tables that are rarely used and can be assigned to the RECYCLE pool.
Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
The following query will return a list of tables that are frequently used by SQL statements and can be assigned to the KEEP pool.
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
Once you have setup the database to use KEEP and RECYCLE pools, you can monitor the buffer hit ratio by querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the buffer pool initialization parameters.
Step 5: Identify the amount of memory needed to maintain required performance.
Oracle 9i maintains block buffer advisory information in V$DB_CACHE_ADVICE. This view contains simulated physical reads for a range of buffer cache sizes. The DBA can query this view to estimate buffer cache requirement for the database. The cache advisory can be activated by setting DB_CACHE_ADIVE initialization parameter.
DB_CACHE_ADVICE = ON
There is a minor overhead associated with cache advisory collection. Hence, it is not advisable to collect these statistics in production databases until there is a need to tune the buffer cache. The DBA can turn on DB_CACHE_ADVISE dynamically for the duration of sample workload period and collect advisory statistics.
Conclusion
Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:
KEEP: Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.
RECYCLE: Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.
DEFAULT: Cache tables that do not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.
