Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: x$bh.dbablk values (repost)

Re: x$bh.dbablk values (repost)

From: Daniel W. Fink <>
Date: Sun, 01 Jun 2003 19:24:40 -0800
Message-ID: <>


    Thank you for pointing this out. The ol' Uncertainty Principle (or Schrodinger's Cat). Of course, I may have my principles wrong (no comments on that one!).

Daniel W. Fink

Wolfgang Breitling wrote:

> You are running into the classical problem with monitoring an
> experiment: To what extent (no pun intended) does the monitoring
> change the experiment.
> Referencing dba_extent pulls in the segment headers of all segments
> owned by 'BCA'. When you interrogate x$bh have nothing else in your
> sql. Dump the result set somewhere and then get the information from
> dba_extents so that you can match dbarfil and dbablk to a segment. I
> usually use excel and its vlookup function to do that.
> At 09:19 AM 5/29/2003 -0800, you wrote:
>> I am reposting this in the hopes that someone can help me with this
>> puzzler.
>> I am working on determining which objects have 'hot blocks'.
>> In two different sessions, I issue "select count(*) from random_data
>> where rowid_rownum in (1,2,3);" repeatedly to see what happens with
>> the touch count (x$bh.tch).
>> In another session, I look for the blocks related to this object by
>> issuing the statement:
>> select x.owner, x.segment_name, x.segment_type, b.dbarfil, b.dbablk,
>> b.class, b.state, sum(
>> from dba_extents x, x$bh b
>> where b.dbarfil = x.file_id
>> and b.dbablk between x.block_id and (x.block_id + blocks - 1)
>> and x.owner = 'BCA'
>> group by x.owner, x.segment_name, x.segment_type, b.dbarfil,
>> b.dbablk, b.class, b.state;
> [snip]
>> The oddity is that I have restarted the instance and have only issued
>> queries against the random_data table. Since I am the only user on
>> the system, I know that no other sessions are accessing the objects.
>> The interesting bit in all this is that the blocks other than
>> random_data listed in x$bh are the segment headers.
>> select segment_name, file_id, block_id, blocks, block_id+blocks-1
>> from dba_extents
>> where owner = 'BCA'
>> and extent_id = 0
>> order by file_id, block_id;
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
-- Please see the official ORACLE-L FAQ: -- Author: Daniel W. Fink INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Sun Jun 01 2003 - 22:24:40 CDT

Original text of this message