oracle表缓存的设定

最新推荐文章于 2024-08-19 19:55:28 发布

learningaginlearnin

最新推荐文章于 2024-08-19 19:55:28 发布

阅读量4.6k

点赞数

分类专栏： Oracle数据库文章标签： oracle buffer cache table integer sql

Oracle数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

--表缓存

alter table table_name cache = alter table table_name storage(buffer_pool default);
alter table table_name storage(buffer_pool keep);
--已经加入到KEEP区的表想要移出缓存，使用
alter table table_name nocache;
--查看哪些表被放在缓存区但并不意味着该表已经被缓存
select table_name from dba_tables where buffer_pool='keep';
--查询到该表是否已经被缓存
select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';
--查询当前用户下表的情况
select table_name,cache,buffer_pool from user_TABLES;
--对于普通LOB类型的segment的cache方法
alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);
--取消缓存
alter table test modify lob(address) (storage (buffer_pool keep) nocache);
keep Buffer Pool

    Keep Buffer Pool 的作用是缓存那些需要经常查询的对象但又容易被默认缓冲区置换出去的对象，按惯例，Keep pool设置为合理的大小，以使其中存储的对象不再age out，也就是查询这个对象的操作不会引起磁盘IO操作，可以极大地提高查询性能。

    默认的情况下 db_keep_cache_size=0，未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后 db_cache_size 会减少。

    并不是我们设置了keep pool 之后，热点表就一定能够缓存在 keep pool ，keep pool 同样也是由LRU 链表管理的，当keep pool 不够的时候，最先缓存到 keep pool 的对象会被挤出，不过与default pool 中的 LRU 的管理方式不同，在keep pool 中表永远是从MRU 移动到LRU，不会由于你做了FTS而将表缓存到LRU端，在keep pool中对象永远是先进先出。当oracle发现你的表太太，大过你设定keep pool的大小是，根本就不会放到keep池中去的（如keep pool设定100M ，设定的用户缓存的表为200M）。可以用select segment_name from dba_segments where BUFFER_POOL = 'KEEP';语句查看便知。

    10g中SGA自动管理，ORACLE并不会为我们管理keep pool ，ORACLE只会管理default pool。

   查看 keep pool 大小

SQL> select component,current_size from v$sga_dynamic_components
2 where component='KEEP buffer cache';

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache                                                           0

  手动分配keep pool

SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 0
SQL> alter system set db_keep_cache_size=10m;

系统已更改。

SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 16M这里keep pool 16M，可我前面设置命名是10m了？

SQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache';

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache                                                    16777216    这里keep pool 16M，可我前面设置命名是10m了？

   查看keep pool剩余大小

SQL> select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p
2 where a.set_id=p.LO_SETID and     p.name='KEEP';

NAME                 total buffers free buffers
-------------------- ------------- ------------
KEEP                          1984         1984
可以看到没有使用过keep 池

   指定table的缓存池

SQL>create table test as select * from dba_objects;;

Table created.
SQL> alter table test storage(buffer_pool keep);

Table altered.

或者是

create table test storage(buffer_pool keep) as select * from dba_objects;

查看放入Keep的对象

SQL> select segment_name from dba_segments where BUFFER_POOL = 'KEEP';

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST

SQL> /

NAME                 total buffers free buffers
-------------------- ------------- ------------
KEEP                          1984         1962     可以看到使用了22个block

查看以上的表占用了db_keep_cache_size 多大的空间？

