Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

x$bh.dbablk values (repost)

From: Daniel W. Fink <optimaldba_at_yahoo.com>
Date: Thu, 29 May 2003 09:19:54 -0800
Message-ID: <F001.005A5BA6.20030529091954@fatcity.com>


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;

and the output is

OWNER      SEGMENT_NAME              SEGMENT_TYPE       DBARFIL     
DBABLK      CLASS      STATE SUM(B.TCH)
---------- ------------------------- --------------- ---------- 
---------- ---------- ---------- ---
BCA        HWM_DATA                  TABLE                   12      
12809          4          3          2
BCA        FRAG_DATA                 TABLE                   12      
19209          4          3          2
BCA        PLAN_TABLE                TABLE                   12      
25609          4          3          2
BCA        RANDOM_DATA               TABLE                   12       
6409          4          1          5
BCA        RANDOM_DATA               TABLE                   12      
12729          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12730          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12731          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12732          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12733          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12745          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12746          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12747          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12748          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12749          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12750          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12751          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12752          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12753          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12754          1          1          0
BCA        RANDOM_DATA               TABLE                   12      
12755          1          1          0
BCA        IX_SD_ROWNUM              INDEX                   13       
1929          4          3          2
BCA        PK_FD_REC_NO              INDEX                   13       
8993          4          3          2
BCA        PK_HD_REC_NO              INDEX                   13       
8969          4          3          2
BCA        PK_RD_REC_NO              INDEX                   13       
3977          4          3          2
BCA        PK_SD_REC_NO              INDEX                   13          
9          4          3          2
BCA        IX_RD_SMALL_RN            INDEX                   13       
3985          4          3          2
BCA        SEQUENTIAL_DATA           TABLE                   12          
9          4          3          2

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;

SEGMENT_NAME                 FILE_ID   BLOCK_ID     BLOCKS 
BLOCK_ID+BLOCKS-1
------------------------- ---------- ---------- ---------- 
-----------------
SEQUENTIAL_DATA                   12          9          
8                16
RANDOM_DATA                       12       6409          8              
6416
HWM_DATA                          12      12809          8             
12816
FRAG_DATA                         12      19209          8             
19216
PLAN_TABLE                        12      25609          8             
25616
PK_SD_REC_NO                      13          9          
8                16
IX_SD_ROWNUM                      13       1929          8              
1936
PK_RD_REC_NO                      13       3977          8              
3984
IX_RD_SMALL_RN                    13       3985          8              
3992
PK_HD_REC_NO                      13       8969          8              
8976
PK_FD_REC_NO                      13       8993          8              
9000

The questions are
Why are the segment headers being returned by my first query? Is there something wrong with the sql statement? Am I not reading the dbablk value properly?
Is there a process that is reading these segment headers?

--

Daniel W. Fink
http://www.optimaldba.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.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). Received on Thu May 29 2003 - 12:19:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US