怎么知道一个表有多少块在cache中？

问：怎么知道一个表有多少块在cache中？
答：you can get the # of blocks in cache. (note that there are alway blocks not rows in cache)

select count(*) from v$bh where objd='your_data_object_id';

我的实验：
SQL> select OBJECT_ID,DATA_OBJECT_ID from all_objects
2 where object_name='TD1';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
45658 45659

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from v$bh
2 where objd=45659;

COUNT(*)
----------
14537

SQL> select count(*) from td1;

COUNT(*)
----------
675664

SQL> select count(*) from v$bh
2 where objd=45659;

COUNT(*)
----------
15782

SQL> select blocks from dba_segments
2 where segment_name='TD1';

BLOCKS
----------
10240

SQL> select count(*) from v$bh
2 where objd=45659;

COUNT(*)
----------
15103
以上的实验结果很奇怪，为什么执行flush操作后，select count(*) from v$bh还有很大的值。
另外，有一点疑惑，select count(*) from v$bh的值大于td1表的块数.

答：the num of blocks from v$bh is more than 'real' num of blocks, one of the possible reasons might be that MULTI-VERSION of the data, try
select status,count(*) from v$bh where objd=45659 group by status;

or you might want to see which blocks have multiple versions by using 'having
再一次试验：
1 select count(*) from v$bh
2* where objd=45659 and status <>'free'
SQL> /

COUNT(*)
----------
9285

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from v$bh
2 where objd=45659 and status <>'free';

COUNT(*)
----------
0

SQL> select count(*) from td1;

COUNT(*)
----------
675664

SQL> select count(*) from v$bh
2 where objd=45659 and status <>'free';

COUNT(*)
----------
9285

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472932/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472932/