Oracle Free Buffer Waits

等待事件free buffer waits与数据库写进程的活动紧密相关并且通常也会看到等待事件db file parallel write与log file parallel write(日志写进程等待)。然而free buffer waits等待事件是唯一要数据库写进程,IO子系统和服务器进程活动有趣的给合才能出现。

理解free buffer waits的关键是要了解push-to-disk问题与pull-from-LRU-chain问题之间的区别。当我们看到等待事件db file parallel write进入到报告的top等待事件中时,这个问题说明有太多的时间被花费在写或pushingdirty buffers到磁盘上了(buffers不是真实的移动到磁盘,而是复制,将造成块与buffer匹配来创建free buffer)。一个服务器进程当它不能足够快速地找到一个free buffer时就会posts一个free buffer waits事件。一种原因是数据库写进程没有从LRU chian中pulled足够的dirty buffers并且使它们再次变为free状态。因此它不是一个push-to-disk的问题,而是一个pull-from-LRU-chain问题。虽然这种差异可能被夸大了,但它对我们的解决方法有很大的影响。

如果我们关注pull问题,那么将尽一切可能确保数据库写进程不会落后于从buffer cache中获取dirty buffers太多。换句话说,我们的目标是让数据库写进程做更多的工作因为问题不是写入磁盘的问题。如果问题是写入磁盘的问题,那么top等待事件将是db db file parallel write而不是free buffer waits。

通过另一种buffer cache场景来突出显示free buffer wait的情况。假设你是一个服务器进程必须要查询一行记录,而记录存放在一个特定的数据块中。基于SQL语句与数据字典,你知道文件号与块号。你肯定希望这个数据块已经存放到buffer cache中了,但为了确认并获得它的内存地址,你必须访问CBC结构。通过哈希文件号与块号指向到一个特定的哈希桶。基于这个哈希桶,查找相关的latch并争取拿到这个latch。在经过一些spins操作后,可能能获得这个latch,因此可以开始序列化CBC搜索操作。然而这个CBC是空的并且没有包含buffer headers。因此知道这个数据块不在buffer cache中。为了得到这个数据块,给IO子系统执行读取调用并等待,将post一个db file squential read。最后,获得了这个数据块,停止等待,并且开始消耗CPU,现在这个数据块已经到了你的PGA内存中。

你需要找到一个free buffer来缓存这个块,因此要定位你的LRU chain。但在你可以开始扫描你的LRU chain之前,你必须要获得合适的LRU chain latch。最后使用你的LRU chain latch与在PGA内存中的块,你开始扫描LRU来查找一个free buffer。你首先遇到一个buffer header并检查这个buffer header。好消息是这个buffer是free状态,但是坏消息是它不被频繁访问,且touch count为12。因此promote这个buffer到LRU列表的MRU端,并且将touch count减小到0。

继续查找到下一个buffer header,检查buffer header并发现它是一个dirty buffer且touch count为1。不满足缺省频繁访问的阈值2。因此,移动buffer header到LRU chain’s写列表。在完成移动后,检查drity list长度确保
它小于_db_large_dirty_queue的值。dirty list只有6,它小于缺省值25,因此不需要通知数据库写进程执行写操作。

现在假设你已经扫描了比_db_writer_max_scan_pct更多的buffer headers。如果是这样将会很沮丧。将会要消耗大量的CPU与持有LRU chain latch相当长的时间。假设你已经扫描的buffer headers比这个阈值多,你现在停止扫描,释放LRU chain latch,通知数据库写进程释放一些buffers,并post等待事件free buffer waits而耐心等待10ms。当你正在感叹“free buffer wait!”有10ms时,数据库写进程正在忙于将dirty buffer写入磁盘,并释放它们,然后将它们再插入到LRU chain的LRU端。

