The KEEP buffer pool retains the schema object’s data blocks in memory.
The RECYCLE buffer pool eliminates data blocks from memory as soon as they
are no longer needed.
The DEFAULT buffer pool contains data blocks from schema objects that are not
assigned to any buffer pool, as well as schema objects that are explicitly
assigned to the DEFAULT pool.
The initialization parameters that configure the KEEP and RECYCLE buffer pools are
DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE.

对象放入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池中。[@more@]

ITPub博客

oracle cache table(1)

最新文章