You set autotrace and query select dummy fromdual

ChapTer 4 ■ MeMOry STruCTureS

DATA_OBJECT_ID COUNT(*)
-------------- ----------
337 2
6 3
29 3
620 7
2 18
781 3
8 3
750 3
64 2
10 3

dictionary. If you set autotrace on and query SELECT DUMMY FROMDUAL, you’ll observe some I/O in all releases of Oracle

(consistent gets). In 9i and earlier, if you query SELECT SYSDATE FROMDUAL or variable := SYSDATE in pL/SQL, you’ll

So every time we run the following query, we should be hitting the real DUAL table (since we explicitly reference the DUMMY column):

SYS@ORA12CR1> select tch, file#, dbablk, DUMMY
2 from x$bh, (select dummy from dual)
3 where obj = (select data_object_id
4 from dba_objects
5 where object_name = 'DUAL'
6 and data_object_id is not null) 7 /

PL/SQL procedure successfully completed.

SYS@ORA12CR1> /

TCH FILE# DBABLK D
---------- ---------- ---------- -
4 1 1416 X
4 1 1417 X

SYS@ORA12CR1> exec dbms_lock.sleep(3.2);

So, in Oracle8i and above, a block buffer no longer moves to the head of the list as it used to; rather, it 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.