Buffer Cache(缓冲区缓存)篇:keep pool(保留池)
Buffer Cache(缓冲区缓存)篇:keep pool(保留池)
默认缓冲区池
keep缓冲区池
recycle缓冲区池
--保留池和回收池可以独立于sga中的其他缓存分配内存。创建表的时候可以在storage子句中使用buffer_pool keep和buffer_pool recyle子句来指定表的数据块将要驻留的池。
keep缓冲区池
当数据读取到kepp缓冲区池后,会一直保留在内存中,不会被刷出内存,比如整天使用频繁的表,有利的做法是讲其放在keep缓冲池中,以减少IO数量。
keep池的大小由db_keep_cache_size决定,默认值为0
示例:
Setp1 设置db_keep_cache_size大小 SQL> alter system set db_keep_cache_size=32M scope=both; System altered. SQL> show parameter keep NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_keep string control_file_record_keep_time integer 7 db_keep_cache_size big integer 32M /*--注buffer_pool_keep,8i参数,意义和db_keep_cache_size一样 增大db_keep_cache_size会相应减小db_cache_size,可以通过下面方式查看db_cache_size SQL> select * from v$sgainfo; Fixed SGA Size 2217992 No Redo Buffers 2396160 No Buffer Cache Size 255852544 Yes Shared Pool Size 230686720 Yes Large Pool Size 4194304 Yes Java Pool Size 4194304 Yes Streams Pool Size 0 Yes Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 839282688 No Startup overhead in Shared Pool 67108864 No Free SGA Memory Available 339738624 或 SELECT x.ksppinm NAME,y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%__db_cache_size%'; SQL> SELECT x.ksppinm NAME,y.ksppstvl VALUE, x.ksppdesc describ 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%__db_cache_size%'; NAME VALUE DESCRIB ------------------------------ ------------------------------ -------------------------------------------------------------------------------- __db_cache_size 255852544 Actual size of DEFAULT buffer pool for standard block size buffers */ Setp2 将表keep到keep池中 方式1: SQL> create table test_keep1 (id int,name varchar2(32)); Table created. SQL> alter table test_keep1 storage(buffer_pool keep); Table altered. 方式2: SQL> create table test_keep2 (id int,name varchar2(32)) storage(buffer_pool keep); Table created. Step3 查看放入keep池中的对象 SQL> select segment_name from dba_segments where BUFFER_POOL = 'KEEP'; TEST_KEEP1 TEST_KEEP2 Setp4 将表加载到keep池中 SQL> select * from test_keep1; Setp5 检测IO情况 SET AUTOTRACE ON STATISTICS select * from test_keep1; Setp6 取消keep alter table test_keep1 storage(buffer_pool default);