下面再创建一个同样大小的表：
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@]

ITPub博客

oracle cache table(2)

最新文章