| Previous | Table of Contents | Next |
The size of the buffer is determined by the database administrator—and for some databases, separate buffers may be created for different tables. The method for maximizing the use of buffers is to perform a check on the “buffer hit ratio.” The buffer hit ratio is the ratio of logical requests to physical disk reads. A logical read is a request from a program for a record, while a physical read is real I/O against a database. A 1:1 correspondence does not always exist between logical and physical reads, since some records may have been fetched by a previous task and still reside in the buffer. In other words, the buffer hit ratio is the probability of finding the desired record in the memory buffer. The equation for finding the buffer hit ratio is:
Hit Ratio = Logical Reads - Physical Reads / Logical Reads
Listings 3.7 and 3.8 show two scripts for calculating the buffer hit ratio.
Listing 3.7 Method 1 for the script.
buffer1.sql - displays the buffer hit ratio
PROMPT **********************************************************
PROMPT HIT RATIO SECTION
PROMPT **********************************************************
PROMPT
PROMPT =========================
PROMPT BUFFER HIT RATIO
PROMPT =========================
PROMPT (should be > 70, else increase db_block_buffers in init.ora)
SELECT TRUNC((1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
(sum(decode(name,'consistent gets',value,0)))))
)* 100) "Buffer Hit Ratio"
FROM v$sysstat;
Listing 3.8 Method 2 for the script.
buffer2.sql - displays the buffer hit ratio
PROMPT **********************************************************
PROMPT HIT RATIO SECTION
PROMPT **********************************************************
PROMPT
PROMPT =========================
PROMPT BUFFER HIT RATIO
PROMPT =========================
PROMPT (should be > 70, else increase db_block_buffers in init.ora)
COLUMN "logical_reads" FORMAT 99,999,999,999
COLUMN "phys_reads" FORMAT 999,999,999
COLUMN "phy_writes" FORMAT 999,999,999
SELECT a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
ROUND(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
"BUFFER HIT RATIO"
FROM v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
WHERE
a.statistic# = 37
AND
b.statistic# = 38
AND
c.statistic# = 39
AND
d.statistic# = 40;
Listing 3.9 shows the output from the second method.
Listing 3.9 The second method’s output.
SQL> @t3
**********************************************************
HIT RATIO SECTION
**********************************************************
=========================
BUFFER HIT RATIO
=========================
(should be > 70, else increase db_block_buffers in init.ora)
Fri Feb 23 page 1
dbname Database
Data Dictionary Hit Ratios
logical_reads phys_reads phy_writes BUFFER HIT RATIO
------------- ------------ ----------- ----------------
18,987,002 656,805 87,281 97
1 row selected.
Be aware that the buffer hit ratio (as gathered from the V$ tables), measures the overall buffer hit ratio of the system since the Oracle instance was started. Since the V$ tables keep their information forever, our current buffer hit ratio may be far worse than the 97 percent we see in Listing 3.9. To get a measure of the buffer hit ratio over a specific time period, use Oracle’s bstat-estat utility, as described in Chapter 9.
While some of the mainframe databases allow for individual buffers for each record type, midrange databases such as Oracle provide only one database-wide buffer for all of the database I/O. In general, the buffer hit ratio is a function of the application and of the size of the buffer pool. For example, an application with a very large customer table is not likely to benefit from an increase in buffers, since the I/O is widely distributed across the tables. However, smaller applications will often see an improvement as the buffer size is increased, since this also increases the probability that frequently requested data remains in the buffer. For example, the high level nodes of an index are generally used by all applications, and response time can be improved if these blocks can be kept in the buffers at all times.
Databases that allow segmented buffer pools (such as the CA-IDMS) can be configured such that small indexes will be kept in the buffer at all times. This is accomplished by allocating an index to a separate area, and assigning the area to a separate buffer in the Device Media Control Language (DMCL).
If the hit ratio is less then 70 percent (i.e., two-thirds of data requests require a physical disk I/O), you may want to increase the number of blocks in the buffer. In Oracle, a single buffer pool exists and is controlled by a parameter called db_block_buffers in the init.ora process.
To estimate statistics, the following init.ora parameters must be set, and the database must be bounced:
db_block_lru_statistics = true
db_block_lru_extended_statistics = #buffers
NOTE: Where #buffers is the number of buffers to add, be aware that the SGA will increase in size by this amount, such that a value of 10,000 would increase an SGA by 40 megabytes (assuming a 4 K block size). Make sure that your host has enough memory before trying this. Also, note that performance will be degraded while these statistics are running, and it is a good idea to choose a non-critical time for this test.
Oracle uses two system tables called sys.x$kcbrbh (to track buffer hits) and sys.x$kcbcbh (to track buffer misses). Note that these are temporary tables and must be interrogated before stopping Oracle. An SQL query can be formulated against this table to create a chart showing the size of the buffer pool and the expected buffer hits:
REM morebuff.sql - predicts benefit from added blocks to the buffer
SET LINESIZE 100;
SET PAGES 999;
COLUMN "Additional Cache Hits" FORMAT 999,999,999;
COLUMN "Interval" FORMAT a20;
SELECT 250*TRUNC(indx/250)+1
||' to '||250*(TRUNC(indx/250)+1) "Interval",
SUM(count) "Additional Cache Hits"
FROM sys.x$kcbrbh
GROUP BY TRUNC(indx/250);
| Previous | Table of Contents | Next |