Database Reference
In-Depth Information
segments that were fairly popular but not really hot, these would be considered the warm blocks. These segments'
blocks could get flushed from the cache to make room for blocks you used infrequently (the “do not care to cache”
blocks). To keep these warm segments' blocks cached, you could do one of the following:
•
Assign these segments to the keep pool, in an attempt to let the warm blocks stay in the buffer
cache longer.
•
Assign the “do not care to cache” segments to the recycle pool, keeping the recycle pool fairly
small so as to let the blocks come into the cache and leave the cache rapidly (decrease the
overhead of managing them all).
Having to do one of these two things increased the management work the DBA had to perform, as there were
three caches to think about, size, and assign objects to. Remember also that there is no sharing among them, so if the
keep pool has lots of unused space, it won't give it to the overworked default or recycle pool. All in all, these pools
were generally regarded as a very fine, low-level tuning device, only to be used after most other tuning alternatives
had been looked at (if I could rewrite a query to do one-tenth the I/O rather then set up multiple buffer pools, that
would be my choice).
Starting in Oracle9
i
, the DBA had up to four more optional caches, the
DB_nK_CACHE_SIZE
, to consider in addition
to the default, keep, and recycle pools. These caches were added in support of multiple block sizes in the database.
Prior to Oracle9
i
, a database would have a single block size (typically 2KB, 4KB, 8KB, 16KB, or 32KB). Starting with
Oracle9
i
, however, a database can have a default block size, which is the size of the blocks stored in the default, keep,
or recycle pool, as well as up to four nondefault block sizes, as explained in Chapter 3.
The blocks in these buffer caches are managed in the same way as the blocks in the original default pool—there
are no special algorithm changes for them either. Let's now move on to see how the blocks are managed in these pools.
Managing Blocks in the Buffer Cache
For simplicity, assume that there's just a single default pool. Because the other pools are managed in the same way, we
need only discuss one of them.
The blocks in the buffer cache are basically managed in a single place with two different lists pointing at them:
•
The list of
dirty
blocks that need to be written by the database block writer
(
DBWn
; we'll take a look at that process a little later)
A list of
•
nondirty
blocks
The list of nondirty blocks used to be a Least Recently Used (LRU) list in Oracle 8.0 and before. Blocks were listed
in order of use. The algorithm has been modified slightly in Oracle8
i
and in later versions. Instead of maintaining the
list of blocks in some physical order, Oracle employs a touch count algorithm, which effectively increments a counter
associated with a block as you hit it in the cache. This count is not incremented every time you hit the block, but about
once every three seconds if you hit it continuously. You can see this algorithm at work in one of the truly magic sets of
tables: the
X$
tables. The
X$
tables are wholly undocumented by Oracle, but information about them leaks out from
time to time.
■
I am using a user connected with the SySDBa privilege in the following examples, because the
X$
tables are by
default visible only to that account. you shouldn't in practice use an account with SySDBa privileges to run queries. The
need to query for information about blocks in the buffer cache is a rare exception to that rule.
Note
