Understand database buffer cache usage

Posted by admin under Tuning, Oracle

As you know, the buffer cache is an “expensive” database resource. While the amount of RAM available in servers is getting larger and larger with every generation, so is the requirements for realtime transactions, caching more data, etc…

So, in terms of caching data, RAM could still be a bottleneck for your database.  We all want to avoid physical I/O. We need to make sure appropriate database segments (indexes, tables) are cached in memory.

We can set the Oracle automatic SGA tuning parameters  (or do manual memory sizing), but how do we know which objects are using the buffer cache? Which objects are getting cached? Simple!

To help us better understand which objects are cached in the database buffer cache, we can use the following query:

SELECT owner, object_name, object_type type, round((COUNT(*)*8)/1024) MB_SIZE
From V$bh
Join All_Objects On Object_Id = Objd
Group By Owner, Object_Name, Object_Type
ORDER BY MB_SIZE DESC;

The MB_SIZE column calculates the amount of blocks resident in the buffer cache X block size (it’s 8K here, but change according to your environment) / 1024 to get the output in megabytes.

Update! An even better query that will display results from the keep cache as well as the default cache. Remember to change * in the top query to the block size of your database.

Select Object_Name  As Parttion_Name, round(sum(Blocks)*8/1024) as MBYTES
from
(
Select Decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') Subcache,
bh.object_name,bh.subname, bh.blocks
From X$kcbwds Ds,X$kcbwbpd Pd,(Select /*+ use_hash(x) */ Set_Ds,
o.name object_name, o.subname, count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
And X.State !=0 And O.Owner# !=0
group by set_ds,o.name, o.subname) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
And Pd.Bp_Size != 0
and ds.addr=bh.set_ds
)
group by object_name
order by MBYTES desc