oraclex$bh说明.doc

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