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.[@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.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/202861/viewspace-806039/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-806039/