Oracle Buffer Cache 中的 Recycle Pool 说明.docx

Oracle Buffer Cache 中的 Recycle Pool 说明 一. Recycle Pool 说明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 pools 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, theobjects 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./docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF942851.3 说明 Recycle Pool用于存储临时使用的、不被经常使用的较大的对象，这些对象放置在Default Buffer Pool显然是不合适的，这些块会导致过量的缓冲区刷新输出，而且不会带来任何好处，因为等你想要再用这个块时，它可已经老化退出了缓存。要把这些段与默认池和保持池中的段分开，这样就不会导致默认池和保持池中的块老化而退出缓存。 对于Recycle Pool，先装载的表保留在RECYCLE池中。RECYCLE池满了之后，读取的数据不被缓存。读取没有进入过RECYCLE池的表，会替换掉最后进入RECYCLE池的很少的BLOCK，而且，缓存的BLOCK也可能会很快被替换出去，但是最少会保留1个BLOCK在缓存中。 和全表扫描相比，索引扫描产生的CACHE被缓冲的优先级更高。二. 相关测试-查看db_recyle_cache_sizeSYSanqing2(rac2)show parameter db_recycle_cache_sizeNAME TYPE VALUE- -db_recycle_cache_size big integer 0-查看db_cache_size大小SYSanqing2(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 大小SYSanqing2(rac2)show parameter db_keep_cache_sizeNAME TYPE VALUE- -db_keep_cache_size big integer 100M-释放keeppool空间，然后分配给recycle poolSYSanqing2(rac2)alter system set db_keep_cache_size=0 scope=both sid=anqing2;Systemaltered.SYSanqing2(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 forstandardSYSanqing2(rac2)alter system set db_recycle_cache_size=15M scope=bothsid=anqing2;Systemaltered.SYSanqing2(rac2)show parameter db_recycle_cache_sizeNAME TYPE VALUE- -db_recycle_cache_size big integer 16MSYSanqing2(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 poolSYSanqing2(rac2)create table r1 storage(buffer_pool recycle) as select * fromdba_objects;Tablecreated.SYSanqing2(rac2) create table r2 as select * from dba_objects;Tablecreated.SYSanqing2(rac2)alter table r2 storage(buffer_pool recycle);Tablealtered.-查看recyclepool中的对象SYSanqing2(rac2) select segment_name,blocks,bytes/1024/1024|MAS SIZE from dba_segments where BUFFER_POOL = RECYCLE;SEGMENT_NAME BLOCKS SIZE- -R1 768 6MR2 768 6M-查看v$bh 视图SYSanqing2(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 STATUSCOUNT(*)- -R2 xcur 1R1 xcur 1在我的Blog里： OracleBuffer Cache 原理 /tianlesoftware/article/details/6573438有介绍过这个视图，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。SYSanqing2(rac2)set autot on statSYSanqing2(rac2)select count(*) from r1; COUNT(*)- 50269Statistics- 28recursive calls 0db block gets 786consistent gets 690physical reads 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed-再次select，因为之前已经缓存过，所以现在不会有物理读SYSanqing2(rac2)select count(*) from r1; COUNT(*)- 50269Statistics- 0recursive calls 0db block gets 695consistent gets 0physical reads -物理读为0 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed-查看v$bh 的缓存情况SYSanqing2(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 STATUSCOUNT(*)- -R2 xcur 1R1 xcur 1R1 scur 690XCUR：a current mode block, exclusive to this instanceSCUR：a current mode block, shared with other instances注意： 690+1=691 个blocks。 与之前查看的768个blocks 有出入。-我们对表R1 进行分析，在查看实际使用的BlocksSYSanqing2(rac2) exec dbms_stats.gather_table_stats(SYS,R1);PL/SQLprocedure successfully completed.-dba_tables 中的blocks必须在分析之后才有值SYSanqing2(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。SYSanqing2(rac2) select count(*) from r2; COUNT(*)- 50270Statistics- 151recursive calls 0db block gets 793consistent gets 690physical reads 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 3sorts (memory) 0sorts (disk) 1rows processedSYSanqing2(rac2)select count(*) from r2; COUNT(*)- 50270Statistics- 0recursive calls 0db block gets 695consistent gets 0physical reads -第二次select 物理读为0 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed-查看v$bh缓存情况SYSanqing2(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 STATUSCOUNT(*)- -R2 scur 690R2 xcur 1R1 scur 691-R1 和 R2 都缓存进去了，Recycle Pool 也满了-创建tableR3，也缓存到Recycle PoolSYSanqing2(rac2)create table r3 storage(buffer_pool recycle) asselect * from dba_objects;Tablecreated.SYSanqing2(rac2)select segment_name,blocks,bytes/1024/1024|M AS SIZE fromdba_segments where BUFFER_POOL = RECYCLE;SEGMENT_NAME BLOCKS SIZE- -R1 768 6MR2 768 6MR3 768 6M-查看V$bh SYSanqing2(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 STATUSCOUNT(*)- -R2 scur 690R2 xcur 1R1 scur 691R3 xcur 1-我们select一下，看能cache 多少blocks 进来SYSanqing2(rac2)select count(*) from r3; COUNT(*)- 50271Statistics- 28recursive calls 0db block gets 766consistent gets 690physical reads -第一次物理读了690个blocks 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processedSYSanqing2(rac2)/ COUNT(*)- 50271Statistics- 0recursive calls 0db block gets 695consistent gets 0physical reads -第二次没有物理读 0redo size 413bytes sent via SQL*Net to client 400bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed-查看V$bh 视图SYSanqing2(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 STATUSCOUNT(*)- -R2 scur 690R3 xcur 1R3 scur 690R2 xcur 1R1 scur 691- R3 也cache 进来了，-查看recyclepool 大小SYSanqing2(rac2)show parameter db_recycle_cache_sizeNAME TYPE VALUE- -db_recycle_cache_size big integer 16M-三个表数据全部cache进去了，但是3*6M，需要18M的空间，肯定是放不下的，在次查看一下：SYSanqing2(rac2)SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) |MTotal_Size 2FROM ( SELECT o.OBJECT_NAME,COUNT (*)NUMBER_OF_BLOCKS 3FROM DBA_OBJECTS o, V$BHbh,dba_segments dd 4WHERE 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= RECYCLE8 GROUP BY o.OBJECT_NAME 9ORDER BY COUNT (*) b;/TOTAL_SIZE-16.07M实际也使用了16M的空间。 在keep 一个大表进去SYSanqing2(rac2)alter table rb_test storage(buffer_pool recycle);Tablealtered.SY