文章介紹了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@]

oracle cache table(3)

相關文章