Oracle KEEP池的CACHE特点

元娅萍

今天看了yangtingkun很多文章，相当的不错。
下面摘录关于KEEP池的文章：
本人在ORACLE11GR2上，也测试了一遍。
KEEP池的使用十分简单，设置DB_KEEP_CACHE_SIZE的值大于0，就可以将其他对象的BUFFER_POOL参数设置为KEEP了。

SQL> SHOW PARAMETER DB%CACHE_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 956301312
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> SELECT 128*1024*1024 FROM DUAL;

128*1024*1024
-------------
134217728

SQL> SELECT 956301312-134217728 FROM DUAL;

956301312-134217728
-------------------
822083584

SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 822083584;

System altered.

SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 134217728;

System altered.

创建测试用表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_SOURCE;

Table created.

SQL> SELECT SUM(BYTES)/1024/1024 M FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T';

M
----------
80

SQL> ALTER TABLE T STORAGE (BUFFER_POOL KEEP);

Table altered.

SQL> SET AUTOT ON STAT
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
167011


Statistics
----------------------------------------------------------
107 recursive calls
0 db block gets
4849 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
2 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT COUNT(*) FROM T;

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池已经生效，T的数据已经缓存在KEEP池中。

下面再创建一个同样大小的表：

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 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

这时可以观察到一个有趣的现象，对T执行扫描的时候，几乎全部都是物理读，而我们刚刚看到内容中仍然有3000多个数据块在缓存中。

这就是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池外。

下面说明KEEP池缓存的特点，先看一下查询的结果：

SQL> SELECT COUNT(*) FROM T2;

COUNT(*)
----------
167011


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 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 3268
T2 xcur 4829

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 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池中的空间被T表占满了，隐藏这时候对T2的查询需要从物理磁盘读取。执行完这个查询，可以发现，T2表全部放入缓存中，T表的数据被替换出一部分，还有3000多个BLOCK存储在KEEP池中。但是对T的查询确全部由物理读组成，而KEEP池中的缓存没有起作用。

对于普通的DEFAULT池，Oracle使用的是最近最少使用算法，在内存中有一个类似链表的结构。当DB_CACHE填满后，Oracle会从将这个链表的最少使用端交换出去，用来存放新的数据。而且会根据新的数据的性质，选择把新的数据放到最多使用端还是最少使用端。

如果DB_CACHE满后，执行的是索引扫描，则Oracle认为需要缓存这些数据，因此会清空最少使用端的空间，存放索引扫描的缓存数据。如果是大表的全表扫描，则Oracle认为这些数据是很少需要被访问的，因此清空最少使用端的空间放入表扫描的缓存数据后，仍然放回到最少使用端。

而KEEP池没有采用这种算法，KEEP池其实是一块可用内存采用类似循环的算法进行访问。如果KEEP池里面还有剩余空间，则新的数据会首先使用剩余的空间，如果KEEP池已经存储满了，Oracle会从头开始重用KEEP池。

这就是对T表的查询导致了全部的物理读的原因。由于T2表将T表中最初部分数据替换出KEEP，导致了查询T表的时候，开头部分的数据无法找到，产生了物理读后在KEEP池中替换了T表中间部分的数据，同样的道理，读取到T表中部的时候，又把T表末尾的数据替换出去了。因此，执行完查询发现，对T表查询全部都是物理读，KEEP池缓冲中的内容没有起作用。

而且，由于T表的大小超过了KEEP池的大小，因此T表末尾部分的数据又会将开头部分的数据替换出去，因此，再次对T表查询仍然全部都是物理读。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 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 T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 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表的扫描的块小于KEEP池的大小时，才能保证缓存可以被利用。

SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 100000;

COUNT(*)
----------
99999


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3696 consistent gets
3695 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 T WHERE ROWNUM < 100000;

COUNT(*)
----------
99999


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3696 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



首先，创建T表的一个索引：

SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);

Index created.