SQL> select substr(sum(b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) || 'M'
from (SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
          FROM DBA_OBJECTS o, V$BH bh, dba_segments dd
         WHERE o.DATA_OBJECT_ID = bh.OBJD
           AND o.OWNER = dd.owner
           and dd.segment_name = o.OBJECT_NAME
           and dd.buffer_pool != 'DEFAULT'
         GROUP BY o.OBJECT_NAME
         ORDER BY COUNT(*)) b; 2    3    4    5    6    7    8    9

SUBSTR(SUM(
-----------
3.643M

SQL> select table_name,cache,blocks from dba_tables where wner='ROBINSON' and buffer_pool='KEEP';

TABLE_NAME                     CACHE                    BLOCKS
------------------------------ -------------------- ----------
TEST                               N                        22

可以看到这个表的 22个block 全部cache 到 keep pool ，这里的cache 字段表明这个表还没有使用这个命令 alter table  test cache,如果使用了 alter table test cache ,命令，那么 N 将变成Y

总结：如果表经常使用，而且表较小，可以设置 keep pool ，将table 全部 cache 到 keep pool, keep pool 要么全部 cache 一个table ，要么不cache 。所以，对于大表来说，如果想要 cache 到 keep pool，就需要设置较大的 keep pool ，以容纳大的 table，否者就没有作用了。

Recycle Buffer Pool

Recycle Buffer Pool正好相反。Recycle Buffer Pool用于存储临时使用的、不被经常使用的较大的对象，这些对象放置在Default Buffer Pool显然是不合适的，这些块会导致过量的缓冲区刷新输出，而且不会带来任何好处，因为等你想要再用这个块时，它可已经老化退出了缓存。要把这些段与默认池和保持池中的段分开，这样就不会导致默认池和保持池中的块老化而退出缓存。

SQL> show parameter recyc

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 12M
recyclebin                           string      on

如何将一个表放入Recycle Buffer Pool中：

SQL> alter table test1 storage (buffer_pool recycle);

Table altered.

很多老的文档会提及buffer_pool_keep和buffer_pool_recycle 这两个参数，其实这两个参数已经废弃，由新参数db_keep_cache_size和db_recycle_cache_size 替代：

SQL>select ISDEPRECATED,NAME from v$parameter where name = 'buffer_pool_keep';

ISDEP NAME
----- -----------------
TRUE buffer_pool_keep

=======================================================================================

--表缓存

alter table ..... storage(buffer_pool keep);

--查看哪些表被放在缓存区但并不意味着该表已经被缓存

select table_name from dba_tables where buffer_pool='keep';

--查询到该表是否已经被缓存

select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';

--已经加入到KEEP区的表想要移出缓存，使用

alter table table_name nocache;

--批量插入ORACLE建议用

insert all into ...insert into ...select 1 from dual;

insert all into ... insert into ...select 1 from dual;

--查询当前用户下表的情况

select table_name,cache,buffer_pool from user_TABLES;

--对于普通LOB类型的segment的cache方法

alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);

--取消缓存

alter table test modify lob(address) (storage (buffer_pool keep) nocache);

--查询段

select segment_name,segment_type,buffer_pool from user_segments;

--对基于CLOB类型的对象的cache方法

alter table lob1 modify lob(c1.xmldata) (storage (buffer_pool keep) cache);

  --查询该用户下所有表内的大字段情况

select column_name,segment_name from user_lobs;

来一段Tom关于Multiple Buffer Pools的解释，讲解得很清楚：

实际上，这3 个池会以大体相同的方式管理块；将块老化或缓存的算法并没有根本的差异。这样做的目标是让DBA 能把段聚集到“热”区（hot）、“温”区（warm）和“不适合缓存”区（do not care to cache）。

理论上讲，默认池中的对象应该足够热（也就是说，用得足够多），可以保证一直呆在缓存中。缓存会把它们一直留在内存中，因为它们是非常热门的块。可能还有一些段相当热门，但是并不太热；这些块就作为温块。这些段的块可以从缓存刷新输出，为不常用的一些块（“不适合缓存”块）腾出空间。为了保持这些温段的块得到缓存，可以采取下面的某种做法：将这些段分配到保持池，力图让温块在缓冲区缓存中停留得更久。将“不适合缓存”段分配到回收池，让回收池相当小，以便块能快速地进入缓存和离开缓存（减少管理的开销）。这样会增加DBA 所要执行的管理工作，因为要考虑3 个缓存，要确定它们的大小，还要为这些缓存分配对象。还要记住，这些池之间没有共享，所以，如果保持池有大量未用的空间，即使默认池或回收池空间不够用了，保持池也不会把未用空间交出来。总之，这些池一般被视为一种非常精细的低级调优设备，只有所有其他调优手段大多用过之后才应考虑使用。

按以上步骤把表storage到keep pool中，然后调用alter system flush buffer_cache清空缓存，再全表扫描该表并打开执行计划跟踪，发现有physical reads，如下：

第一次执行计划如下：

----------------------------------------------------------
          0 recursive calls
          0 db block gets
        253 consistent gets
        251 physical reads
          0 redo size
        520 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
第二次执行计划如下：

----------------------------------------------------------
          0 recursive calls
          0 db block gets
        253 consistent gets
          0 physical reads
          0 redo size
        520 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

不知道是否可以这样理解：对于storage到keep pool中的表，第一次会直接physical reads 到keep pool中，下次就直接从keep pool中读了。flush buffer_cache会清空keep pool，这个试验就可以证明。

像上面这样连续执行2次再看执行计划，和不设置keep pool时的执行计划应该一样的，因为读第二次时，也是从default cache中读。但是当我们多读几个大表到buffer cache后，也就是替换原来从default cache中读取的数据后，再去读放入keep中的表时，就会发现keep确实起作用了，唉，终于明白怎么一回事，害得我为flush buffer导致keep中的表也phisical郁闷了半天。

转自： http://hanjian861202.iteye.com/blog/1074693