Buffer Cache（緩衝區快取）篇：keep pool（保留池）

Buffer  Cache可以有三個池

預設緩衝區池

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);

轉自：http://blog.csdn.net/laoshangxyc/article/details/11732451

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1324314/，如需轉載，請註明出處，否則將追究法律責任。