15.12 How do I…Detect contention for the database buffer cache?

Problem

I have used the techniques from How-To’s 15.5 and 15.6 to determine and tune the database buffer cache hit rate. I have heard that there can be contention for the database buffer cache. How do I detect database buffer cache contention and minimize or eliminate it?

Technique

There are two potential causes of database buffer cache contention. The first occurs when user processes have to skip an increasing number of dirty buffers to find a clean one. This situation generates waits. The second kind of contention is latch contention. A latch is a serialization mechanism that protects the structures residing in the SGA; Oracle uses many different types of latches.

User processes need a particular latch, the cache buffer chains latch, when they look for free buffers in the database buffer cache. A second user requesting this latch must wait for the current holder of the latch to release it. In this way, the cache buffer chains latch serializes requests on behalf of user processes that need to scan the database buffer cache.

The UTLESTAT report contains statistics that may help you identify both of these potential contention problems. Listings 15.15 and 15.16 show excerpts from the report, which highlights database buffer wait statistics and database buffer latch contention, respectively.

Listing 15.15 An excerpt from the UTLESTAT report showing buffer cache wait statistics

SQLDBA> Rem The total is the total value of the statistic between the time

SQLDBA> Rem bstat was run and the time estat was run. Note that the estat

SQLDBA> Rem script logs on as “internal” so the per_logon statistics will

SQLDBA> Rem always be based on at least one logon.

Statistic Total Per Transact Per Logon
------------- ------------- ------------- -------------
free buffer inspected 6 6 1
free buffer requested 3832 3832 638.67

Increasing values for the free buffer inspected statistic indicate an increasing number of database waits for the a clean buffer in the buffer cache. Increasing the size of the buffer cache can help.

Listing 15.16 An excerpt from the UTLESTAT report showing latch wait statistics

SQLDBA> Rem Sleeps should be low. The hit_ratio should be high.
LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS
-------------- -------------- -------------- -------------- -------------- --------------
cache buffer handle 50 0 1 0 0
cache buffers chai 66642 0 1 0 0

A hit ratio value, shown in the fourth column of Listing 15.16, of less than .95 may indicate a latch contention problem.

The statistics in the UTLESTAT report are also available in the dynamic performance views of the Oracle8 data dictionary. You can query the V$SYSSTAT dynamic performance view, shown previously in Table 15.4, to determine database buffer cache wait statistics. Query the V$LATCH dynamic performance views V$LATCH (it appears in Table 15.10) to show latch wait statistics.

Table 15.10 The structure of the V$LATCH dynamic performance view

Steps

1. Run SQL*Plus, connect as the WAITE user, and use the start command to load and execute the script CHP15_21.SQL. The script and its output appears in Figure 15.17.

2. Use the START command to load and execute the script CHP15_22.SQL (see Figure 15.18).

3. If the results of the scripts in the preceding steps suggest that buffer waits or latch contention is becoming problematic, shutdown the database, increase the value of initialization parameter DB_BLOCK_BUFFERS, and restart the database.

How It Works

Step 1 queries the V$SYSSTAT dynamic performance view to determine the number of buffers scanned by user processes looking for a free buffer. Step 2 queries the V$LATCH dynamic performance view to determine the hit rate for the cache buffers chains latch.

If Step 1 indicates a large number of buffers scanned or Step 2 reports that a cache buffers chains latch hit ratio is less than .98, then increasing the size of the database buffer cache, as shown in Step 3, may help.

Comments

In general, latch performance is easy to measure but difficult to improve. Often, the only way to do so is to increase the amount of resource governed by the latch, which in this case is the size of the database buffer cache. Increasing the number of latches may be useful in an SMP environment but will not improve latch performance on single processor platforms. Nor is there any general consensus on the effect of the SPIN_COUNT initialization parameter, which is alleged to change the behavior of all latches.

Column Column Description
ADDR Address of latch
LATCH# Latch number
LEVEL# Latch level
NAME Latch name
GETS Satisfied latch requests
MISSES Latch requests satisfied after one failure