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.state, count(*) from x$kcbwds ds,x$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds,o.name object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state from obj$ o, x$bh x where o.dataobj# = x.obj and x.state !=0 and o.owner# !=0 ) 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 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'), bh.object_name, bh.state order by subcache, object_name, state;