|
本帖最后由 Yong Huang 于 2012-4-2 09:46 编辑
Now I see what you mean. I think if the blocks are still in buffer cache, regardless which pool, there's only memory read, and the table storage attribute (buffer_pool keep) won't have a chance to take effect. As soon as one block has to be read from datafile into buffer cache, this block will go to the keep pool. When I have time, I'll test it. Basically, we need to go from x$bh.set_ds (working set address I think) to x$kcbwds and to x$kcbwbpd to find which pool the buffer is in. The following query can be simplified to do exactly that:
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',
6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds, x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS
from obj$ o, x$bh x
where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and o.name='&tablename'
group by set_ds, o.name) 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;
|
|