Oracle7 Server Tuning
In this section, you will learn how to tune the buffer cache. The following issues are covered in this section:
- how to monitor buffer cache performance
- how to improve buffer cache performance
Examining Buffer Cache Activity
Oracle collects statistics that reflect data access and stores them in the dynamic performance table V$SYSSTAT. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM. These statistics are useful for tuning the buffer cache:
Monitor these statistics over a period of time while your application is running with this query:
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets',
'physical reads');
The output of this query might look like this:
NAME VALUE
------------------------------------------------------ ----------
db block gets 85792
consistent gets 278888
physical reads 23182
Calculate the hit ratio for the buffer cache with this formula:
Hit Ratio = 1 - ( physical reads / (db block gets + consistent gets) )
Based on the statistics obtained by the example query, the buffer cache hit ratio is 94%.
Information in the V$SYSSTAT table can also be obtained through SNMP.
Reducing Buffer Cache Misses
If your hit ratio is low, say less than 60% or 70%, then you may want to increase the number of buffers in the cache to improve performance. To make the buffer cache larger, increase the value of the initialization parameter DB_BLOCK_BUFFERS.
Oracle can collect statistics that estimate the performance gain that would result from increasing the size of your buffer cache. With these statistics, you can estimate how many buffers to add to your cache.
The X$KCBRBH Table
The virtual table SYS.X$KCBRBH contains statistics that estimate the performance of a larger cache. Each row in the table reflects the relative performance value of adding a buffer to the cache. This table can only be accessed by the user SYS. The following are the columns of the X$KCBRBH table:
For example, in the first row of the table, the INDX value is 0 and the COUNT value is the number of cache hits to be gained by adding the first additional buffer to the cache. In the second row, the INDX value is 1 and the COUNT value is the number of cache hits for the second additional buffer.
Enabling the X$KCBRBH Table
The collection of statistics in the X$KCBRBH table is controlled by the initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS. The value of this parameter determines the number of rows in the X$KCBRBH table. The default value of this parameter is 0, which means the default behavior is to not collect statistics.
To enable the collection of statistics in the X$KCBRBH table, set the value of DB_BLOCK_LRU_EXTENDED_STATISTICS. For example, if you set the value of the parameter to 100, Oracle will collect 100 rows of statistics, each row reflecting the addition of one buffer, up to 100 extra buffers.
Collecting these statistics incurs some performance overhead. This overhead is proportional to the number of rows in the table. To avoid this overhead, collect statistics only when you are tuning the buffer cache and disable the collection of statistics when you are finished tuning.
Querying the X$KCBRBH Table
From the information in the X$KCBRBH table, you can predict the potential gains of increasing the cache size. For example, to determine how many more cache hits would occur if you added 20 buffers to the cache, query the X$KCBRBH table with the following SQL statement:
SELECT SUM(count) ach
FROM sys.x$kcbrbh
WHERE indx < 20;
You can also determine how these additional cache hits would affect the hit ratio. Use the following formula to calculate the hit ratio based on the values of the statistics db block gets, consistent gets, and physical reads and the number of additional cache hits (ACH) returned by the query:
Hit Ratio = 1 - (physical reads - ACH / (db block gets + consistent gets) )
Grouping Rows in the X$KCBRBH Table
Another way to examine the X$KCBRBH table is to group the additional buffers in large intervals. You can query the table with a SQL statement similar to this one:
SELECT 250*TRUNC(indx/250)+1||' to '||250*(TRUNC(indx/250)+1)
"Interval", SUM(count) "Buffer Cache Hits"
FROM sys.x$kcbrbh
GROUP BY TRUNC(indx/250);
The result of this query might look like
Interval Buffer Cache Hits
--------------- --------------------
1 to 250 16080
251 to 500 10950
501 to 750 710
751 to 1000 23140
where:
| INTERVAL | Is the interval of additional buffers to be added to the cache. |
| BUFFER CACHE HITS | Is the number of additional cache hits to be gained by adding the buffers in the INTERVAL column. |
Examining the query output leads to these observations:
- If 250 buffers were added to the cache, 16,080 cache hits would be gained.
- If 250 more buffers were added for a total of 500 additional buffers, 10,950 cache hits would be gained in addition to the 16,080 cache hits from the first 250 buffers. This means that adding 500 buffers would yield a total of 27,030 additional cache hits.
- If 250 more buffers were added for a total of 750 additional buffers, 710 cache hits would be gained, yielding a total of 27,740 additional cache hits.
- If 250 buffers were added to the cache for a total of 1000 additional buffers, 23,140 cache hits would be gained, yielding a total of 50,880 additional cache hits.
Based on these observations, you should decide how many buffers to add to the cache. In this case, you may make these decisions:
- It is wise to add 250 or 500 buffers, provided memory resources are available. Both of these increments offer significant performance gains.
- It is unwise to add 750 buffers. Nearly the entire performance gain made by such an increase can be made by adding 500 buffers instead. Also, the memory allocated to the additional 250 buffers may be better used by some other Oracle memory structure.
- It is wise to add 1000 buffers, provided memory resources are available. The performance gain from adding 1000 buffers to the cache is significantly greater than the gains from adding 250, 500, or 750 buffers.
Removing Unnecessary Buffers
If your hit ratio is high, your cache is probably large enough to hold your most frequently accessed data. In this case, you may be able to reduce the cache size and still maintain good performance. To make the buffer cache smaller, reduce the value of the initialization parameter DB_BLOCK_BUFFERS. The minimum value for this parameter is 4. You can apply any leftover memory to other Oracle memory structures.
Oracle can collect statistics to predict buffer cache performance based on a smaller cache size. Examining these statistics can help you determine how small you can afford to make your buffer cache without adversely affecting performance.
The X$KCBCBH Table
The virtual table SYS.X$KCBCBH contains the statistics that estimate the performance of a smaller cache. The X$KCBCBH table is similar in structure to the X$KCBRBH table. This table can only be accessed by the user SYS. The following are the columns of the X$KCBCBH table:
The number of rows in this table is equal to the number of buffers in your buffer cache. Each row in the table reflects the number of cache attributed to a single buffer. For example, in the second row, the INDX value is 1 and the COUNT value is the number of cache hits for the second buffer. In the third row, the INDX value is 2 and the COUNT value is the number of cache hits for the third buffer.
The first row of the table contains special information. The INDX value is 0 and the COUNT value is the total number of blocks moved into the first buffer in the cache.
Enabling the X$KCBCBH Table
The collection of statistics in the X$KCBCBH table is controlled by the initialization parameter DB_BLOCK_LRU_STATISTICS. The value of this parameter determines whether Oracle collects the statistics. The default value for this parameter is FALSE, which means that the default behavior is not to collect statistics.
To enable the collection of statistics in the X$KCBCBH table, set the value of DB_BLOCK_LRU_STATISTICS to TRUE.
Collecting these statistics incurs some performance overhead. To avoid this overhead, collect statistics only when you are tuning the buffer cache and disable the collection of statistics when you are finished tuning.
Querying the X$KCBCBH Table
From the information in the X$KCBCBH table, you can predict the number of additional cache misses that would occur if the number of buffers in the cache were reduced. If your buffer cache currently contains 100 buffers, you may want to know how many more cache misses would occur if it had only 90. To determine the number of additional cache misses, query the X$KCBCBH table with the SQL statement:
SELECT SUM(count) acm
FROM sys.x$kcbcbh
WHERE indx >= 90;
You can also determine the hit ratio based on this cache size. Use the following formula to calculate the hit ratio based on the values of the statistics db block gets, consistent gets, and physical reads and the number of additional cache misses (ACM) returned by the query:
Hit Ratio = 1 - ( physical reads + ACM / (db block gets + consistent gets) )
Another way to examine the X$KCBCBH table is to group the buffers in intervals. For example, if your cache contains 100 buffers, you may want to divide the cache into four 25-buffer intervals. You can query the table with a SQL statement similar to this one:
SELECT 25*TRUNC(indx/25)+1||' to '||25*(TRUNC(indx/25)+1)
"Interval", SUM(count) "Buffer Cache Hits"
FROM sys.x$kcbcbh
WHERE indx > 0 GROUP BY TRUNC(indx/25);
Note that the WHERE clause prevents the query from collecting statistics from the first row of the table. The result of this query might look like
Interval Buffer Cache Hits
--------------- --------------------
1 to 25 1900
26 to 50 1100
51 to 75 1360
76 to 100 230
where:
| INTERVAL | Is the interval of buffers in the cache. |
| BUFFER CACHE HITS | Is the number of cache hits attributable to the buffers in the INTERVAL column. |
Examining the query output leads to these observations:
- The last 25 buffers in the cache (buffers 76 to 100) contribute 230 cache hits. If the cache were reduced in size by 25 buffers, 230 cache hits would be lost.
- The third 25-buffer interval (buffers 51 to 75) contributes 1,360 cache hits. If these buffers were removed from the cache, 1,360 cache hits would be lost in addition to the 230 cache hits lost for buffers 76 to 100. Removing 50 buffers would result in losing a total of 1,590 cache hits.
- The second 25-buffer interval (buffers 26 to 50) contributes 1,100 cache hits. Removing 75 buffers from the cache would result in losing a total of 2,690 cache hits.
- The first 25 buffers in the cache (buffers 1 to 25) contribute 1,900 cache hits.
Based on these observations, you should decide whether to reduce the size of the cache. In this case, you may make these decisions:
- If memory is scarce, it may be wise to remove 25 buffers from the cache. The buffers 76 to 100 contribute relatively few cache hits compared to the total cache hits contributed by the entire cache. Removing 25 buffers will not significantly reduce cache performance, and the leftover memory may be better used by other Oracle memory structures.
- It is unwise to remove more than 25 buffers from the cache. For example, removing 50 buffers would reduce cache performance significantly. The cache hits contributed by these buffers is a significant portion of the total cache hits.