如何配置多个Buffer Pools

来自tom

在Oracle 的较早版本中，只有一个块缓冲区缓存，所有段的所有块都放在这个区中。从Oracle 8.0开始，可以把SGA中各个段的已缓存块放在3个位置上。

默认池（default pool）：所有段块一般都在这个池中缓存。这就是原先的缓冲区池（原来也只有一个缓冲区池）。
保持池（keep pool）：按惯例，访问相当频繁的段会放在这个候选的缓冲区池中，如果把这些段放在默认缓冲区池中，尽管会频繁访问，但仍有可能因为其他段需要空间而老化（aging）。 用于想无限制(pin)的缓冲区，可以对那些需要经常访问并驻留在内存的小表使用keep pool。

回收池（recycle pool）：按惯例，访问很随机的大段（偶尔访问的大段）可以放在这个候选的缓冲区池中，这些块会导致过量的缓冲区刷新输出，而且不会带来任何好处，因为等你想要再用这个块时，它可能已经老化退出了缓存。要把这些段与默认池和保持池中的段分开，这样就不会导致默认池和保持池中的块老化而退出缓存。

需要注意，在保持池和回收池的描述中，我用了一个说法“按惯例”。因为你完全有可能不按上面描述的方式使用保持池或回收池，这是无法保证的。实际上，这3个池会以大体相同的方式管理块。将块老化或缓存的算法并没有根本的差异。这样做的目标是让DBA 能把段聚集到“热”区（hot）、“温”区（warm）和“不适合缓存”区（do not care to cache）。

理论上讲，默认池中的对象应该足够热（也就是说，用得足够多），可以保证一直呆在缓存中。缓存会把它们一直留在内存中，因为它们是非常热门的块。可能还有一些段相当热门，但是并不太热。这些块就作为温块。这些段的块可以从缓存刷新输出，为不常用的一些块（“不适合缓存”块）腾出空间。为了保持这些温段的块得到缓存，可以采取下面的某种做法:
将这些段分配到保持池，力图让温块在缓冲区缓存中停留得更久。
将“不适合缓存”段分配到回收池，让回收池相当小，以便块能快速地进入缓存和离开缓存（减少管理的开销）。

任何一种做法都会增加DBA所要执行的管理工作，因为要考虑3个缓存，要确定它们的大小，还要为这些缓存分配对象。还要记住，这些池之间没有共享，所以，如果保持池有大量未用的空间，即使默认池或回收池空间不够用了，保持池也不会把未用空间交出来。总之，这些池一般被视为一种非常精细的低级调优设备，只有所有其他调优手段大多用过之后才应考虑使用。

--将表使用keep pool,但是要确保db_keep_cache_size能够容得下要放入keep pool的所有对象。
--alter table table_name storage(buffer_pool keep);之后对表进行分析，确认表已被缓存到keep pool中。

--当keep pool中无法容纳时，会使用默认缓存区内存空间（默认缓存区有多余的空间）。

--当对象增长时，keep pool的内存空间也许会不再适合该对象。

测试:

