一. Recycle Pool 说明
在我之前的Blog里对DB buffer 进行了一个说明，参考：
Oracle Buffer Cache 原理
http://www.cndba.cn/Dave/article/1398
Oracle Buffer Cache 中 Keep Pool 说明
http://www.cndba.cn/Dave/article/1395
Oracle 的buffer cache 由三个部分组成： default pool，keep pool 和Recycle pool. 每个Pool 都有自己的LRU来管理.
（1）The default pool is for everythingelse.
（2）The recycle pool is for larger objects.
（3）The keep pool's purpose is to takesmall objects that should always be cached, for example Look Up Tables.
1.1 相关理论知识
1.1.1 BUFFER_POOL
The BUFFER_POOL clause letsyou specify a default buffer pool (cache) for aschema object. All blocks for the object are stored in the specified cache.
-- 缓冲池子句可指定一个数据库对象的默认缓冲池。这个对象的所有数据块存储在指定的缓存中。
If you define a buffer poolfor a partitioned table or index, then the partitions inherit the buffer poolfrom the table or index definition, unless overridden by a partition-leveldefinition.
--如果给一个分区表或索引指定了缓冲池，那么该表或索引的分区也同样使用指定的缓冲池，除非在分区的定义中指定分区使用的缓冲池。
For an index-organized table,you can specify a buffer pool separately for the index segment and the overflowsegment.
--对于一个索引组织表，可以为索引段和溢出段分别指定缓冲池。
（1）Restrictions on BUFFER_POOL
You cannot specify this clausefor a cluster table. However, you can specify it for a cluster.
--不能在聚集表上指定缓冲池，但是，可以在一个聚集上指定缓冲池。
You cannot specify this clausefor a tablespace or for a rollback segment.
--不能在表空间或回滚段上指定缓冲池。
（2）KEEP
Specify KEEP to put blocksfrom the segment into the KEEP buffer pool. Maintaining an appropriately sizedKEEP buffer pool lets Oracle retain the schema object in memory to avoid I/Ooperations. KEEP takes precedence over any NOCACHEclause you specify for a table, cluster, materialized view, ormaterialized view log.
--指定KEEP将把数据块放入KEEP缓冲池中。维护一个适当尺寸的KEEP缓冲池可以使Oracle在内存中保留数据库对象而避免I/O操作。在表、聚集、实体化视图或实体化视图日志上，KEEP子句的优先权大于NOCACHE子句。
（3）RECYCLE
Specify RECYCLE to put blocksfrom the segment into the RECYCLE pool. An appropriately sized RECYCLE poolreduces the number of objects whose default pool is the RECYCLE pool fromtaking up unnecessary cache space.
--指定RECYCLE将把数据块放入RECYCLE缓冲池中。一个适当尺寸的RECYCLE缓冲池可以减少默认缓冲池为RECYCLE缓冲池的数据库对象的数量，以避免它们占用不必要的缓冲空间。
（4） DEFAULT
Specify DEFAULT to indicatethe default buffer pool. This is the default for objects not assigned to KEEPor RECYCLE.
--指定DEFAULT将适用默认的缓冲池。这个选项适用于没有分配给KEEP缓冲池和RECYCLE缓冲池的其它数据库对象。
（5）bufferpool 说明
在没有多个缓冲池的数据库中，所有的数据库对象使用同样的缓冲池，这样就会形成一种情况:
当希望某个频繁使用的数据库对象一直保留在缓冲池中时，一个大的、不经常使用的数据库对象会把它“挤”出缓冲池。这样就会降低缓冲池的效率，增加额外的I/O操作。
使用多个缓冲池后，可以更精确的调整缓冲池的使用，频繁使用数据库对象的缓冲池放在KEEP缓冲池中，大的、不经常使用的数据库对象放在RECYCLE缓冲池中，其它的数据库对象放在DEFAULT缓冲池中。
1.1.2 CACHE |NOCACHE | CACHE READS
Use the CACHE clauses toindicate how Oracle should store blocks in the buffer cache. If you specifyneither CACHE nor NOCACHE:
--使用CACHE子句可制定Oracle在缓冲中如何存贮数据块。如果没有指定CACHE或NOCACHE：
In aCREATE TABLE statement, NOCACHE is the default
In an ALTER TABLE statement, the existing value is not changed.
--在CREATE TABLE语句中，默认为NOCACHE。
--在ALTERTABLE语句中，不会改变当前表的CACHE/NOCACHE值。
（1）CACHEClause
For data that is accessedfrequently, this clause indicates that the blocks retrieved for this table areplaced at the most recently used end of the leastrecently used (LRU) list in the buffer cache when a full table scan isperformed. This attribute is useful for small lookup tables.
--对于那些访问频繁的数据，这个子句可以指定当执行一个全表扫描时，将从表中获取的数据块放在缓冲中LRU列表的"最新使用"的一端。这个属性对小的查找表有用。
As a parameter in theLOB_storage_clause, CACHE specifies that Oracle places LOB data values in thebuffer cache for faster access.
--作为LOB存储子句的一个参数，CACHE可指定Oracle将LOB数据放在缓冲中，以便访问得更快。
（2）Restrictionon CACHE
You cannot specify CACHE foran index-organized table. However, index-organized tables implicitly provideCACHE behavior.
--不能在索引组织表上使用CACHE。但是，索引组织表隐式的提供了CACHE的效果。
（3）NOCACHEClause
For data that is not accessedfrequently, this clause indicates that the blocks retrieved for this table areplaced at the least recently used end of the LRU listin the buffer cache when a full table scan is performed.
--对于那些访问不频繁的数据，这个子句可以指定当执行一个全表扫描时，将从表中获取的数据块放在缓冲中LRU列表的“最久使用”的一端。
As a parameter in theLOB_storage_clause, NOCACHE specifies that the LOB value is either not broughtinto the buffer cache or brought into the buffer cache and placed at the leastrecently used end of the LRU list. (The latter is the default behavior.)
--作为LOB存储子句的一个参数，NOCACHE可指定Oracle将LOB数据不放在缓冲中或者放在缓冲中LRU列表的“最久使用”的一端。（后者是默认的做法。）
（4）Restrictionon NOCACHE
You cannot specify NOCACHE forindex-organized tables.
--不能在索引组织表上使用CACHE。
（5）CACHEREADS
CACHE READS applies only toLOB storage. It specifies that LOB values are brought into the buffer cacheonly during read operations, but not during write operations.
--CACHEREADS只适用于LOB存储。它指定当进行读操作时，将LOB放在缓冲中，而写操作时不这样做。
（6）说明
当BUFFER_POOL和CACHE同时使用时，KEEP比NOCACHE有优先权。
BUFFER_POOL用来指定存贮的缓冲池，而CACHE/NOCACHE指定存储的方式。
1.2 官网说明
It is possible to configure aRECYCLE buffer pool for blocks belonging to those segments that you do not wantto remain in memory. The RECYCLE pool is good forsegments that are scanned rarely or are not referenced frequently. If anapplication accesses the blocks of a very large object in a random fashion,then there is little chance of reusing a block stored in the buffer pool beforeit is aged out. This is true regardless of the size of the buffer pool (giventhe constraint of the amount of available physical memory). Consequently, theobject's blocks need not be cached; those cache buffers can be allocated toother objects.
Memory is allocated to the RECYCLEbuffer pool by setting the parameter DB_RECYCLE_CACHE_SIZE to the required size. This memoryfor the RECYCLE buffer pool is not a subset of the default pool.
Do not discard blocks from memorytoo quickly. If the buffer pool is too small, then blocks can age out of thecache before the transaction or SQL statement has completed execution. For example,an application might select a value from a table, use the value to process somedata, and then update the record. If the block is removed from the cache afterthe SELECT statement, then it must be read from disk again to perform theupdate. The block should be retained for the duration of the user transaction.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94285
1.3 说明
Recycle Pool用于存储临时使用的、不被经常使用的较大的对象，这些对象放置在Default Buffer Pool显然是不合适的，这些块会导致过量的缓冲区刷新输出，而且不会带来任何好处，因为等你想要再用这个块时，它可已经老化退出了缓存。要把这些段与默认池和保持池中的段分开，这样就不会导致默认池和保持池中的块老化而退出缓存。
对于Recycle Pool，先装载的表保留在RECYCLE池中。RECYCLE池满了之后，读取的数据不被缓存。读取没有进入过RECYCLE池的表，会替换掉最后进入RECYCLE池的很少的BLOCK，而且，缓存的BLOCK也可能会很快被替换出去，但是最少会保留1个BLOCK在缓存中。
和全表扫描相比，索引扫描产生的CACHE被缓冲的优先级更高。
二. 相关测试
--查看db_recyle_cache_size
SYS@anqing2(rac2)>show parameter db_recycle_cache_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_recycle_cache_size big integer 0
--查看db_cache_size大小
SYS@anqing2(rac2)>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%';
NAME VALUE DESCRIB
------------------------------ ------------------------------------------------
__db_cache_size50331648 Actual size of DEFAULT buffer pool forstandard
-- 查看keeppool 大小
SYS@anqing2(rac2)>show parameter db_keep_cache_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_keep_cache_size big integer 100M
--释放keeppool空间，然后分配给recycle pool
SYS@anqing2(rac2)>alter system set db_keep_cache_size=0 scope=both sid='anqing2';
Systemaltered.
SYS@anqing2(rac2)>SELECT x.ksppinm NAME,y.ksppstvl VALUE, x.ksppdescdescrib FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinmLIKE '%__db_cache_size%';
NAME VALUE DESCRIB
------------------------------ ------------------------------------------------
__db_cache_size155189248 Actual size of DEFAULT buffer pool forstandard
SYS@anqing2(rac2)>alter system set db_recycle_cache_size=15M scope=bothsid='anqing2';
Systemaltered.
SYS@anqing2(rac2)>show parameter db_recycle_cache_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_recycle_cache_size big integer 16M
SYS@anqing2(rac2)>SELECT x.ksppinm NAME,y.ksppstvl VALUE, x.ksppdescdescrib FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinmLIKE '%__db_cache_size%';
NAME VALUE DESCRIB
------------------------------ ------------------------------------------------
__db_cache_size138412032 Actual size of DEFAULT buffer pool forstandard
-- 当增加keep pool 或者 recycle pool 时，db_cache_size就会减小
--创建table，并放入recycle pool
SYS@anqing2(rac2)>create table r1 storage(buffer_pool recycle) as select * fromdba_objects;
Tablecreated.
SYS@anqing2(rac2)> create table r2 as select * from dba_objects;
Tablecreated.
SYS@anqing2(rac2)>alter table r2 storage(buffer_pool recycle);
Tablealtered.
--查看recyclepool中的对象
SYS@anqing2(rac2)> select segment_name,blocks,bytes/1024/1024||'M'AS "SIZE" from dba_segments where BUFFER_POOL = 'RECYCLE';
SEGMENT_NAME BLOCKS SIZE
----------------------- -----------------------------------------
R1 768 6M
R2 768 6M
--查看v$bh 视图
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHERE A.OBJD = B.OBJECT_ID AND OBJECT_NAME IN('R1', 'R2') GROUP BY OBJECT_NAME,A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 xcur 1
R1 xcur 1
在我的Blog里：
OracleBuffer Cache 原理
http://www.cndba.cn/Dave/article/1398
有介绍过这个视图，Information on buffer headers. Contains a record(the buffer header) for each block in the buffer cache. 该视图的每一条记录就是一个block。
这里看到，对r1和R2 都有1个block的cache。 这个是cache的最小单位。 即所有cache的table，至少会保留一个block，用来标记相关的信息。
--全表扫描R1，让R1 加载进Cache。
SYS@anqing2(rac2)>set autot on stat
SYS@anqing2(rac2)>select count(*) from r1;
COUNT(*)
----------
50269
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
786 consistent gets
690 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--再次select，因为之前已经缓存过，所以现在不会有物理读
SYS@anqing2(rac2)>select count(*) from r1;
COUNT(*)
----------
50269
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
0 physical reads --物理读为0
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--查看v$bh 的缓存情况
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 xcur 1
R1 xcur 1
R1 scur 690
XCUR：a current mode block, exclusive to this instance
SCUR：a current mode block, shared with other instances
注意：
690+1=691 个blocks。 与之前查看的768个blocks 有出入。
--我们对表R1 进行分析，在查看实际使用的Blocks
SYS@anqing2(rac2)> exec dbms_stats.gather_table_stats('SYS','R1');
PL/SQLprocedure successfully completed.
--dba_tables 中的blocks必须在分析之后才有值
SYS@anqing2(rac2)>select table_name,blocks from dba_tables where table_name='R1';
TABLE_NAME BLOCKS
----------------------------------------
R1 690
--注意这里也是690.这个是我们实际使用的blocks数量，而我们通过dba_segmetns 表查看出来的是所有分配的blocks数量。
-- 从这个结论，也可以推断出，当我们把objects keep 到对应pool 之后，分配的1个block 只是一个标记blocks，并不是我们真正的数据。
--我们个Recycle pool 是15M，但系统分配了16M，R1和R2 表都是6M大，之前已经把R1，R2 keep 进去了，但R2的数据并没有刷到Recycle Pool。
SYS@anqing2(rac2)> select count(*) from r2;
COUNT(*)
----------
50270
Statistics
----------------------------------------------------------
151 recursive calls
0 db block gets
793 consistent gets
690 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>select count(*) from r2;
COUNT(*)
----------
50270
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
0 physical reads --第二次select 物理读为0
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--查看v$bh缓存情况
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2','R3') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R2 xcur 1
R1 scur 691
--R1 和 R2 都缓存进去了，Recycle Pool 也满了
--创建tableR3，也缓存到Recycle Pool
SYS@anqing2(rac2)>create table r3 storage(buffer_pool recycle) asselect * from dba_objects;
Tablecreated.
SYS@anqing2(rac2)>select segment_name,blocks,bytes/1024/1024||'M' AS "SIZE" fromdba_segments where BUFFER_POOL = 'RECYCLE';
SEGMENT_NAME BLOCKS SIZE
----------------------- -----------------------------------------
R1 768 6M
R2 768 6M
R3 768 6M
--查看V$bh
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R2 xcur 1
R1 scur 691
R3 xcur 1
--我们select一下，看能cache 多少blocks 进来
SYS@anqing2(rac2)>select count(*) from r3;
COUNT(*)
----------
50271
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
766 consistent gets
690 physical reads --第一次物理读了690个blocks
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>/
COUNT(*)
----------
50271
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
0 physical reads --第二次没有物理读
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--查看V$bh 视图
SYS@anqing2(rac2)>SELECTOBJECT_NAME, A.STATUS, COUNT(*) FROMV$BH A, USER_OBJECTS B WHERE A.OBJD =B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2','R3') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R3 xcur 1
R3 scur 690
R2 xcur 1
R1 scur 691
-- R3 也cache 进来了，
--查看recyclepool 大小
SYS@anqing2(rac2)>show parameter db_recycle_cache_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_recycle_cache_size big integer 16M
--三个表数据全部cache进去了，但是3*6M，需要18M的空间，肯定是放不下的，在次查看一下：
SYS@anqing2(rac2)>SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) ||'M'Total_Size
2 FROM ( SELECT o.OBJECT_NAME,COUNT (*)NUMBER_OF_BLOCKS
3 FROM DBA_OBJECTS o, V$BHbh,dba_segments dd
4 WHERE o.DATA_OBJECT_ID=bh.OBJD
5 AND o.OWNER = dd.owner
6 AND dd.segment_name=o.OBJECT_NAME
7 AND dd.buffer_pool= 'RECYCLE'
8 GROUP BY o.OBJECT_NAME
9 ORDER BY COUNT (*)) b;
/
TOTAL_SIZE
-----------
16.07M
实际也使用了16M的空间。 在keep 一个大表进去
SYS@anqing2(rac2)>alter table rb_test storage(buffer_pool recycle);
Tablealtered.
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2','R3','RB_TEST') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R3 xcur 1
R3 scur 690
R2 xcur 1
RB_TEST scur 597
R1 scur 691
6 rowsselected.
SYS@anqing2(rac2)>SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) ||'M'Total_Size
2 FROM ( SELECT o.OBJECT_NAME,COUNT (*)NUMBER_OF_BLOCKS
3 FROM DBA_OBJECTS o, V$BH bh,dba_segments dd
4 WHERE o.DATA_OBJECT_ID=bh.OBJD
5 AND o.OWNER = dd.owner
6 AND dd.segment_name=o.OBJECT_NAME
7 AND dd.buffer_pool ='RECYCLE'
8 GROUP BY o.OBJECT_NAME
9 ORDER BY COUNT (*)) b;
/
TOTAL_SIZE
-----------
20.69M
SYS@anqing2(rac2)>select count(*) from rb_test;
COUNT(*)
----------
4000000
Statistics
----------------------------------------------------------
127 recursive calls
0 db block gets
11047 consistent gets
10416 physical reads --第一次会产生物理读
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHERE A.OBJD = B.OBJECT_ID AND OBJECT_NAME IN('R1', 'R2','R3','RB_TEST') GROUP BYOBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R3 xcur 1
R3 scur 690
R2 xcur 1
RB_TEST scur 10845
R1 scur 691
6 rowsselected.
SYS@anqing2(rac2)>SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) ||'M'Total_Size
2 FROM ( SELECT o.OBJECT_NAME,COUNT (*)NUMBER_OF_BLOCKS
3 FROM DBA_OBJECTS o, V$BH bh,dba_segments dd
4 WHERE o.DATA_OBJECT_ID=bh.OBJD
5 AND o.OWNER = dd.owner
6 AND dd.segment_name=o.OBJECT_NAME
7 AND dd.buffer_pool ='RECYCLE'
8 GROUP BY o.OBJECT_NAME
9 ORDER BY COUNT (*)) b;
/
TOTAL_SIZE
-----------
100.1M
SYS@anqing2(rac2)>select count(*) from rb_test;
COUNT(*)
----------
4000000
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11029 consistent gets
543 physical reads --第二次select ，有少量物理读
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2','R3','RB_TEST') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R3 xcur 1
R3 scur 690
R2 xcur 1
RB_TEST scur 11019
R1 scur 691
6 rowsselected.
SYS@anqing2(rac2)> select count(*) fromrb_test;
COUNT(*)
----------
4000000
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11029 consistent gets
0 physical reads --第三次select，没有物理读
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>SELECT OBJECT_NAME, A.STATUS, COUNT(*) FROM V$BH A, USER_OBJECTS B WHEREA.OBJD = B.OBJECT_ID AND OBJECT_NAME IN ('R1', 'R2','R3','RB_TEST') GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATUS COUNT(*)
-------------------- ----------
R2 scur 690
R3 xcur 1
R3 scur 690
R2 xcur 1
RB_TEST scur 11019
R1 scur 691
6 rowsselected.
SYS@anqing2(rac2)>SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) ||'M'Total_Size
2 FROM ( SELECT o.OBJECT_NAME,COUNT (*)NUMBER_OF_BLOCKS
3 FROM DBA_OBJECTS o, V$BH bh,dba_segments dd
4 WHERE o.DATA_OBJECT_ID=bh.OBJD
5 AND o.OWNER = dd.owner
6 AND dd.segment_name=o.OBJECT_NAME
7 AND dd.buffer_pool ='RECYCLE'
8 GROUP BY o.OBJECT_NAME
9 ORDER BY COUNT (*)) b;
/
TOTAL_SIZE
-----------
101.4M
SYS@anqing2(rac2)>select segment_name,blocks,bytes/1024/1024||'M' AS "SIZE" fromdba_segments where BUFFER_POOL = 'RECYCLE';
SEGMENT_NAME BLOCKS SIZE
----------------------- -----------------------------------------
R1 768 6M
R2 768 6M
R3 768 6M
RB_TEST 11264 88M
SYS@anqing2(rac2)> exec dbms_stats.gather_table_stats('SYS','RB_TEST');
PL/SQLprocedure successfully completed.
SYS@anqing2(rac2)>select table_name,blocks from dba_tables wheretable_name='RB_TEST';
TABLE_NAME BLOCKS
----------------------------------------
RB_TEST 11018
--可以看到RB_TEST表的所有数据块也Cache到Recycle里了。
--注意： 这里虽然Recyle Pool 变大了，但是db_cache_size 还是没有变
SYS@anqing2(rac2)>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%';
NAME VALUE DESCRIB
------------------------------ ------------------------------------------------
__db_cache_size138412032 Actual size of DEFAULTbuffer pool for standard
在测试Keep Pool 的时候，放我们执行SQL>alter system flushbuffer_cache;
会flush 掉keep Pool里的内容，现在我们flush 一下看看，验证下recycle pool里的内容能否flush 掉。
SYS@anqing2(rac2)>alter system flush buffer_cache;
Systemaltered.
SYS@anqing2(rac2)>select count(*) from r1;
COUNT(*)
----------
50269
Statistics
----------------------------------------------------------
124 recursive calls
0 db block gets
705 consistent gets
695 physical reads --所以，flush 也会清空recycle pool的内容
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>select count(*) from r2;
COUNT(*)
----------
50270
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
691 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@anqing2(rac2)>col name for a40
SYS@anqing2(rac2)>select * from v$sgainfo;
NAME BYTESRES
-------------------------------------------------- ---
FixedSGA Size 1267068 No
RedoBuffers 2924544 No
Buffer Cache Size 159383552 Yes
Shared Pool Size 109051904 Yes
LargePool Size 4194304 Yes
JavaPool Size 4194304 Yes
StreamsPool Size 4194304 Yes
GranuleSize 4194304 No
MaximumSGA Size 285212672 No
Startupoverhead in Shared Pool 46137344 No
FreeSGA Memory Available 0
11rows selected.
SYS@anqing2(rac2)> create table r4 storage(buffer_pool recycle) as select *from dba_objects;
Tablecreated.
SYS@anqing2(rac2)>select count(*) from r4;
SYS@anqing2(rac2)>select * from v$sgainfo;
NAME BYTES RES
------------------------------------------ ---
FixedSGA Size 1267068 No
RedoBuffers 2924544 No
Buffer Cache Size 159383552 Yes
Shared Pool Size 109051904 Yes
LargePool Size 4194304 Yes
JavaPool Size 4194304Yes
StreamsPool Size 4194304 Yes
GranuleSize 4194304 No
MaximumSGA Size 285212672 No
Startupoverhead in Shared Pool 46137344 No
FreeSGA Memory Available 0
11rows selected.
--通过以上测试，当Recycle Pool 增大的时候，DB buffer，shared Pool 大小并没有改变，所以在没有设置keep pool的情况下，Recycle pool 增大的内存来自与Defalut pool 减小的空间，而空间的增大和减小由ASMM 自动调整。
小结：
通过以上的测试，可以观察到，虽然我们的db_recycle_cache_size 只有16M，但是我们数据还是可以cache到里面，并且实际大小也会增加。 这是因为我们启动了ASMM （Automatic Shared Memory Management），启动ASMM之后，SGA可以进行自我调整的组件，该组件可以自动调整shared pool size、db cache size等SGA中的组件。
所以虽然我们的的recycle pool 不大，但是通过ASMM 的自动调整，我们的数据还是可以keep 进去。
启动ASMM，只需要设置sga_target参数，则其他组件就能够根据系统的负载和历史信息自动的调整各个部分的大小。还需要设置statistics_level为typical或all。
yangtingkun 对这个recyclepool 也有测试，参考：
http://yangtingkun.itpub.net/post/468/97277
http://yangtingkun.itpub.net/post/468/94255
http://yangtingkun.itpub.net/post/468/96169
-------------------------------------------------------------------------------------------------------
Blog: http://www.cndba.cn/dave
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群：142216823
DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
