Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


Finding Hot Blocks inside the Oracle Data Buffers

The relative performance of the data buffer pools is shown in Oracle8i by the internal x$bh view.  This view shows the following columns:

§       tim – The tim column governs the amount of time between touches and is related to the new db_aging_touch_time parameter.

§       tch  – The tch column gives the number of times a buffer is touched by user accesses.  This is the count that directly relates to the promotion of buffers from the cold region into the hot, based on having been touched the number of times specified by the db_aging_hot_criteria parameter.  

Since the tch column gives the number of touches for a specific data block, the hot blocks within the buffer can be displayed with a simple dictionary query like the one shown below:



   obj      object,

   dbarfil  file#,

   dbablk   block#,

   tch      touches




   tch > 10


   tch desc;


This advanced query is especially useful for tracking objects in the DEFAULT pool.  It was pointed out earlier that there should be enough data blocks in the KEEP pool to fully cache the table or index.  If the DBA finds hot blocks in the DEFAULT pool, they should be moved into the KEEP pool. 


The next section presents a technique for viewing the actual objects inside the data buffers and the scripts that will show their contents.

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.