|
X$BH
|
Hello everyone. When we covered Oracle9i in previous issues, we mainly focused on
X$BH to further understand the structure of database buffer.
In this issue, I will start a new topic focusing on X$BH.
First of all, I check if there is any change in the status of the object on database buffer
when object is being searched or updated in a single instance environment.
Next, I check the status of the object in RAC environment.
Hopefully, I will talk about SGA management in Oracle10g.
Now, I briefly review the X$BH.
What is X$BH?
X$BH is a source table of dynamic performance view where you can query
as SYS user. X$BH helps you examine the type and the status of the object in
database buffer.
Following query will return a list of tables in database buffer.
*************************************************************
select
o.object_name, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
group by o.object_name, blsiz
order by blocks desc
OBJECT_NAME BLSIZ BLOCKS
------------------------------ ---------- ----------
CUSTOMER 8192 920
CUSTOMER_BAD_IDX 8192 23
ITEM 8192 11
STOCK 8192 11
STOCK_PKEY 8192 11
ITEM_PKEY 8192 9
NEW_ORDER_PKEY 8192 6
ORDER_LINE_PKEY 8192 5
ORDER_PKEY 8192 4
.
.
.
*************************************************************
|
For example, the list above indicates that the table CUSTOMER occupies
the database buffer in large quantity. In this case, SQL statements
referring to the table CUSTOMER can be a tuning target.
Why? Because this may prove that index is not performed efficiently
and full scan or inefficient range scan is performed.
If the table CUSTOMER is extremely larger than other tables, this is
not a problem.
Next, I focus on a single object and view STATE column.
Following query will return the status of each object.
(Query is narrowed down to a table TEST.)
*************************************************************
select
o.object_name
,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'
,6,'irec',7,'write',8,'pi') state
, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
and o.object_name = 'TEST'
group by o.object_name, state, blsiz
order by blocks desc
OBJECT_NAME STATE BLSIZ BLOCKS
------------------------------ ----- ---------- ----------
TEST xcur 8192 23
TEST cr 8192 5
*************************************************************
|
STATE column indicates the following:
FREE: not currently in use
XCUR: exclusive
SCUR: shared current
CR: CR block
READ: being read from disk
MREC: in media recovery mode
IREC: in instance recovery mode
WRITE: writing to disk
PI: past image block involved in cache fusion block transfer
I will explain each of the categories in future issues.
Yoshihiro Uratsuji
|
|
