RECYCLE池的CACHE特点（一）-技术开发专区

RECYCLE池的CACHE特点（一）

作者：yangtingkun 编辑：孙敏杰 2006-05-27 00:00

首先，将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在后的顺序进行测试：

第1页：之一第2页：之二第3页：之三

关注我们