Buffer cache 的调整与优化(二)

--********************************

-- Buffer cache 的调整与优化(二)

--********************************

Buffer cache 实际上细分为多个不同的Buffer cache,如keep pool,recycle pool,default pool,下面描述不同buffer cache的使用。

有关Buffer cache 的总体描述,请参考:Buffer cache 的调整与优化(一)

一、不同buffer pool的应用

一个buffer pool即对应于一个oracle 数据块,三种不同的pool实际上是针对在data buffer cache中块的访问的程度不同在通常的

惯例下采取的方法。即对最热块,次热快,以及冷块存放到不同的buffer pool中。实际上这几个不同的buffer pool除了分配的大小不同

之外,所采用的算法都是LRU算法,因此对块的缓存以及淘汰(aged out)算法实质一样。

任意一个不同的buffer pool都将根据访问方式的不同而只缓存读取到的数据块,即如果是全表扫描,则缓存所有块,如果是索引快速

扫描,则缓存索引的所有叶节点块。

keep buffer cache --对应keep pool

recycle buffer cache --对应recycle pool

nk buffer caches --对应db_nk_cache_size

default buffer cache --对应dafault pool

keep pool:

对于经常访问的小表将其常驻内存,即放置到keep pool。其作用是保证这部分经常访问的数据能够常驻内存而不被替换出内存,

从而提高访问这些数据的速度。这个池最好能够保持99%的命中率,也就是说要保证这个池的大小能够缓存放于这个池的大部分对象。

recycle pool:

对于不经常访问的大segment,就可以考虑将其放置到recycle pool,以尽快将其淘汰出去。

dafault pool:

普通对象的缓冲池,那些没有在keep pool也没有在recycle pool的对象将缓冲到这里。

nk buffer caches

主要适用于不同平台传输表空间,或根据业务需要来使用非标准表空间之外的表空间。

如磁盘上数据文件的最小I/O单元叫block一样,buffer cache的最小单元(或者说结构)叫buffer。

每个buffer跟x$bh中每条记录存在一一对应关系。

注意:

default buffer cache = db_cache_size - db_keep_cache_size - db_recycle_cache_size - db_nk_cache_size

sys@ORCL> select * from v$version where rownum < 2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> select name,bytes/1024/1024 from v$sgainfo where name='Buffer Cache Size';

NAME BYTES/1024/1024

------------------------- ---------------

Buffer Cache Size 192

sys@ORCL> select name,current_size,buffers,block_size FROM v$buffer_pool;

NAME CURRENT_SIZE BUFFERS BLOCK_SIZE

--------------- ------------ ---------- ----------

DEFAULT 192 23952 8192

sys@ORCL> alter system set db_recycle_cache_size=16m;

sys@ORCL> alter system set db_keep_cache_size=16m;

sys@ORCL> select name,current_size,buffers,block_size FROM v$buffer_pool;

NAME CURRENT_SIZE BUFFERS BLOCK_SIZE

--------------- ------------ ---------- ----------

KEEP 16 1996 8192

RECYCLE 16 1996 8192

DEFAULT 160 19960 8192

sys@ORCL> alter system set db_16k_cache_size=4m;

sys@ORCL> select name,current_size,buffers,block_size FROM v$buffer_pool;

NAME CURRENT_SIZE BUFFERS BLOCK_SIZE

--------------- ------------ ---------- ----------

KEEP 16 1996 8192

RECYCLE 16 1996 8192

DEFAULT 160 19960 8192

DEFAULT 4 252 16384

从上面的设置可以看出,任意一个buffer pool大小的调整,不影响整个buffer_pool的大小。即任意buffer pool的增加,将使得

default buffer pool的减小,反之,任意buffer pool尺寸的减少,default buffer pool的尺寸将会增加。

其次,任意buffer pool的增加应考虑到Oracle 以及OS是否有足够的内存来进行分配。如果是使用了ASMM管理,则应考虑整个

