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
Originally, the list of nondirty blocks used to be a Least Recently Used (LRU) list. Blocks were listed in order of use. The algorithm has been modified slightly in later versions of Oracle. 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.
Note 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.
The X$BH table shows information about the blocks in the block buffer cache (which offers more information than the documented V$BH view). Here, we can see the touch count get incremented as we hit blocks. We can run the following query against that view to find the five “currently hottest blocks” and join that information to the DBA_OBJECTS view to see what segments they belong to. The query orders the rows in X$BH by the TCH (touch count) column and keeps the first five. Then we join the X$BH information to DBA_OBJECTS by X$BH.OBJ to DBA_OBJECTS.DATA_OBJECT_ID:
$ sqlplus / as sysdba
SQL> select tch, file#, dbablk,
case when obj = 4294967295
This is due to clusters (discussed in Chapter 10), which may contain multiple tables. Therefore, when joining from X$BH to DBA_OBJECTS to print out a segment name, we would technically have to list all of the names of all of the objects in the cluster, as a database block does not belong to a single table all of the time.
We can even watch as Oracle increments the touch count on a block that we query repeatedly. We will use the magic table DUAL in this example—we know it is a one-row, one-column table. So every time we run the following query, we should be hitting the real DUAL table (since we explicitly reference the DUMMY column):
SQL> select tch, file#, dbablk, DUMMY from x$bh, (select dummy from dual) where obj = (select data_object_id from dba_objects where object_name = ‘DUAL’ and data_object_id is not null);
I expect output to vary by Oracle release; you may well see more than two rows returned. You might observe TCH not getting incremented every time. On a multiuser system, the results will be even more unpredictable. Oracle will attempt to increment the TCH once every three seconds (there is a TIM column that shows the last update time to the TCH column), but it is not considered important that the number be 100 percent accurate, as it is close. Also, Oracle will intentionally “cool” blocks and decrement the TCH count over time. So, if you run this query on your system, be prepared to see potentially different results.
A block buffer stays where it is in the list and has its touch count incremented. Blocks will naturally tend to “move” in the list over time, however. I put the word “move” in quotes because the block doesn’t physically move; rather, multiple lists are maintained that point to the blocks, and the block will “move” from list to list. For example, modified blocks are pointed to by a dirty list (to be written to disk by DBWn). Also, as they are reused over time, when the buffer cache is effectively full, and some block with a small touch count is freed, it will be “placed” into approximately the middle of the list with the new data block.
The whole algorithm used to manage these lists is fairly complex and changes subtly from release to release of Oracle as improvements are made. The actual full details are not relevant to us as developers, beyond the fact that heavily used blocks will be cached, and blocks that are not used heavily will not be cached for long.