Re: x$bh.dbablk values (repost)

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(b.tc
>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
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).