Database Reference
In-Depth Information
SYS@ORA12CR1> exec dbms_lock.sleep(3.2);
PL/SQL procedure successfully completed.
SYS@ORA12CR1> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
2 1 1416 X
2 1 1417 X
SYS@ORA12CR1> exec dbms_lock.sleep(3.2);
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);
PL/SQL procedure successfully completed.
SYS@ORA12CR1> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
5 1 1416 X
5 1 1417 X
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.
So, in Oracle8
i
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.
