15.5 How do I…Determine the buffer cache hit ratio?

Problem

I want to make sure that the database is reading from memory, rather than from disk, whenever possible. If the buffer cache hit ratio is too low, I could benefit by adding more database block buffers to the SGA. I don’t want to add database block buffers without analyzing the hit ratio, however, because I may be unnecessarily committing memory that the operating system needs. How do I identify the database buffer cache hit ratio?

Technique

Whenever Oracle reads data from disk, it places that data into the database buffer cache so that it is available for future read requests generated by any database user. Data is “aged out” of the buffer cache using a least recently used (LRU) algorithm. When users or applications request data, Oracle first looks in the buffer cache, starting at the most recently used (MRU) end of the LRU list. If the requested block does not exist in the buffer cache, either because it was never there or because it has aged out, then Oracle must search the disk-based data files that comprise the database.

On most production systems, Oracle should not have to resort to disk reads more than 20% of the time. Listing 15.6 shows an excerpt of the section of the UTLESTAT report that reveals the database buffer cache hit rate.

Listing 15.6 The data dictionary cache performance section of the UTLESTAT report

Statistic Total Per Transact Per Logon Per Second
------------ ------------ ------------ ------------ ------------
consistent gets 15100 15100 7550 188.75
db block gets 110 110 55 1.38
physical reads 1728 1728 864 21.6
To use this report to determine the buffer cache hit ratio, use this formula:

hit ratio = (consistent gets + db block gets)/( consistent gets +

db block gets + physical reads) * 100

Another method to determine the buffer cache hit rate is to query the V$SYSSTAT dynamic performance view. Table 15.4 contains a description of this view.

Table 15.4 The V$SYSSTAT dynamic performance view

Column Column Description
STATISTIC# Statistic number
NAME Statistic name
CLASS Statistic class:
  1 (User)
  2 (Redo)
  4 (Enqueue)
  8 (Cache)
  16 (OS)
  32 (Parallel Server)
  64 (SQL)
  128 (Debug)
VALUE Statistic value

Steps

1. Start SQL*Plus and connect as the WAITE user. Use the start command to load and execute the script contained in the file called CHP15_8.SQL. This script and its output appear in Figure 15.6.

Lines 2 and 3 of this query calculate the total number of disk read operations that the buffer cache satisfied. Lines 5 through 7 of the query calculate the total number of read operations. The SUM operator in conjunction with the DECODE function forces the query to return a single row. This is preferable to joining the V$SYSSTAT table with itself three times to get the desired results.

How It Works

Step 1 queries the V$SYSSTAT dynamic performance view to calculate the hit ratio. The formula makes use of the SUM operator and the DECODE function to return a single calculation instead of multiple rows.

Comments

If the buffer cache hit value returned by Step 1 is less than 80 percent, you should consider increasing the size the database buffer cache in the SGA, which can be done by increasing the value for DB_BLOCK_BUFFER in the initialization parameter file. There is an economy to observe here, though. If you are too stingy with the DB_BLOCK_BUFFER parameter then the buffer cache hit rate will not improve, at least not by much. Increase DB_BLOCK_BUFFER too greatly, though, and the SGA will consume so much memory that general operating system performance will suffer. So if the database buffer cache hit rate is less than 80%, how many additional buffers do you need to bump this value up to 80%? The answer is the subject of the next section.