下面再建立一個同樣大小的表：
SQL> CREATE TABLE T2 STORAGE (BUFFER_POOL KEEP) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
-------
167011
Statistics
-----------
0 recursive calls
0 db block gets
4839 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 COUNT(*) FROM T2;
COUNT(*)
----------
167011

Statistics
----------
0 recursive calls
0 db block gets
4839 consistent gets
0 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
兩張表的大小合在一起已經超過了KEEP池的大小。下面看看現在記憶體中快取的情況：
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 3268
T2 xcur 4829
T2中的資料已經全部放入KEEP池，而部分T中的資料被替換出去。
下面用執行對T的查詢，嘗試快取T的資料：
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
167011
Statistics
--------------
0 db block gets
4839 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

這時可以觀察到一個有趣的現象，對T執行掃描的時候，幾乎全部都是物理讀，
而我們剛剛看到內容中仍然有3000多個資料塊在快取中。
這就是KEEP池與DEFAULT池演算法的差異之處。[@more@]

oracle cache table(2)

相關文章