| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> x$bh.dbablk values
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'
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 Fri May 23 2003 - 18:11:47 CDT
 |
 |