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

Donald K. Burleson

Oracle Tips

New X$BH view columns in Oracle

The X$ fixed views provide a wealth of internal information about the behavior of the data buffer caches, and the addition of the following columns to X$BH adds additional data.  These include the LRU_FLAG, TCH, and TIM columns.

Let’s take a closer look at each of these undocumented columns and see if we can infer their purpose inside Oracle.


The LRU_FLAG column of X$BH contains information about LRU state of a block in the buffer cache, but this column does not reveal much useful information.


The TIM column appears to be related to the hidden parameter _DB_AGING_TOUCH_TIME, and this metric appears to be associated with the new mid-point data buffer insertion algorithm that was introduced in Oracle8i.  On the surface, it appears that the duration between RAM touches in the data buffer is tangentially related to Oracle’s internal movement of the data block to the MRU movement of the buffer.


It appears that the TCH column measures the number of times that a specific data block has been touched by an SQL query.  As we know from Oracle Internals, the Oracle9i data buffers are designed to segregate data into hot regions and cold regions, and the internal processes are used to segregate data blocks according to the frequency that they have been touched.

The TCH column appears to be related to the hidden Oracle9i parameter _DB_AGING_HOT_CRITERIA and this present some very useful insights.

As we know, one of the challenges of the Oracle DBA is to identify candidates for each of the eight data pools within Oracle.  We have the DEFAULT pool, the KEEP pool, the RECYCLE pool, as well as instantiated pools for each supported blocksize on the Oracle server.  The TCH column can be used to infer the relative popularity of data blocks from each table or index, and this data can be used to make intelligent decisions about the segregation of these database objects into their separate data buffers.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:




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.