Friday, November 24, 2006

Touch-Count Algorithm




Touch-Count Algorithm:

In advancement to LRU/MRU algorithm, Oracle 8i moves towards an efficient
algorithm of managing the Buffer cache i.e. touch-count algorithm.




The Buffers in the Data Buffer Cache is managed as shown in the figure above. Prior to Oracle 8, when the data is fetched into data buffers from disk, the data used to
automatically place at the head of the MRU end. However from Oracle 8 onwards the
new data buffers are placed at the middle of block-chain. After loading the data-block, Oracle keeps the track of the “touch” count of the data.

And according to the number of touches to the data, Oracle moves the data-buffers either towards MRU (hot) end or LRU (cold) end.
This is a huge advancement to the management of the data-buffers in Oracle 8
We have hot and cold areas in each buffer-pool (default, recycle, keep).
The size of hot regions is configured by the following newly added parameters of init.ora

a) _db_percent_hot_default.
b) _db_percent_hot_keep
c) _db_percent_hot_recycle

Finding Hot Blocks inside the Oracle Data-Buffers
Oracle 8i provides a internal X$BH view that shows relative performance of the databuffer pools.

Following columns are more of interest :-

a) tim : The tim column is related to the new _db_aging_touch_time init.ora
parameter and governs the amount of time between touches.

b) tch : represents the number of times a buffer has been touched by the user
access. This touch relates directly to the promotion of buffers from cold region to hot region in a buffer pool


SQL> Select b.Object_name object, a.tch touches from x$bh a, dba_objects b
2 Where a.obj=b.object_id and a.tch > 100
3* Order by a.tch desc;
OBJECT TOUCHES
----------------------------------------
APPBROKERINFO 335
SYS_C003474 335
I_SYSAUTH1 278
I_SYSAUTH1 269
I_SYSAUTH1 268
SYSAUTH$ 259
PROPS$ 258
SESSIONDATA 236
8 rows selected.


The above advanced query can be very useful for DBA’s for tracking down those objects,
which are perfect candidates to be moved from DEFAULT pool to KEEP pool.
.


The above article i have written was inspired after reading a Steve Adams article for which i lost the link
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)