【IT168 技术文档】
RECYCLE池的CACHE算法与DEFAULT池和KEEP池都不相同。
RECYCLE池的设置也很简单，设置DB_RECYCLE_CACHE_SIZE的值大于0，再将对象的BUFFER_POOL参数设置为RECYCLE就可以了。
SQL> SHOW PARAMETER DB_%E%_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_keep_cache_size big integer 134217728
db_recycle_cache_size big integer 0
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 134217728;
System altered.
创建测试用表：
SQL> CREATE TABLE T STORAGE(BUFFER_POOL RECYCLE) AS
2 SELECT * FROM DBA_SOURCE
3 UNION ALL
4 SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T2 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> COL SEGMENT_NAME FORMAT A30
SQL> SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 M
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME IN ('T', 'T2', 'T3')
4 GROUP BY SEGMENT_NAME;
SEGMENT_NAME M
------------------------------ ----------
T 152
T2 80
T3 80
T表的大小超过RECYCLE池的大小，而T2和T3的大小都小于RECYCLE的大小。
下面看看RECYCLE池的CACHE特点：
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 1
T2 xcur 1
T3 xcur 1
SQL> SET AUTOT ON STAT
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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 1
T2 xcur 4829
T3 xcur 1
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 1
T2 xcur 4829
T3 xcur 3266
首先，将T2表放入CACHE中，然后放入T3表，下面观察RECYCLE池的CACHE特点：
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> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
1563 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 1
T2 xcur 4829
T3 xcur 3266
SQL> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
1563 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
可以看出，RECYCLE池的特点和KEEP池以及DEFAULT又都不相同。对于首先放到CACHE中的T2表不会被替换出去，而T3表也有部分数据被CACHE。而且，通过观察对T3的查询，发现每次的物理读都是相同的，说明T3中CACHE数据的大小也是稳定的。
由于T2和T3是以完全相同的方法创建的，因此，二者应该拥有相同的BLOCK数。这里可以发现一个有趣的关系。由于T2是全部缓存，说明T2拥有4829个BLOCK，那么T3也应该拥有4829个BLOCK。用4829减去缓存的T3缓存的3266，结果就是查询T3时的物理读数。
SQL> SELECT 4829-3266 FROM DUAL;
4829-3266
----------
1563
这说明一个查询中，如果RECYCLE池被装满，则不会将原有的缓存替换出去，最新得到数据不会放入到缓存中。
下面里面表空间脱机的方法清掉RECYCLE池，按照T3在前T2在后的顺序进行测试：
SQL> ALTER TABLESPACE NDMAIN OFFLINE;
Tablespace altered.
SQL> ALTER TABLESPACE NDMAIN ONLINE;
Tablespace altered.
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 free 1
T2 free 4829
T3 free 3266
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 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(*)
------------------------------ ----- ----------
T2 xcur 3267
T3 xcur 4829
由于T3先放到RECYCLE池中，T3的数据全部被CACHE，而T2表被部分CACHE。
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
1562 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
1562 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(*)
------------------------------ ----- ----------
T2 xcur 3267
T3 xcur 4829
