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

KEEP池的CACHE特点（一）

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

【IT168 技术文档】

对象放入KEEP池后，数据的CACHE特性和DEFAULT池是有一些区别的。

KEEP池的使用十分简单，设置DB_KEEP_CACHE_SIZE的值大于0，就可以将其他对象的BUFFER_POOL参数设置为KEEP了。

SQL> SHOW PARAMETER DB%CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 956301312
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> SELECT 128*1024*1024 FROM DUAL;
128*1024*1024
-------------
134217728
SQL> SELECT 956301312-134217728 FROM DUAL;
956301312-134217728
-------------------
822083584
SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 822083584;
System altered.
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 134217728;
System altered.

创建测试用表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> SELECT SUM(BYTES)/1024/1024 M FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T';
M
----------
80
SQL> ALTER TABLE T STORAGE (BUFFER_POOL KEEP);
Table altered.
SQL> SET AUTOT ON STAT
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
167011

Statistics
----------------------------------------------------------
107 recursive calls
0 db block gets
4849 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
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*) FROM T;
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

现在看到，KEEP池已经生效，T的数据已经缓存在KEEP池中。

下面再创建一个同样大小的表：

SQL> CREATE TABLE T2 STORAGE (BUFFER_POOL KEEP) AS SELECT * FROM DBA_SOURCE;
Table created.
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 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

两张表的大小合在一起已经超过了KEEP池的大小。下面看看现在内存中缓存的情况：

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

T2中的数据已经全部放入KEEP池，而部分T中的数据被替换出去。

下面用执行对T的查询，尝试缓存T的数据：

SQL> SELECT COUNT(*) FROM T;
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

这时可以观察到一个有趣的现象，对T执行扫描的时候，几乎全部都是物理读，而我们刚刚看到内容中仍然有3000多个数据块在缓存中。

这就是KEEP池与DEFAULT池算法的差异之处

关注我们