文章介绍了KEEP池和DEFAULT池的差异之处，下面通过例子进行详细的说明：
首先，将T表的容量扩大1倍，这样，即使是单独查询T表，所有的数据也无法全部放入KEEP池。
SQL> INSERT INTO T SELECT * FROM T;
167011 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022

Statistics
----------------
0 recursive calls
0 db block gets
9666 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8097

SQL> SELECT SUM(BLOCKS) FROM USER_EXTENTS WHERE SEGMENT_NAME = 'T';

SUM(BLOCKS)
-----------
9728

可以看到T表一共是9728个BLOCK，而KEEP池中仅仅缓存了8097个。

SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
--------------------
db_block_size integer 16384
KEEP池的大小是128M，除以数据块的大小16K，KEEP池中可以存放的BLOCK上限就是8K个。现在KEEP池已经装满，
但是仍然有部分数据被替换到KEEP池外。[@more@]

ITPub博客

oracle cache table(3)

最新文章