sga_target < sga_max_size。

二、大表段、大索引段随机访问的问题

当使用LRU算法时,对于大表段,索引段的随机访问,容易导致其他的并非最频繁访问的热点块从cache中被aged out.此外,这些随机访问

的数据块并不属于热块,也很容易随时被替换,通过下面的三种方法来避免该情况的产生:

1.如果受影响的对象是索引,则判断是否是精确的索引选择,如果不是,则调整SQL语句。

2.如果SQL语句已优化,则可以将被访问的大段存放到recycle cache中。

3.可以将一些小的热点块移入到keep buffer pool,keep buffer pool能够最小化cache的丢失。

三、多个buffer pool的设置

db_cache_size

db_keep_cache_size

db_recycle_cache_size

db_nk_cache_size

以上的参数为动态参数,使用下面的方式来调整,可以基于内存调整,也可以将参数更改到spfile。

alter system set db_keep_cache_size=nm scope = both | memory | spfile;

alter system set db_16k_cache_size=nm;

alter system set db_recycle_cache_size=nm;

闩由Oracle RDBMS来自动分配

进行了上述设置之后,可以基于这些不同的缓冲池来创建对象

create index idx_obj

storage(buffer_pool keep);

alter table tb_obj

storage(buffer_pool recycle);

alter index idx_obj

storage(buffer_pool keep);

注:

四个池除了使用不同的名称且产生不同的作用之外,其内部算法实质是一样的,都是采用LRU算法。

一个segment只能放入到一个buffer pool中.如果一个表或索引对象拥有多个segment,则不同的segment可以存放到不同的buffer pool.

如何决定什么样的segment存放到何种buffer cache则根据业务需求来定。

如果没有指定buffer_pool短语,则表示该对象进入default类型的buffer cache。

四、keep buffer pool的使用与优化

将常用的小表对象常驻内存

一般情况是对象的大小应当为少于default buffer pool 大小的10%。

根据下面的方法计算对象所占用块的总数,且该块的总数大小应当小于于keep buffer pool的大小。

计算所有将要放入到keep buffer cache对象的总块数得到一个近似值,然后将稍微大于该近似值的尺寸指定给keep buffer pool。

可以通过查询DBA_TABLES.BLOCKS 和DBA_TABLES.EMPTY_BLOCKS 获得块的信息或者通过V$BH查看segment所占用的buffer。

对于放入keep buffer pool中的表数据,如果对象块多于buffer pool数量,则以buffer pool数量为准进行缓存,冷块将被新块置换。

注:如果位于keep buffer pool中的对象尺寸增大,将不会被填充到keep buffer pool。

其次,如果有多个对象缓存到keep buffer pool,而keep buffer pool不足以缓存这些对象,按LRU算法,先前位于keep buffer

pool 的对象一样会被aged out。

首先使用包收集对象信息

sys@ORCL> exec dbms_stats.gather_table_stats('SCOTT','BIG_KEEP');

sys@ORCL> select table_name,blocks,empty_blocks

2 from dba_tables where owner='SCOTT' and table_name='BIG_KEEP';

TABLE_NAME BLOCKS EMPTY_BLOCKS

------------------------- ---------- ------------

BIG_KEEP 180 0

设定keep buffer pool的大小

alter system set db_keep_cache_size=16m scope = both ; --注意,该参数值的大小应根据实际情况设置

将对象放置的keep buffer pool

alter table big_keep

storage(buffer_pool keep);

五、recycle buffer pool的使用与优化

一旦事务被提交则这些块将从recycle buffer pool中被清除

对象的大小应当为多于default buffer pool 大小的两倍

recycle buffer pool需要具有一个事务所需要的全部块

下面将big_temp 置入到recycle buffer pool

sys@ORCL> exec dbms_stats.gather_table_stats('SCOTT','BIG_RECYCLE');

scott@ORCL> select blocks,empty_blocks from dba_tables

