Oracle KEEP池的CACHE特点
今天看了yangtingkun很多文章，相当的不错。
下面摘录关于KEEP池的文章：
本人在ORACLE11GR2上，也测试了一遍。
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