Oracle缓存表与Oracle缓存的区别

一、Oracle缓存表与 Oracle缓存的概念

Oracle 缓存：是把Oracle近期查询的语句放置在Oracle设定的缓存当中。

Oracle 缓存表：是把某个表放置在缓存当中，缓存是Oracle在内存中的一个分区。

二、表缓存的设定

1、oracle中如何将表缓存到内存中

由于在一些静态资料表在数据库中被频繁的访问，所以可以考虑将这些数据量不大的表缓存到内存当中。

将emp表缓存到内存中：

#方法一：
    alter table emp cache;

#方法二：
    alter table emp storage(buffer_pool keep);

取消缓存：

1）alter table emp nocache;

2）alter table emp storage(buffer_pool default);

查看是否缓存：

select table_name,OWNER,cache,buffer_poolfrom dba_tables where table_name='EMP';

查看表大小：

select * from dba_segments where segment_name='EMP' ;

方法一： cache是将表缓存到sharepool 中，该操作直接将表缓存的热端，受LRU算法控制。

方法二：将表缓存到一个固定的内存空间中，默认情况下buffer_pool空间为0,。需手动设置空间大小。

设置空间大小：

alter system set db_keep_cache_size=50M scope=both sid=‘*';

表缓存：

alter table table_name cache = alter table table_name storage(buffer_pool default);

alter table table_name storage(buffer_poolkeep);

已经加入到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 setdb_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 fromv$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 andp.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' 
  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 != 'DEFAULT' 
  8  GROUP BY o.OBJECT_NAME 
  9  ORDER BY COUNT(*)) b; 

SUBSTR(SUM(B 
------------ 
.2170M
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 testcache,如果使用了 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_poolkeep);

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

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 1from 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关于MultipleBuffer Pools的解释，讲解得很清楚：

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

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

按以上步骤把表storage到keeppool中，然后调用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时的执行计划应该一样的，因为读第二次时，也是从defaultcache中读。但是当我们多读几个大表到buffer cache后，也就是替换原来从default cache中读取的数据后，再去读放入keep中的表时，就会发现keep确实起作用了，唉，终于明白怎么一回事，害得我为flush buffer导致keep中的表也phisical郁闷了半天。

五、ORACLE缓存设置

Oracle缓存由两个参数控制SGA_TARGET和PGA_AGGREGATE_TARGET，设置了这两个参数，其他的基本内存部分都由Oracle自动配置为最优值，这也是Oracle推荐的方式。

SGA_TARGET 和PGA_AGGREGATE_TARGET是动态参数，可以在不重启数据库的情况下修改。但是SGA_TARGET受限于 sga_max_size，SGA_TARGET不能超过sga_max_size，所以要增大sga_target先要增大sga_max_size，而sga_max_size是静态参数，修改sga_max_size必须重启Oracle。

所以修改sga_target和pga_aggregate_target的过程如下：

1、修改sga_max_size

SQL>ALTER SYSTEM SET sga_max_size=4g scope=spfile;

2、重启Oracle

3、设置参数sga_target和pga_aggregate_target，

alter system set sga_target=4G;

alter system set pga_aggregate_target=1g;

如果使用的是10g，已经是ASM,oracle会根据统计的信息，自动的来调整你的内存组件的大小，你只需要设置sga_target即可。当然你可以手动设置 db_cache_size，如果设置了的话，Oracle会在自动调整内存大小的时候把这个作为db_cache_size的最小值。

对于sga_target,在动态修改的时候，最大值不能操过sga_max_size, 如果是用scope=spfile这个方式来修改可以超过sga_max_size，应该此时sga_max_size也跟着变大了，如果超过的话。

Oracle 对数据库的cache有他自己的计算的，10g以后，内存是动态的根据对你使用系统的统计来进行调整的，如果出现问题，这块不是原因，你之所以db cache还没有上去，可能是访问的数据比较少，不过你加大db_cache_size的值，会保留这个内存空间的，但是也是一样的，数据 load到内存里，才看得到变化。

数据访问是什么样的访问，你的系统是OLAP还是OLTP，这些应用上的东西对你的决定也有影响的，要谨记，数据库的优化和维护，不仅仅是DBA来做的。如果是到了只能通过DBA来做这一步的话，就相当于看病已经到了拿手术刀这一步了。你的改变带来的风险和代价最高。

要想减少磁盘读,只能增大内存的使用.楼主可以看看这个视图v$db_cache_size,并执行下面的查询：

select block_size, size_for_estimate,size_factor, estd_physical_read_factor, estd_physical_reads from v$db_cache_advice;

Oracle在这个视图中针对db_cache_size的大小会给出一些建议。

下面解释几个列的含义

size_for_estimate：估计的cache size大小

size_factor：估计的cachesize大小与当前大小的比值

estd_physical_reads：在估计的cache size大小情况下，会产生的物理读数量

estd_physical_read_factor：估计的物理读数量与当前物理读数量的比值。

例子：

SIZE_FOR_ESTIMATE SIZE_FACTORESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

20 0.0893 6.1454 270840
40 0.1786 3.0632 135003
60 0.2679 2.4908 109775
80 0.3571 2.1411 94362
100 0.4464 1.9619 86464
120 0.5357 1.8235 80366
140 0.625 1.6081 70873
160 0.7143 1.5038 66275
180 0.8036 1.3957 61510
200 0.8929 1.1541 50861
220 0.9821 1.0089 44465
224 1 1 44072
240 1.0714 0.8524 37568
260 1.1607 0.8184 36068
280 1.25 0.7965 35103
300 1.3393 0.793 34948
320 1.4286 0.7908 34853
340 1.5179 0.7905 34841
360 1.6071 0.7905 34841
380 1.6964 0.7905 34841
400 1.7857 0.7873 34698

从上面的数据中可以看到，当前db_cache_size为224M，其物理读为44072，增加cachesize大小会减少物理读，当cache size到340M时，物理读的减少不再明显，因此db_cache_size大小为340M在目前情况下是一个合适的值。

由于这里用的是自动共享内存管理方式，应该增加sga_target，这样db_cache_size大小会相应增加，要增加sga_target要先增加sga_max_size。

转载：https://blog.csdn.net/DBDeep/article/details/79922449

posted on 2020-09-25 17:30 morgan363 阅读(1827) 评论(0) 编辑收藏举报

刷新页面返回顶部

morgan363