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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

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_againg_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:

See code depot for full scripts

SELECT
   . . .
FROM
   x$bh
WHERE
   . . .
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 we find hot blocks in the DEFAULT pool, they should be moved into the KEEP pool.

We are now ready to learn a technique for viewing the actual objects inside the data buffers and the scripts that will show their contents.

 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.


Complete Oracle Script Collection Available

Burleson Consulting shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, BC experts share their secrets for navigating the Oracle data dictionary.

Packed with 681 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

BC has priced this collection of 681 scripts at $79.95.  You can download them immediately at this link:

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

 

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation.