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

KEEP池的CACHE特点（二）

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

【IT168 技术文档】

上一篇文章介绍了KEEP池和DEFAULT池的差异之处，下面通过例子进行详细的说明：

首先，将T表的容量扩大1倍，这样，即使是单独查询T表，所有的数据也无法全部放入KEEP池。

SQL> INSERT INTO T SELECT * FROM T;
167011 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8097
SQL> SELECT SUM(BLOCKS) FROM USER_EXTENTS WHERE SEGMENT_NAME = 'T';
SUM(BLOCKS)
-----------
9728

可以看到T表一共是9728个BLOCK，而KEEP池中仅仅缓存了8097个。

SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_block_size integer 16384

KEEP池的大小是128M，除以数据块的大小16K，KEEP池中可以存放的BLOCK上限就是8K个。现在KEEP池已经装满，但是仍然有部分数据被替换到KEEP池外。

下面说明KEEP池缓存的特点，先看一下查询的结果：

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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3268
T2 xcur 4829
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

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

关注我们