现在已经等待了10ms,你被唤醒,再次获取LRU chain latch,并开始从LRU chain的LRU端搜索。现在很有可能有一个不被频繁访问的free buffer正在等待你执行替换操作。现在pin住buffer header,释放LRU latch,更新buffer header,合理移动CBC结构中的buffer header因此在你正将这个块放入cache时其它的进程可以找到这个块,使用从磁盘中读取的块来替换free buffer,然后unpin这个buffer header。

注意是什么导致服务器进程post这个free buffer wait事件了。首先,执行一个物理IO读取,将强制服务器进程来搜索一个free buffer。再次,需要扫描太多的dirty buffers,这意味着必须存在活动的DML语句。最后,数据库写进程没能确保在LRU chain的LRU端有足够的free buffers。这三种条件都会造成这种情况,这也意味着对于这个问题有三种解决方案。

如果top等待事件是free buffer waits,关注pull,而不是push问题。如果忘记这一点,将会采用不合适的解决方案。

操作系统可能会遇到CPU或IO瓶颈或都两者都有,但可能是IO瓶颈。等待事件free buffer waits从来没有通过关注操作系统而被解决。应该从Oracle与应用程序角度来解决这个问题。如果是CPU瓶颈,查找非Oracle消耗CPU的进程。
这里有以下解决方案:
.增加buffer cache
如果有可用内存,增加buffer cache大小。这将允许更多的buffers可用,将增加找到一个free buffer的可能性。

.增加数据库写进程的pull能力
例如增加数据库写进程的数量。做任何你认为可以帮助数据库写进程可以增加dirty buffer写入效率的事情。除非buffer cache非常小,那么这可能是你最好的解决方案了。

.增加_db_writer_max_scan_pct参数
这将给数据库写进程更多的时间来清除它的写列表。这将造成LRU chain latch的竞争,因为服务器进程在你放弃与post一个free buffer waits事件之前搜索更多的free buffers。

.减小写批处理大小的阈值
这将强制数据库更频繁的flush写列表,增加在LRU chain的LRU端存在free buffer的可能性。为了减小写批处理大小,减小_db_large_dirty_queue参数的大小。如果数据库写进程正忙于写dirty list中的buffer到磁盘时,服务器进程将不能移动一个dirty buffer到写列表中。如果一个服务器进程正在寻找一个free buffer,并尝试移动一个不被频繁访问的dirty buffer到正执行写操作的dirty列表中,它将等待,并post一个free buffer waits等待事件。如果为了解决db file parallel write问题而增加了写批理处理大小,它可能增加的大多了。这不是很常见,但可能发生。

应用层面有两种解决方案:
.查找并优化物理IO语句
没有从磁盘读取数据块就不会出现free buffer waits等待事件。找到top物理IO SQL语句。通常只有少量大的SQL语句消耗物理IO很明显。通过优化或降低它们的执行频率来减少物理IO量。

.查找并减少DML SQL语句的影响。
因为free buffer waits等待事件与LRU chain中有太多的dirty buffers相关,这就肯定存在DML SQL语句。DMLSQL可能很难被找到因为它可能是一个高物理IO,高逻辑IO,高执行频率或高CPU消耗的语句。它可能是很多统计信息的巧妙组合。如果不能查看SQL的类型,那么查看top物理IO与逻辑IO SQL语句,然后检查语句本身。很有可能DML SQL也是top物理IO SQL语句。如果是这样,你就已经找到了关键SQL语句。

因为修改windows管理员密码导致sql server 2005不能启动处理

由于修改了操作系统管理员密码,导致重启操作系统后Sql Server实例不能正常启动,错误信息如下:

查看事件查看器,在windows日志目录中点击系菜单,查看来源为Service Control Manager的错误信息可以看到错误原因如下

MSSQL$JY 服务无法使用当前配置的密码以 .\Administrator 身份登录,错误原因如下:
登录失败: 未知的用户名或错误密码。

接首查看下一个来源为Service Control Manager的错误信息可以看到错误原因如下:

由于下列错误,SQL Server (JY) 服务启动失败:
由于登录失败而无法启动服务。

