oracle学习笔记 buffer_cache大小的设置及依据
一）先回顾一下
前面讲的buffercache的工作过程和大体的结构原理
磁盘有一堆dbf文件
内存有buffercache
第一步：一个sql语句进来以后
如：select * from t2 where id=1;
首先是serverprocess会拿着这个sql语句去解析
解析的时候会用到cpu资源还会访问rowcache
解析完成以后生成执行计划再存储到librarycache里面去
这时sql语句的第一步解析执行完了
第二步：要执行语句
执行时要访问数据
buffercache结构中一部分为链区，剩下的为buffer
这里面的链单元有地址指向里面的一些buffer
这时oracle首先通过一系列的判断以后
发现要访问的表t2里面id等于1的行
先判断应该在哪个链上
它计算完了以后它发现这个行所在的块应该在某个链上但不见得在这个链上
然后对这个链进行遍历
第一种结果
在这个链上找到了这个block对应的buffer
既然找到了
这时候这个serverprocess直接读这个buffer
不需要到磁盘再去读了
第二种情况
在这个链上没有找到这个block对应的buffer
这时serverprocess就会在buffer里面找到一个可用的块
假设一个块可用
serverprocess把这个block读到可用的buffer块里面去
同时把可用buffer块的相关地址写到对应链单元内
然后把数据接着读出来
整个的，从链上找buffer，找不到时把block读到内存去写到buffer里面去，然后再读buffer
整个过程都是serverprocess做的
再看另外一个语句
update t2 set name=某个值 where id=1；
要修改某个block的某一行
这时同样serverprocess经过解析以后要执行
执行时同样判断
这行所在的块所在的block应该挂在某个链上
这里在链上遍历发现遍历以后确实找到对应的buffer
直接在内存里面修改buffer不须到磁盘上修改
修改完成以后buffer和block就不一致了
这个buffer就成了一个脏的buffer
这个buffer会被挂在LRUW上和CHECKPOINT队列上
而前面select语句结果访问的块是干净的是可用的
它挂在LRU上
刚才最基本的知识都需要掌握
这是我们回顾的内容
二）DBWn进程和buffercache
接下来我们关心很多内容
某个脏块脏了serverprocess并没有把它写回到磁盘上
是一个进程DBWn负责写的，它是系统的一个进程
使用
[[email protected] ~]$ ps -ef|grep ora
查询当前系统进程中与ora相关的进程
结果中有
oracle 3161 1 0 03:48 ? 00:00:00 ora_dbw0_jiagulun
的一个进程
此进程会把我们的脏块写到磁盘上
dbwn是一个后台进程
serverprocess是一个前台进程
我们的用户送出一个sql语句
serverprocess接到sql语句
整个的从解析到执行到获取这个过程都是serverprocess工作
serverprocess工作时用户一直在等着，工作完了把结果返给用户
用户所执行的sql语句所等待的时间就是serverprocess工作的时间
所以serverprocess是直接面向用户的叫前台进程
我们希望它越快越好
我们就希望serverprocess所做的事情越少越好
将脏块写回磁盘我们没有让serverprocess去做
而是交给一个后台进程DBWn这个进程
它的运行和用户没有关系，它并不影响用户
它执行时用户没有在等待
它在后台周期性执行
1）脏块和DBWn的触发
有很多条件会触发DBWn
1、每隔一段时间DBWn会触发
一般触发不会将整个的所有的脏块全写回磁盘
它会找LRUW链
将冷端的几个脏块写回磁盘
因为冷端意味着不怎么经常被使用
这是第一个每隔一个时间段
每次写的时候写一批不是写所有的
2、没有了干净块
对于干净的和free的块我们可以覆盖可以来使用
现在有一个block要调到内存里面去
找了半天没有找到干净块没有找到可用块
这时也会触发DBWn
DBWn会批量的写一批buffer到磁盘上
写过来的buffer和磁盘的block就一致了
一致以后就可用，这也是一种条件
3、数据库关闭以后
DBWn会把所有的buffer全部写回磁盘
1和2两种情况最多
数据库正常运行期间
1周期性DBWn触发
2当我们的脏缓冲区到一定程度的时候会触发DBWn
还有一种情况
我要在buffer里面找一个可用块的时候
半天都没找着
就是找可用块找的时间太长
虽然链还有剩余，很多剩余块还没有搜索也会中途退出寻找
这里有一个时间的阈值我们叫阀值也可以叫阈值
它也会触发DBWn
总之DBWn是保证我们的脏块能够相对及时的合理的写回到磁盘上
DBWn是后台
它的工作不影响前台不影响serverprocess
它在后台默默的工作
我们用数据库的时候并不怎么关心DBWn
2）DBWn的个数
系统进程查询时
结果有进程名为ora_dbw0_jiagulun
其中dbw0有个数字0
既然有0，就可以有1
也就是DBWn进程可以是多个
但是我们不要去设置过多的DBWn
我们可以看一个参数
SQL> show parameter writer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
db_writer_processes参数目前的值是1
也就是只有一个进程
一般的它的默认值DBWn的个数是cpu数量除上8
如果你16颗cpu的话就应该是两个DBWn进程
一般DBWn进程是够的
老师有一次去用户那里
看到有一个很奇怪的现象
那么多的DBWn而且DBWn占用cpu很高
最后发现它改过这个参数
老师问：你为什么要改它？
他说：我觉得多一些数据库更快！
老师的忠告是：其实不见得！
改这个参数也很容易
SQL> alter system set db_writer_processes=2;
alter system set db_writer_processes=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
结果提示这个参数不能被修改意味着它是静态参数要使用scope=spfile选项静态改
SQL> alter system set db_writer_processes=2 scope=spfile;
System altered.
然后重启数据库
SQL> startup force;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
测试环境下startup force可以重启数据库
但实际环境这样重启很危险的
因为这样重启
整个过程中的关闭数据库过程会被强制执行
而不管有没有正在运行的进程和正在连接的用户
非常可能造成数据的丢失，和引起数据库故障。
再去查一下
[[email protected] ~]$ ps -ef|grep ora
结果中
oracle 21914 1 0 09:09 ? 00:00:00 ora_dbw0_jiagulun
oracle 21916 1 0 09:09 ? 00:00:00 ora_dbw1_jiagulun
有这么两行
说明目前DBWn有两个进程
ora_dbw0_jiagulun和ora_dbw1_jiagulun
因为我们把db_writer_processes参数值设成2了
这次教给大家DBWn进程的数量到底设几个不要乱设
三）buffercache大小
buffercache很重要，目前为止到底该设多大呢
1）buffercache大小的查询和修改
buffercache的设置和sharedpool的设置很相似
一种情况我们单独的去设置
如buffercache设置1个g或2个g
另外可以把buffercache的设置放在SGA target里面去
oracle自动去设置buffercache大小
在实际的生产中我们还是经常的去手工设buffercache大小
这句
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
是查一下当前我们sga里面的各个组件到底是多大
buffer设置大小
alter system set db_cache_size=20M scope=memory;
也可以scope=both
设置buffercache大小这里设的是20M
一般的有一个原则
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
dbcache设的大小一般是
SGA_MAX_SIZE的二分之一到三分之二
也就是整个的SGA的一半到三分之二的空间
我们建议大家直接设成三分之二
当然这个值不是很准确
我们到时候去通过相关的命令去设置去查
先查一下sga的状态
SQL> SELECT component,current_size,min_size FROM v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------------------------------------------------- ------------ ----------
shared pool 100663296 96468992
large pool 4194304 4194304
java pool 4194304 4194304
streams pool 0 0
DEFAULT buffer cache 167772160 167772160
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
ASM Buffer Cache 0 0
13 rows selected.
这一行
DEFAULT buffer cache 167772160 167772160
是buffercache的大小
目前是100多M
sga总体大小为200多M
buffercache占了二分之一要多
仔细查一下设置的值
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
sga设置的总大小sga_target为272M
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
db_cache_size的设置的值为默认值0，说明没有设置大小，用的全是由SGA自动调整
我们可以自己设置大小
我们设大小只能往大了设，往小了设没有意义
设的值比oracle系统分配给它的值小了
oracle不会理会这个自己设的值仍会采用自动分配的值
现在改一下
alter system set db_cache_size=200M scope=both;
SQL> alter system set db_cache_size=200M scope=both;
alter system set db_cache_size=200M scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
结果显示没有足够的内存去增加
就是sga_target已经就这么大了，都分完了
都分给各个池子了
这时要把DEFAULT buffer cache的值增加
要增加这个buffercache池子的大小的话
首先增加sga的大小
这地方首先说一下思路
首先增加sga_max_size大小
然后再增加sga_target
最后再增加db_cache_size
语句分别为
alter system set sga_max_size=500M scope=spfile;
alter system set sga_target=400M;
alter system set db_cache_size=200M;
其中的scope选项有三个可取值
scope=spfile
表示将参数值存入spfile文件，即改变oracle的初始化参数文件内容，在重启数据库后才能生效
scope=memory
表示改变正在内存中运行的实例的参数值，只在内存上修改，立即生效，
关闭实例此值就会丢失而被还原为原已保存的值，因为这个修改并没有写入到初始化参数文件
scope=both
表示将参数值同时存入spfile和memory，立即生效，因为存入了spfile重启数据库后仍然有效。
both是默认值，修改静态参数必须使用spfile，动态参数三种方式都可以用。
spfile文件的位置
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfilejiagulun.ora
为了保护数据库的安全有的参数进行了保护把它指定为静态状态
修改值时要加额外参数并且要数据库重启才能生效
但大部分参数是动态的可以随时修改随时生效
sga_max_size是静态参数，sga_target和db_cache_size是动态参数
这三个参数目前值
sga_max_size big integer 272M
sga_target big integer 272M
db_cache_size big integer 0
因为sga_max_size和sga_target当前值相同
所以这里必须先修改sga_max_size并重启数据库后
才能继续修改sga_target的值使其增加
在sga有足够空间时才能增加db_cache_size的值
我的修改过程
在sga_target把值增加后就会立即自动把剩余的空间全部分配给db_cache_size
以为是因为db_cache_size使用默认值0，
但手工对db_cache_size分配值后效果仍然一样。
在修改db_cache_size的值时
增加时是有限制的
sga要根据当前所有的状态值分析有没有足够的空间满足这个增大的值
减少时一般没限制
因为这个值即使设置小了但sga仍使用自动分配空间分配给db_cache_size
一般真实值不会减少，仍保持在合理状态
2）buffercache的合理值
有一个命令
SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');
可以通过这个命令查一下当前数据库buffercache该设多大
执行一下：
SQL> SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
2 estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
3 4 FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size'); 5
Cache Size (MB) SIZE_FACTOR Buffers EST_READ_FACTOR ESTD_PHY_RED EST_PHY_RED_T
--------------- ----------- ---------- --------------- ------------ -------------
16 .1 1996 1.7523 14677 77
32 .2 3992 1.1516 9646 48
48 .3 5988 1 8376 40
64 .4 7984 1 8376 40
80 .5 9980 1 8376 40
96 .6 11976 1 8376 40
112 .7 13972 1 8376 40
128 .8 15968 1 8376 40
144 .9 17964 1 8376 40
160 1 19960 1 8376 40
176 1.1 21956 1 8376 40
192 1.2 23952 1 8376 40
208 1.3 25948 1 8376 40
224 1.4 27944 1 8376 40
240 1.5 29940 1 8376 40
256 1.6 31936 1 8376 40
272 1.7 33932 1 8376 40
288 1.8 35928 1 8376 40
304 1.9 37924 1 8376 40
320 2 39920 1 8376 40
20 rows selected.
我们看一下这些列
Cache Size (MB)是假设的大小
如第一行结果
16 .1 1996 1.7523 14677 77
设16MB的话会有1996个buffer
当前数据库设置的块的大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
所以一个buffer是8K
16MB除以8K就是第一行Buffers列的值
EST_READ_FACTOR 为预估的物理读因子
ESTD_PHY_RED 预估的物理读的次数
EST_PHY_RED_T 预估的物理读时间
我们可以以物理读时间作为标准
因为buffercache最最突出的作用是缓存block
目的是减少物理io
这样就减少了物理io读的时间
如果设16M的话
预测的物理读次数14677，系统的物理读时间是77
如果设32M的话
预测的物理读次数9646，物理读时间是48
ESTD_PHY_RED和EST_PHY_RED_T基本上是同步的
次数多时间就长
对我们当前数据库来讲实际上
因为我的数据库没有任何负载48M就可以了
因为你设的再怎么大
它对物理读的次数和物理读的时间的减少没有任何意义
在实际生产中我们就是找这个数值
它对应的这行最后边三列的值，在此行以后的行这三列的数值变化不大的时候
找此行Cache Size (MB)值设置就可以了
视频课程中我们可以看到
老师个人比较喜欢
把平时要做的很多操作的一些sql语句提前给它找出来
找好了以后给它写到txt文件里面去
以后干活的时候在txt文件中找直接复制出来用就行了
这样它就成为了老师的一个工具
老师希望学生回去以后也要在这方面要去好好的去积累
积累以后你干活的时候有工具
你干活的时候就会非常的好
所以这些收集的语句都很重要
这个地方我们讲了buffercache该设多大怎么设
还教大家怎么看当前大小
这是buffercache
知道这个以后基本的操作基本上都会了
这节课主要内容讲了buffercache的设置
后面的课是block的状态
block状态其实应该是buffer的状态
里面好多东西可能大家不太理解需要详细的讲一下
内容还有一堆sql语句
2016年9月25日
文字：韵筝
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)