Analyzing Oracle SGA Database Buffer Cache
Posted: October 8th, 2008 | Author: TnT Admin | Filed under: Analyze | Tags: Oracle | No Comments »
Like the Shared Pool (Library Cache and Data Dictionary Cache), the performance of the Database Buffer Cache is determine with the cache hit-ratio. Cache hits occur whenever a user process finds that a data buffer needed by their SQL statement is already cached in memory; Consequently, cache misses occur when the user process does not find the requested data to already be cached in memory – causing the data to be read form disk instead. High cache hit ratios indicate that your application users are frequently finding that the data buffers they need are already in memory thus reducing the need (and delay) to read from disk (remember that disk reads are much slower than memory).
On the other hand, there are also non-hit ratio measures of Database Buffer Cache effectiveness such as Free Buffer Inspected Waits, Buffer Busy Wait events and Free Buffer Wait events. To find out about the hit-ratio for the Database Buffer Cache, we can use V$SYSSTAT, V$SYSTEM_EVENT and STATSPACK.
The counters that we should be watching are as followed:
- V$SYSSTAT: Buffer Cache Hit Ratio
- V$SYSSTAT & V$SYSTEM_EVENT: Free Buffer Inspected
- STATSPACK > Instance Efficiency Percentages (Target 100%): Buffer Hit %
- STATSPACK > Instance Activity Stats for DB: <instance>: Free Buffer Inspected
- STATSPACK > Buffer Pool Statistics for DB: <instance>: Buffer Busy Wait
What we want to see of the counters are:
- Buffer Cache Hit Ratio should be higher than 90% for OLTP system according to Oracle.
The following queries will be used to determine the hit-ratio:
- SELECT 1 – ((physical.value – direct.value -lobs.value) / logical.value) “Buffer Cache Hit Ratio” FROM V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical WHERE physical.name = ‘physical reads’ AND direct.name = ‘physical reads direct’ AND lobs.name = ‘physical reads direct (lob)’ AND logical.name = ’session logical reads’;
- SELECT name, value FROM V$SYSSTAT WHERE name IN (‘free buffer inspected’) UNION SELECT event, total_waits FROM V$SYSTEM_EVENT
WHERE event in (‘free buffer waits’, ‘buffer busy waits’);
To know more about the counters, read on.
[1] V$SYSSTAT
Out of the 200 different available statistics, the performance of Database Buffer Cache for hit-ratio is calculated and based on four statistics, namely, Physical Reads, Physical Reads Direct, Physical Reads Direct [LOB] and Session Logical Reads. Based on the four statistics, we are trying to calculate the physical reads made unintentionally (or on normal circumstances) against the the total of hits made on the cache represented by Session Logical Reads. The general formula for Database Buffer Cache is as followed:
1 – ((physical reads – physical reads direct – physical reads direct [LOB]) / session logical reads)
The syntax of the SQL is as followed:
SELECT 1 – ((physical.value – direct.value – lobs.value) / logical.value) “Buffer Cache Hit Ratio” FROM V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical WHERE physical.name = ‘physical reads’ AND direct.name = ‘physical reads direct’ AND lobs.name = ‘physical reads direct (lob)’ AND logical.name = ’session logical reads’;
Physical Reads; This indicates the number of data blocks (i.e. tables, indexes, and rollback segments) read from disk into the Buffer Cache since instance startup.
Physical Reads Direct; This indicates the number of reads that bypassed the Buffer Cache because the data blocks were read directly from disk instead. Because direct physical reads are done intentionally by Oracle when using certain features like Parallel Query, these reads are subtracted from the Physical Reads value when the Buffer Cache hit ratio is calculated. Otherwise, including these direct reads in the Buffer Cache hit ratio calculation would result in an artificially low hit ratio.
Physical Reads Direct (LOB); This indicates the number of reads that bypassed the Buffer Cache because the data blocks were associated with a Large Object (LOB) data type.
Because direct physical reads are done intentionally by Oracle when accessing segments that contain LOB datatypes, these reads are also subtracted from the Physical Reads value when the Buffer Cache hit-ratio is calculated. Including these direct reads in the Buffer Cache hit ratio calculation would also result in an artificially low hit ratio.
Session Logical Reads; This indicates the number of times a request for a data block was satisfied by using a buffer that was already cached in the Database Buffer cache. For read consistency, some of these buffers may have contained data from rollback segments.
Hit-ratios are one effective measure of Database Buffer Cache performance. However, there are also ways to measure performance of Database Buffer Cache through non-hit ratio. TIP: Because these statistics can help guide you towards the true tuning trouble-spot, using them as a starting point of Buffer Cache tuning is often more effective than calculating Buffer Cache hit ratios and blindly increasing the size of the Buffer Cache. The V$SYSSTAT and V$SYSTEM_EVENT can be used to monitor these secondary indicators of Buffer Cache Performance. The V$SYSSTAT contains statistics of the Free Buffer Inspected statistics and the V$SYSTEM_EVENT contains statistics on the Free Buffer Waits and Buffer Busy Waits. All three of these can be combined into one query using the SQL below:
SELECT name, value FROM V$SYSSTAT WHERE name IN (‘free buffer inspected’) UNION SELECT event, total_waits FROM V$SYSTEM_EVENT
WHERE event in (‘free buffer waits’, ‘buffer busy waits’);
Free Buffer Inspected; Number of Buffer Cache buffers inspected by user Server Process before finding a buffer. A closely related statistic is dirty buffer inspected, which represents the total number of dirty buffers a user process found while trying to find a free buffer.
Free Buffer Waits; Number of waits experienced by user Server Processes during Free Buffer Inspected activity, These waits occur whenever the Server Process had to wait for Database Writer to write a dirty buffer to disk.
Buffer Busy Waits; Number of times user Server Processes waited for a free buffer to become available. These waits occur whenever a buffer requested by user Server Processes is already in memory, but is in use by another process. These waits can occur for rollback segment buffers as well as data and index buffers.
The SQL statement queries the state of the statistics and it is recommend to record a benchmark over a period of time before proceeding with a load test. High or steadily increasing values for any of these statistics indicate that user Server Processes are spending too much time searching for, and wait for access to, free buffers in the Database Buffer Cache.
[2] STATSPACK
Unlike the V$SYSSTAT and V$SYSTEM_EVENT, STATSPACK does not require calculation of the statistics as it has been calculated before display. In the Instance Efficiency Percentage section, look at “Buffer Hit %” for the Database Buffer Cache hit-ratio. For non-hit ratio, look at Instance Activity Stats for DB: <instance>: Free Buffer Inspected for Free Buffer Statistics and Buffer Pool Statistics for DB: <instance>: Buffer Busy Wait provides statistics for Number of times user Server Processes waited for a free buffer to become available.
Leave a Reply