SQL> analyze table tkeep compute statistics; 表已分析。 SQL> analyze table txx compute statistics; 表已分析。 SQL> analyze table txx2 compute statistics; 表已分析。 SQL> SELECT DECODE(wbpd.bp_id, 2 1, 3 'Keep', 4 2, 5 'Recycle', 6 3, 7 'Default', 8 4, 9 '2K Pool', 10 5, 11 '4K Pool', 12 6, 13 '8K Pool', 14 7, 15 '16K Pool', 16 8, 17 '32K Pool', 18 'UNKNOWN') Pool, 19 bh.owner, 20 bh.object_name object_name, 21 COUNT(1) NumOfBuffers 22 FROM X$kcbwds wds, 23 X$kcbwbpd wbpd, 24 (SELECT set_ds, x.addr, o.name object_name, u.name owner 25 FROM sys.obj$ o, sys.user$ u, X$bh x 26 WHERE o.owner# = u.user# 27 AND o.dataobj# = x.obj 28 AND x.state != 0 29 -- AND o.owner# != 0 30 ) bh 31 WHERE wds.set_id >= wbpd.bp_lo_sid 32 AND wds.set_id <= wbpd.bp_hi_sid 33 AND wbpd.bp_size != 0 34 AND wds.addr = bh.set_ds 35 AND bh.object_name IN('TXX','TXX2','TKEEP') 36 GROUP BY DECODE(wbpd.bp_id, 37 1, 38 'Keep', 39 2, 40 'Recycle', 41 3, 42 'Default', 43 4, 44 '2K Pool', 45 5, 46 '4K Pool', 47 6, 48 '8K Pool', 49 7, 50 '16K Pool', 51 8, 52 '32K Pool', 53 'UNKNOWN'), 54 bh.owner, 55 bh.object_name 56 ORDER BY 1, 4, 3, 2; POOL OWNER OBJECT_NAME NUMOFBUFFERS -------- ------------------------------ ------------------------------ ------------ Keep SYS TKEEP 1 Keep SYS TXX 151 Keep SYS TXX2 347 SQL> set autot on SQL> set autot traceonly SQL> select count(*) from tkeep; 执行计划 ---------------------------------------------------------- Plan hash value: 1750614220 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TKEEP | 1 | 2 (0)| 00:00:01 | -------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 1 physical reads 0 redo size 335 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from tkeep; 执行计划 ---------------------------------------------------------- Plan hash value: 1750614220 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TKEEP | 1 | 2 (0)| 00:00:01 | -------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 335 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx; 执行计划 ---------------------------------------------------------- Plan hash value: 238917920 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 407 consistent gets 253 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx; 执行计划 ---------------------------------------------------------- Plan hash value: 238917920 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 407 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx2; 执行计划 ---------------------------------------------------------- Plan hash value: 2696244914 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX2 | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 407 consistent gets 57 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx2; 执行计划 ---------------------------------------------------------- Plan hash value: 2696244914 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX2 | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 407 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from tkeep; 执行计划 ---------------------------------------------------------- Plan hash value: 1750614220 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TKEEP | 1 | 2 (0)| 00:00:01 | -------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 335 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx; 执行计划 ---------------------------------------------------------- Plan hash value: 238917920 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 407 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from txx2; 执行计划 ---------------------------------------------------------- Plan hash value: 2696244914 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TXX2 | 1199 | 90 (0)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 407 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autot off SQL> SELECT DECODE(wbpd.bp_id, 2 1, 3 'Keep', 4 2, 5 'Recycle', 6 3, 7 'Default', 8 4, 9 '2K Pool', 10 5, 11 '4K Pool', 12 6, 13 '8K Pool', 14 7, 15 '16K Pool', 16 8, 17 '32K Pool', 18 'UNKNOWN') Pool, 19 bh.owner, 20 bh.object_name object_name, 21 COUNT(1) NumOfBuffers 22 FROM X$kcbwds wds, 23 X$kcbwbpd wbpd, 24 (SELECT set_ds, x.addr, o.name object_name, u.name owner 25 FROM sys.obj$ o, sys.user$ u, X$bh x 26 WHERE o.owner# = u.user# 27 AND o.dataobj# = x.obj 28 AND x.state != 0 29 -- AND o.owner# != 0 30 ) bh 31 WHERE wds.set_id >= wbpd.bp_lo_sid 32 AND wds.set_id <= wbpd.bp_hi_sid 33 AND wbpd.bp_size != 0 34 AND wds.addr = bh.set_ds 35 AND bh.object_name IN('TXX','TXX2','TKEEP') 36 GROUP BY DECODE(wbpd.bp_id, 37 1, 38 'Keep', 39 2, 40 'Recycle', 41 3, 42 'Default', 43 4, 44 '2K Pool', 45 5, 46 '4K Pool', 47 6, 48 '8K Pool', 49 7, 50 '16K Pool', 51 8, 52 '32K Pool', 53 'UNKNOWN'), 54 bh.owner, 55 bh.object_name 56 ORDER BY 1, 4, 3, 2; POOL OWNER OBJECT_NAME NUMOFBUFFERS -------- ------------------------------ ------------------------------ ------------ Default SYS TKEEP 1 Default SYS TXX2 57 Default SYS TXX 253 Keep SYS TKEEP 1 Keep SYS TXX 151 Keep SYS TXX2 347 已选择6行。
keep pool相关资料：
http://yangtingkun.itpub.net/post/468/77951
http://yangtingkun.itpub.net/post/468/78272
http://yangtingkun.itpub.net/post/468/86429
http://space.itpub.net/4227/viewspace-68852