Keep小表到内存,提高访问速度

俩小表，频繁更新，老出现等待

干脆keep到内存里，避免物理读，提高性能

使用到参数buffer_pool_keep

Oracle的高速缓冲区其实可以分成三个不同的子池

默认池

Keep池

Recycle池

Keep中可以保留经常使用的数据，使其不被老化调，提高命中率；recycle相反。默认数据存放在默认池中

数据库版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE    9.2.0.8.0       Production

TNS for Solaris: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

Sga分配，Variable Size有足够空间

SQL> show sga

Total System Global Area 2635566408 bytes

Fixed Size                   732488 bytes

Variable Size             704643072 bytes

Database Buffers         1929379840 bytes

Redo Buffers                 811008 bytes

Keep池已经分配

SQL> show parameter keep

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_keep                     string

control_file_record_keep_time        integer     7

db_keep_cache_size                   big integer 33554432

keep表到内存中

alter table tbl_a storage (buffer_pool keep);

alter table tbl_b storage (buffer_pool keep);

keep索引到内存中

 alter index IND_AA storage (buffer_pool keep);

查询被存在于keep池中的表

SELECT BUFFER_POOL,table_name FROM DBA_Tables where BUFFER_POOL<>'DEFAULT';

BUFFER_ TABLE_NAME

------- ------------------------------

KEEP    TBL_A

KEEP    TBL_B