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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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:

 

SELECT

   obj      object,

   dbarfil  file#,

   dbablk   block#,

   tch      touches

FROM

   x$bh

WHERE

   tch > 10

ORDER BY

   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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

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.