Database Reference
In-Depth Information
Problem Description
The buffer cache is shared among all processes belonging to a database instance. As a result, several processes might
concurrently need to read or modify the very same block stored in the buffer cache. To avoid conflicting accesses,
each process, before being able to access a block in the buffer cache, has to hold a pin on it (there are exceptions to
this rule, but it's not important to discuss them for the purpose of this section). A
pin
, which is a short-term lock, is
taken in either shared or exclusive mode. On a given block, several processes might hold a pin in shared mode
(for example, if they all need to read the block only), while only a single process can hold it in exclusive mode (needed
to modify the block). Whenever a process needs a pin in a mode conflicting with other pins held by other processes, it
has to wait. It's confronted with
block contention
.
■
Before being able to pin or unpin a block, a process has to get the cache buffers chains latch protecting the
block. Because of that, it might happen that block contention is masked by and/or comes with contention for a latch.
Note
Problem Identification
If you're following the recommendations provided in Part 2, the only effective way to identify block contention
problems is to measure how much time is lost because of them. For that purpose, you should check whether the
application experiences the wait event related to block contention, namely,
buffer busy waits
. In fact, processes
that experience block contention wait for that event. As a result, if this event shows up as a relevant component in the
resource usage profile, the application is suffering because of block contention. To troubleshoot such a situation, you
need the following information:
•
The SQL statement that experiences the waits
•
The segment on which the waits occur
•
The class of the blocks on which the waits occur
As described in Part 2, the best way to get the required information depends on the kind of problem you're facing.
Is the problem reproducible or irreproducible? For irreproducible problems, is the analysis performed in real time
or postmortem? In addition, licensing requirements should be considered. For example, do you have the Diagnostic
Pack license? It's also essential to recognize that not all techniques described in Part 2 are suitable to accurately
troubleshoot a block contention problem. In fact, although you can unequivocally identify block contention problems
by using techniques based on dynamic performance views (for example, Snapper or Active Session History) and on
SQL Trace, you end up in situations involving some uncertainty when using techniques based on AWR and Statspack
reports. There are two main reasons for this:
•
A system-wide analysis can pinpoint only problems that are so relevant that they impact the
whole system. As a result, you might miss a problem that impacts only specific sessions.
•
AWR and Statspack reports are based on a number of dynamic performance views that
contain data that can't always be correlated. To illustrate this limitation, let's have a look at the
v$waitstat
view (the following query shows an example of the information it provides). Even
though the content of this view provides required information about the class of the blocks
on which the waits occurred, there's no way to know for sure which SQL statements waited on
those blocks (notice that all columns of the view are shown).
