【IT168 技术文档】
继续前面的测试，这回测试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> 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 T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9667 consistent gets
1561 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
9667 consistent gets
1561 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表已经CACHE在RECYCLE中了，虽然T表比缓冲区大，但是由于RECYCLE的算法，保证大于缓存区大小的数据不会被缓存，因此，保证每次读取T表都会产生相同的物理读。
下面在缓存区满的情况下读取T2表到内存中：
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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8079
T2 xcur 17
可以发现，对于缓存区中不存在的表，Oracle是要将其放入到缓冲区的，这时候要把缓存中最后缓存的BLOCK替换出去。至于为什么是17个BLOCK，后面详细解释。
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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8078
T2 xcur 1
T3 xcur 17
在把T3表也放入到缓存中，同样Oracle发现T3没有在缓冲中，因此替换了最后进入到缓冲区的T2的16个BLOCK，并替换了T表最后被缓存的一个BLOCK。
Oracle这里之所以没有将T2表全部替换出去，就是为了在缓存中保留一份曾经进入过缓存的表的记录。而1个BLOCK是最小的保留数据。
回忆上一篇文章第一个例子中，T表的1个BLOCK始终没有被替换出去就是这个原因。
SQL> SELECT 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
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 8078
T2 xcur 1
T3 xcur 17
