X$BH筆記<一>

 X$BH.STATE
         0: FREE--not currently in use
         1: XCUR--exclusive
         2: SCUR--shared current
         3: CR--CR block
         4: READ-- being read from disk
         5: MREC--in media recovery mode
         6: IREC-- in instance recovery mode
         7: WRITE-- writing to disk
         8: PI-- Past image block involved in cache fusion block transfer

1、檢查資料庫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;

佔用資料庫buffer數量比較大的物件，有可能是需要調優的物件。

2、檢查某個物件的在資料庫buffer的狀態情況
select
         o.object_name,
         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 ='obj_name'
    group by o.object_name, state, blsiz
    order by blocks desc


 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-625952/，如需轉載，請註明出處，否則將追究法律責任。