由于登录失败而无法启动服务,而主要原因就是管理员用户密码被修改后与原来所记录的密码不对不能登录而导致不能启动。

修改 SQL Server(JY)服务登录选项卡中的用户密码有两种方式
1.通过SQL Server Configuration Manager来修改找到SQL Server(jy)右击—->属性,,选择登录选项卡,选择本帐户,重新输入管理员用户登录密码,点确定就OK了。

修改后点启动就可以了。

2.通过管理工具-服务,找到SQL Server(JY)点击属性,选择登录卡,修改此帐户选项中的管理员用户密码,修改后再启动就可以了。

Oracle Write List and Database Writer

Write lists通常也叫做dirty lists或LRU-W lists,是由整个dirty buffer headers组成。每个dirty bufferheader也存放在CBC结构中。Oracle有一个工作集的概念,它由LRU latch,LRU chain与write list组成。每个数据库写进程与一个或多个工作集关联。在实例启动时,Oracle将决定工作集的数据量与数据库写进程的数量(db_writer_processes,它的缺省值为1),然后设置它们的关联。当一个数据库写进程执行操作时,它将从它的写列表中获取信息。服务器进程与数据库写进程从它们瓣LRU chains中移动不被频繁访问的dirty buffers到与它们相关的写列表中。

数据库写进程的运转
多块写比单块写更有效,因此数据库写进程在执行写操作之前会构建一个dirty列表。多年以来,Oracle已经修改了实例参数与算法来控制最小的dirty列表的批量大小。事实上,Oracle使用了一种自调整的算法来计算dirty buffers的突发事件。当数据库写进程正在进行多块写操作时,它将出现db file parallel write等待事件。v$session_wait视图中的参数用来提供正在被写到磁盘的数据块数。数据库写进程的职责是将dirty buffers写入磁盘。执行下面的查询:

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       155

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       185

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       121
SQL>  select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       172

SQL>  select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       173

从上面的查询结果可以看到dirty buffer数量是呈循环性的增长与减少。循环的结果是当数据库写进程将dirty buffers写入磁盘时,这些dirty buffers将再一次变为free buffers。这种循环是正常的,也是想要看到的。如果dirty buffers的计数一直在增长,那么你就知道数据库写进程处理能力不足。对于大型Oracle系统来说通常有上千个dirty buffers存在。

我们都知道数据库写进程每3秒会被唤醒一次。通过跟踪来查看在Oracle 11g中是不是也是这样。注意休眠时间是大概3秒钟并且系统调用为semtimeodop。而当一个服务器进程在获取latch期间休眠时,因为它执行的是select系统调用。select不允许这具进程被唤醒,但信号量调用可以。这是很重要的区别,因为数据库写进程由于各种原因需要被唤醒,比如检查点操作或free buffer waits等待事件。

[root@db1 ~]# ps -eaf |grep dbw
oracle    49087      1  0  2018 ?        03:20:23 ora_dbw0_RLZY1
oracle    49089      1  0  2018 ?        03:19:18 ora_dbw1_RLZY1

[root@db1 ~]# strace -rp 49089
......
0.000298 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000482 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000336 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000608 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......

数据库写进程相关竞争的识别与解决
有各种与数据库写进程相关的等待事件。对争用情况进行分类的一种方法是理解数据库写进程是否有“push-to-disk”问题或“pull-from-write-list”问题。大多数的问题是push问题,也就是写磁盘的问题。但也有一种很常见的pull问题稍后再进行说明。与数据库写进程push-to-disk问题相关的所有等待事件都是以db file开始。与其它IO等待事件一样,在IO调用之前和之后,会执行gettimeofday调用,并且区别就是我们通过Oracle等待事件接口所看到的。这里有两个常见的数据库写进程push-to-disk等待事件:
.db file parallel write
目前最常见的数据库写进程等待事件,一个parallel写也可以简化为多块写。这是数据库写进程从写列表中获取数据并将dirty块批量写入磁盘的结果。希望这个等待事件的等待时间小于5ms,但每个单位有它自己的预算与服务要求。写操作的时间小于5ms说明写缓存工作的很好。

