Database Reference
In-Depth Information
The
X$BH
table shows information about the blocks in the block buffer cache (which offers more information
than the documented
V$BH
view). Here, we can see the touch count get incremented as we hit blocks. We can run
the following query against that view to find the five “currently hottest blocks” and join that information to the
DBA_OBJECTS
view to see what segments they belong to. The query orders the rows in
X$BH
by the
TCH
(touch count)
column and keeps the first five. Then we join the
X$BH
information to
DBA_OBJECTS
by
X$BH.OBJ
to
DBA_OBJECTS.
DATA_OBJECT_ID
:
SYS@ORA12CR1> select tch, file#, dbablk,
2 case when obj = 4294967295
3 then 'rbs/compat segment'
4 else (select max( '('||object_type||') ' ||
5 owner || '.' || object_name ) ||
6 decode( count(*), 1, '', ' maybe!' )
7 from dba_objects
8 where data_object_id = X.OBJ )
9 end what
10 from (
11 select tch, file#, dbablk, obj
12 from x$bh
13 where state <> 0
14 order by tch desc
15 ) x
16 where rownum <= 5
17 /
TCH FILE# DBABLK WHAT
---------- ---------- ---------- ------------------------------
98 1 2825 (INDEX) SYS.I_JOB_NEXT
13 1 337 (INDEX) SYS.I_OBJ1
13 1 62117 (INDEX) SYS.I_OBJ1
11 1 4377 (INDEX) SYS.SYS_C00819
11 1 209 (TABLE) SYS.USER$ maybe!
■
The (2^32 - 1) or 4,294,967,295 referred to in the
CASE
statement is a magic number used to denote “special”
blocks. If you'd like to understand what the underlying block in that instance is associated with, use the query
select *
from dba_extents where file_id = <FILE#> and block_id <= <DBABLK> and block_id+blocks-1 >= <DBABLK>
.
Note
You might be asking what is meant by the “maybe!” and the use of
MAX()
in the preceding scalar subquery. This is
due to the fact that
DATA_OBJECT_ID
is not a “primary key” in the
DBA_OBJECTS
view, as evidenced by the following:
SYS@ORA12CR1> select data_object_id, count(*)
2 from dba_objects
3 where data_object_id is not null
4 group by data_object_id
5 having count(*) > 1;