2 where table_name='BIG_RECYCLE' and owner='SCOTT';

BLOCKS EMPTY_BLOCKS

---------- ------------

1062 0

alter system set db_recycle_cache_size=16m scope = both ; --注意,该参数值的大小应根据实际情况设置

scott@ORCL> alter table big_recycle

2 storage(buffer_pool recycle);

使用视图v$cache,

SELECT owner#

,NAME

,COUNT(*) blocks

FROM v$cache

GROUP BY owner#,NAME;

SELECT s.username --跟踪recycle buffer pool的I/O情况

,io.block_gets

,io.consistent_gets

,io.physical_reads

FROM v$sess_io io,v$session s

WHERE io.sid=s.sid;

六、获得buffer pool中的相关信息

视图v$bh(基于视图x$bh)显示当前位于SGA中所有块的详细信息。决定哪个段位于哪个缓冲区,所占住的块的个数等

1.查询buffer cache中不同对象占住块的个数(可以根据查询将不经常访问的大对象置于到recycle pool)

SELECT o.owner, object_name, object_type, COUNT(1) buffers --这个查询获得到经常访问的对象,可以将其放到recycle pool中

FROM SYS.x$bh, dba_objects o

WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))

AND obj = o. object_id

AND o.owner NOT IN ('SYSTEM', 'SYS')

GROUP BY o.owner, object_name, object_type

ORDER BY buffers;

SELECT o.owner, object_name, object_type, COUNT(1) buffers --这个查询获得到经常访问的对象,可以将其放到keep pool中

FROM SYS.x$bh, dba_objects o

WHERE tch > 10

AND lru_flag = 8

AND obj = o.object_id

AND o.owner NOT IN ('SYSTEM', 'SYS')

GROUP BY o.owner, object_name, object_type

ORDER BY buffers;

2.查询单个对象占住buffer cache中块的总个数

SELECT COUNT(*)

FROM v$bh

WHERE objd=(

SELECT data_object_id

FROM Dba_Objects

WHERE object_name=UPPER('big_table')

AND owner='SCOTT'

AND status != 'free');

COUNT(*)

----------

4235

3.获得所有不同的buffer pool 当前分配块的总个数

SELECT NAME

,block_size

,SUM(buffers)

FROM v$buffer_pool

GROUP BY NAME,block_size

HAVING SUM(buffers)>0;

NAME BLOCK_SIZE SUM(BUFFERS)

---------- ---------- ------------

DEFAULT 8192 8958

KEEP 8192 1996

DEFAULT 16384 252

RECYCLE 8192 1996

4.获得单个对象占用buffer cache的比率

SELECT round(obj_cnt/totalcache_cnt*100,3)

FROM

(SELECT COUNT(*) AS obj_cnt

FROM v$bh

WHERE objd=(

SELECT data_object_id

FROM Dba_Objects

WHERE object_name=UPPER('big_table')

AND owner='SCOTT'))a,

(SELECT NAME

,block_size

,SUM(buffers) AS totalcache_cnt

,COUNT(*)

FROM v$buffer_pool

WHERE NAME='DEFAULT'

GROUP BY NAME,block_size

HAVING SUM(buffers)>0) b;

ROUND(OBJ_CNT/TOTALCACHE_CNT*100,3)

-----------------------------------

.015

七、查询不同的buffer pool的命中率(buffer cache hit ratio)

低ratio并不能表明增加cache size可以提高性能。高ratio有时反而会让你误认为cache size已经足够大而满足要求了。比如:重复的扫描

