如何配置多个Buffer Pools

wapysun

浏览: 21511115 次
性别:
来自: 杭州

最近访客更多访客>>

devcang

hunankeda110

辽东小小

apex53

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 77)
2012-05 ( 587)
2012-04 ( 177)
更多存档...

来自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

统计信息
----------------------------------------------------------
          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

统计信息
----------------------------------------------------------
          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 |
-------------------------------------------------------------------

SQL> select count(*) from tkeep;

执行计划
----------------------------------------------------------
Plan hash value: 1750614220

统计信息
----------------------------------------------------------
          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

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行。

分享到：