KEEP池的CACHE特点（四）-技术开发专区

KEEP池的CACHE特点（四）

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

【IT168 技术文档】

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池要简单一些

关注我们