一些大表或索引。然后大表的全表扫描往往都是物理读,会人为的降低hit ratio。因此在不同高峰时段,多次采集数据非常有必要(或使用

StatsPack。

Db_cache_size 是针对默认的db_block_size的,对于非标准的block,要特别指定DB_nK_CACHE_SIZE 参数。

SELECT NAME,

block_size,

physical_reads,

db_block_gets,

consistent_gets,

(1 -(physical_reads / (DECODE(db_block_gets, 0, 1, db_block_gets) +

DECODE(consistent_gets, 0, 1, consistent_gets)))) * 100 "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS

ORDER BY NAME;

NAME BLOCK_SIZE PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Hit Ratio

---------- ---------- -------------- ------------- --------------- ---------

DEFAULT 16384 63 5 128 52.632

DEFAULT 8192 21013 230479 879158 98.106

KEEP 8192 0 0 0 100.000

RECYCLE 8192 106 0 434 75.632

八、总结

尽管使用不同的缓冲池从某种程度上来说可以大大提高数据库系统的I/O,给予了DBA更多的选择性。然而,多个缓冲池(buffer pool)

增加了管理的复杂度,其次由于不同的缓冲池不能够共享,在某种程度上来说,势必造成buffer cache大小的浪费。因此,如果default

buffer pool能够满足现有的需求,尽可能的避免使用过多的缓冲池带来管理的不便。

九、更多参考

有关性能优化请参考

Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化(一)

Oracle 表缓存(caching table)的使用

有关闪回特性请参考

Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query、Flashback Table)

Oracle 闪回特性(Flashback Version、Flashback Transaction)

有关基于用户管理的备份和备份恢复的概念请参考:

Oracle 冷备份

Oracle 热备份

Oracle 备份恢复概念

Oracle 实例恢复

Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考:

RMAN 概述及其体系结构

RMAN 配置、监控与管理

RMAN 备份详解

RMAN 还原与恢复

有关Oracle体系结构请参考:

Oracle 实例和Oracle数据库(Oracle体系结构)

Oracle 表空间与数据文件

Oracle 密码文件

Oracle 参数文件

Oracle 数据库实例启动关闭过程

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 归档日志

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入剖析:insert 的enq: TM – contention

张大朋(Lunar)Oracle 资深技术专家 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于...

3016
来自专栏杨建荣的学习笔记

通过shell定制ash脚本(r3笔记第33天)

ash是在10g以来一个很有用的特性,能够作为awr的补充,对于排查一些历史的问题能够提供更加详细和针对性的数据。 当然个人在使用ash的时候感觉最慢的地方就是...

3334
来自专栏沈唁志

谈谈在SQL语句中的优化技巧

1894
来自专栏13blog.site

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十二)数据层优化-explain关键字及慢sql优化

本文提要 从编码角度来优化数据层的话,我首先会去查一下项目中运行的sql语句,定位到瓶颈是否出现在这里,首先去优化sql语句,而慢sql就是其中的主要优化对象,...

38411
来自专栏程序员同行者

inception安装使用

注意bison版本,centos7如果yum安装bison,会因为版本过高导致编译报错

4872
来自专栏蓝天

Oracle 系统表大全

数据字典dict总是属于Oracle用户sys的。   1、用户:    select username from dba_users;   改口令    al...

1002
来自专栏轮子工厂

深入理解MySQL---数据库知识最全整理,这些你都知道了吗?

对于后端开发人员来说,经常会和数据打交道,今天总结下数据库相关的知识。包括MySQL,JDBC基础,JDBC进阶,MongoDB,性能优化等知识点。

1113
来自专栏数据库

按图索骥:SQL中数据倾斜问题的处理思路与方法

数据倾斜即表中某个字段的值分布不均匀,比如有100万条记录,其中字段A中有90万都是相同的值。这种情况下,字段A作为过滤条件时,可能会引起一些性能问题。 本文通...

2119
来自专栏乐沙弥的世界

dbms_stats 导入导出 schema 级别统计信息

    在使用CBO优化器模式的Oracle数据库中,统计信息是CBO生成最佳执行计划的重要依据。这些统计信息通常包括列级、表级、索引、系统级别的统计信息等。所...

882
来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

3948

扫码关注云+社区

领取腾讯云代金券