首页 > 应用开发 > IT综合 > X$BH


原创 IT综合 作者:playwawa 时间:2005-09-07 10:11:13 0 删除 编辑
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.[@more@]

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.
  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.)

  ,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.

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: Linux 指令大全(3)
下一篇: skip lock
  • 博文量
  • 访问量