.db file single write
它不应该是top等待事件。当所有数据库文件头块在写入检查点操作的末尾可能会出现。这是通过一次执行多个单块写来完成的。

从需求和能力方面来查看IO问题。当存在一个IO问题时,需求已经超过能务了。只有当锁或阻塞类型出现时,比如free buffer waits事件才会出现例外。当看到数据库文件写操作出现问题时,除了锁或阻塞的原因之外,知道IO请求已经超过了IO子系统的能力。使用复杂IO管理将增加不同系统文件与数据库文件存储在相同磁盘上的机会。

IO问题可能变得非常情绪化。供应商参与进来并开始保护自己的地盘。为了帮助解决问题,从应用程序角度来看,将查找生成dirty buffers的SQL语句。将会找到一个或多个更新,插入与删除操作

从Oracle角度来分析,可以考虑任何可能增加Oracle IO写效率的参数。例如研究可以增加数据库写进程批量写大小的方法。修改数据库写进程的批量大小与数据库版本有关,例如_db_block_write_batch与_db_writer_max_writes参数。也可以考虑增加参数_db_writer_max_scan_pct(缺省值是40,例如40%)或_db_writer_max_scan_cnt,在触发数据库写进程开始执行写操作之前它们用来判断一个服务器进程将扫描多少LRU buffer headers。增加这些参数将提供更多时间来构建写列表,因此造成每个数据库写IO请求将写入更多数据块。这将有效的增加每秒写入磁盘的数据块。测试显示通过将db_writer_max_scan_pct从5增加到95,数据库写进程操作系统写调用将减少9%且db file parallel write等待减少3%,当事务活动增加14%时,每秒的块改变将增加19%。仅仅通过改变这个参数,当工作量增加时IO活动减少了。

另一种可能是遇到服务器进程不触发数据库写进程执行写操作,因此会构建写队列。当一个服务器进程,在搜索一个free buffer时,偶然发现一个不被频繁访问的dirty buffer后将其移动到相关的写列表,并且也会检查写列表是否足够长可以执行写操作了。如果写列表已经足够长了,服务器进程将触发数据库写进程执行写操作。因此这是一个有效的选择,为了允许构建写队列来导致大批量写操作,可以增加_db_large_dirty_queue(在某些系统中缺省值是25)参数。但创建太在的写队列需要小心。当dirty buffers正被写入磁盘时,它们不能被改变。任何需要改变正被写入磁盘的buffer都必须等待。相关的等待事件为write complete waits。在top等待事件中write complete waits不是很常见,但如果修改了写列队长度就可能会出现。

最后,从Oracle角度来看,增加buffer cache可以让数据库写进程减轻在短时间内强烈的数据块改变所带来的压力。一个大的buffer cache允许cache来填充dirty buffers,在不强制数据库执行写操作时,有更多的时间来创建大的且更有效的批量写。如果真的想给数据库写进程施加压力,创建一个小的buffer cache并执行一些DML操作,你将会看到数据库写进程疯狂地试图删除脏缓存区中的小缓存。

从操作系统角度来看,这应该存在IO瓶颈。这是很罕见的,如果IO写响应时间小于5ms而写缓存工作的很好,那么总的等待时间将足够大,因此db file parallel write等待事件将被推到top等待事件中。当出现这种情况时,关注IO子系统不会有什么实质性的效果。在这种情况下,关注减小应用程序IO写活动与增加Oracle写效率。这也意味着要在数据库活动高峰期来创造性的减小写操作。经验丰富的DBA已经看到了在正常业务期间存在写密集的IO活动,比如RMAN,血份与文件传输。减少非Oracle IO活动能有效地增加IO子系统的能力。

Proudly powered by WordPress | Indrajeet by Sus Hill.