SQL> ALTER TABLE T MODIFY NAME NOT NULL;

Table altered.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 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
4 sorts (memory)
0 sorts (disk)
1 rows processed

下面看KEEP池和DEFAULT池缓存的另一个区别：

SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);

Index created.

SQL> ALTER TABLE T MODIFY NAME NOT NULL;

Table altered.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 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
4 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

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

SQL> SELECT /*+ INDEX (T) */ COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9656 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

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

通过测试可以看到，在KEEP池中，索引扫描引起的缓存不在拥有高于全表扫描缓存的优先级。根据上一篇文章中描述的KEEP池的缓存使用特点也可以推出这个结果。由于KEEP池不存在LRU链表，因此对索引扫描和全表扫描产生的缓存采用一视同仁的态度。

第二个特点，CACHE存储参数无效：

SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL KEEP) AS SELECT * FROM T2;

Table created.

SQL> ALTER TABLE T2 CACHE;

Table altered.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022

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', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;

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

SQL> SELECT COUNT(*) FROM T2;

COUNT(*)
----------
167011

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', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3267
T2 xcur 4829

SQL> SELECT COUNT(*) FROM T3;

COUNT(*)
----------
167011

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', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T2 xcur 3267
T3 xcur 4829

SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 100000;

COUNT(*)
----------
99999

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', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3696
T3 xcur 4400

通过测试可以发现，CACHE选项没有起作用，其实这也不难理解，既然放到单独的KEEP池中，那么必然打算将这个对象缓存，因此Oracle对所有KEEP池中的对象采用了默认CACHE的方式。而忽略对象本身的CACHE和NOCACHE选项。



KEEP池虽然有很多特点与DEFAULT池有较大的区别，但是有一点却是二者是相似的：最近最常用的BLOCK最晚被替换出内存。

虽然KEEP池没有采用LRU链表的方式，但是Oracle仍然考虑了LRU的算法，KEEP池的缓存中，仍然是越常用的BLOCK保留时间越长。看下面这个例子：

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

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

首先通过全表扫描T表将KEEP池“清空”。

SQL> SELECT /*+ INDEX(T) */ COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538

SQL> SELECT COUNT(*) FROM T2;

COUNT(*)
----------
167011


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 2729
T2 xcur 4829
IND_T_NAME xcur 538

先对T表的索引IND_T_NAME的扫描，后对T2表进行扫描，将这两部分数据装载到KEEP池中。

SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 200000;

COUNT(*)
----------
199999


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6543 consistent gets
6544 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 6544
T2 xcur 1552

通过限制一定数量的T的扫描，发现Oracle首先将IND_T_NAME的缓存替换出去，然后才是T2的缓存。这个符合KEEP池的先进先出的特点。这里不用T的全扫描是为了避免将T2和IND_T_NAME全部替换出去，导致无法观察到替换的先后顺序。

SQL> SELECT COUNT(*) FROM T2;

COUNT(*)
----------
167011


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 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 T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9656 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

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

下面再次利用T的全扫描“清空”KEEP池。

SQL> SELECT /*+ INDEX(T) */ COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538

SQL> SELECT COUNT(*) FROM T2;

COUNT(*)
----------
167011


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 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 /*+ INDEX(T) */ COUNT(*) FROM T;

COUNT(*)
----------
334022


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 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

SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 200000;

COUNT(*)
----------
199999


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6543 consistent gets
6544 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 6544
T2 xcur 1014
IND_T_NAME xcur 538

仍然采用先索引IND_T_NAME扫描后T2扫描的顺序，不同的是，在将T2装载入KEEP池之后，又对索引IND_T_NAME进行了一次扫描。

这次执行T的扫描可以发现，首先被替换出去的是T2的缓存。这说明了KEEP池同样也考虑了LRU算法。不过这种算法相对于DEFAULT池要简单一些。

转自：http://space.itpub.net/8183550/viewspace-667046