【IT168 技术文档】
前面讨论了RECYCLE池的CACHE特点，这篇文章简单描述一下，如果在RECYCLE池中设置了CACHE选项，会产生什么效果。
不过，这种情况一般并不常见，对于放到RECYCLE池的对象，本来就是认为缓存在以后很少或不再使用。所以，很少会对RECYCLE池中的对象设置CACHE选项。不过如果希望对RECYCLE池中的对象再设置优先级的话，CACHE选项还是有点作用的。
首先，清掉RECYCLE池。
SQL> ALTER TABLESPACE NDMAIN OFFLINE;
Tablespace altered.
SQL> ALTER TABLESPACE NDMAIN ONLINE;
Tablespace altered.
利用T表将RECYCLE填满：
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9667 consistent gets
9657 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> COL OBJECT_NAME FORMAT A30
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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
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', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8079
T2 xcur 17
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 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8079
T2 xcur 17
对于前面提到的RECYCLE池的算法，T2的数据不会进入到缓冲区内。
下面将T2表设置为CACHE：
SQL> ALTER TABLE T2 CACHE;
Table altered.
SQL> SELECT COUNT(*) FROM T2;
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 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3267
T2 xcur 4829
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
T2表完全的放到了缓冲区中，在RECYCLE池中设置了CACHE选项则拥有了高的优先级，T表中数据被替换出CACHE池。
SQL> SELECT COUNT(*) FROM T3;
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', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3250
T2 xcur 4829
T3 xcur 17
SQL> SELECT /*+ CACHE(T3) */ COUNT(*) FROM T3;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4840 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', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3250
T2 xcur 4829
T3 xcur 17
SQL> SELECT /*+ CACHE(T3) */ COUNT(*) FROM T3;
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
虽然表的CACHE选项可以起很大的作用，但是对于CACHE提示，仍然不起作用。看了CACHE提示似乎被Oracle废弃掉了。
上一篇文章已经提到，索引扫描产生的BLOCK也会替换掉缓冲区中的其他内容。索引扫描应该和设置了CACHE选项的表二者的特点十分相似。
SQL> SELECT /*+ INDEX(T) */ COUNT(TEXT) FROM T;
COUNT(TEXT)
-----------
333442
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13896 consistent gets
9857 physical reads
0 redo size
384 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', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7653
T2 xcur 1
T3 xcur 1
IND_T_NAME xcur 441
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', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3101
T2 xcur 4829
T3 xcur 1
IND_T_NAME xcur 165
显然，二者都没有遵守RECYCLE池先进入CACHE的内存不会被替换出去的特点，而是采用了完全替换的策略。
或者，换一种说法更恰当。RECYCLE主要是针对NOCACHE的表，而且RECYCLE池的特点也并不是先放入CACHE的数据不会被替换掉，而是在CACHE满了之后，后面读取的数据根本不会向CACHE中放。这种说法似乎更符合RECYCLE的含义。
而对于CACHE的表和索引扫描，RECYCLE池的策略似乎和KEEP池很接近。看下面的例子，基本上完全符合KEEP池的缓存特点。
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
SQL> ALTER TABLE T3 CACHE;
Table altered.
SQL> SELECT COUNT(*) FROM T3;
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 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T2 xcur 3267
T3 xcur 4829
SQL> SELECT COUNT(*) FROM T3;
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
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
