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@]

oracle cache table(1)

相關文章