Oracle Buffer Busy Waits

buffers不能被锁定，因为他们不是关系型结构。然而有许多原因，它们可以会临时不能使用。当出现这种情况时，buffer确实很忙。将所有复杂性能提练为本质的东西，一个buffer busy waits等待事件是关于受限的并发性。一个进程需要访问一个buffer，但不能访问它因为其它的进程正在访问buffer且不允许并发访问。在很多情况下都会出现这种情况，每种情况都有特定的解决方案。

使用buffer busy waits等待事件具有挑战性的是有多种可能的诊断方法。一种最常见的诊断buffer busy的方法是使用reason code。reason code由三个数字组成，每一个数字的值都揭示了为什么buffer正忙且不能被立即访问的的部分原因。但在Oracle 11g中，oracle从v$session_wait与v$session视图中删除了p3这一列。

在Oracle 10g中，Oracle采用了一种最常见的buffer busy情况并且指定了它特有的等待事件。因此可以看到read by other session等待事件用于buffer busy waits等待事件的一部分。虽然可能引起混淆但也使用的等待事件更加具体。

四步诊断法

解决buffer busy类型的等待事件(包括read by other session等待事件)的关键是首先要了解正在等待的buffer。仅仅知道top等待事件是buffer busy waits是不够的。在了解正确解决方案前将需要收集额外的信息。额外需要的信息是buffer的类型与是否它是header块。有了这些信息后，就能对这个问题制定一些解决方案集。为了解决这个问题有一个四步诊断方法如下:

1.确定是否存在参数模式

2.识别buffer类型

3.确定是否是header块

4.实现合适的解决方案集

确定是否存在参数模式

为了诊断重复抽样buffer busy waits等待事件的p1与p2参数。p1参数是buffer的文件号，p2参数是块号。正常情况当重复抽样p1与p2值时，它们将会出现变化，指示正在等待不同的buffers。通常也可以看到一些小数据块集关联到特定的对象。如果相同的buffer或看到两次出现虽然很罕见但要特别注意。它们确实是hot块。当你重复抽样时，注意参数值，分析时需要使用它们。

SQL> @swswp buffer%busy
Database: prod16 30-MAR-10 02:22pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
   ID Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4391  buffer busy waits            4            54        0
4379  buffer busy waits            4            54        0
4381  buffer busy waits            4            54        0
4405  buffer busy waits            5            10340
4 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2
SQL>

识别buffer类型

每一个oracle块buffer是Oracle段的一部分。每个Oracle段是一个段类型，比如数据段，索引段，undo段或者临时段。解决方案集部分基于段类型。使用buffer busy waits等待事件的p1(文件号)与p2(块号),通过查询dba_extents 与dba_data_files可以识别段类型

def file_id=&1
def block_id=&2
col a format a77 fold_after
set heading off verify off echo off
set termout off
col tablespace_name new_value ts_name
select ts.name tablespace_name
from v$tablespace ts, v$datafile df
where file# = &file_id
and ts.ts# = df.ts#
/

set termout on
select
'File number :'||&file_id a,
'Block number :'||█_id a,
'Owner :'||owner a,
'Segment name :'||segment_name a,
'Segment type :'||segment_type a,
'Tablespace :'||e.tablespace_name a,
'File name :'||f.file_name a
from dba_extents e,
dba_data_files f
where e.file_id = f.file_id
and e.file_id = &file_id
and e.block_id < = &block_id
and e.block_id + e.blocks > █_id
and e.tablespace_name = '&ts_name'

上面三个会话正在等待文件4的块54。执行下面的脚本来查看对象类型

SQL> @objfb 4 54
File number :4
Block number :54
Owner :OE
Segment name :ORDERS
Segment type :TABLE
Tablespace :USERS
File name :/u01/oradata/prod16/OE01.dbf
1 row selected.

确定是否是header块

每个Oracle段有一个header块。表段与undo块有一个单独的header块。段的第一个extent中的第一个块就是段的header块。header块不同于其它的对象块，因为他们包含一些特定的信息。这种特殊性依赖于段类型。这也就是为什么首先要确实段类型的原因。

视图dba_segments包含了关于单个Oracle段的信息。它也包含了header块的文件号与块号。因此一个简单查询将返回busy buffer是否是一个header块。

SQL>
1 select *
2 from dba_segments
3 where header_file = 4
4* and header_block = 54
SQL> /
no rows selected

在识别buffer busy模式，对象类型与是否是header块之后，就有足够的信息来直接选择可用的解决方案了。

实现合适的解决方案集

buffer busy waits等待事件在我们选择合适的解决方案集之前之所以能给我们诊断带来许多麻烦， 是因为我们必须首先确定busy pattern，buffer类型与是否是header块。

单个忙表块的解决方案

如果相同的单个buffer几乎总是busy buffer，那么我们需要找出原因。在我们的示例中，一组缓冲区处于繁忙状态，包括缓冲区4、54;也就是说，不仅仅是一个缓冲区几乎总是繁忙的。但是，如果只有一个繁忙的缓冲区，那么需要清楚地了解存储在块中的信息(可能只查询该块中的行)以及为什么应用程序对该块如此感兴趣。

可以基于等待会话的v$session.sql_id列来判断正在等待的 SQL 语句。甚至你可能需要与开发人员沟通，因为这种情况通常是与应用程序相关的。最常见的情况(但不总是)是Oracle 序列号不能被使用。当问为什么Oracle序列号不能被使用时，可能收到的回答是“我们想我们的应用程序独立于数据库”。

这种问题的原因是这种快速buffer访问给buffer的内部结构提出了难以置信的高并发要求。当内部结构被改变后，块对于其它进程是不能使用的，因此出现buffer busy waits等待事件。因为应用程序架构进行修改通常是不现实的，创造性的找到其它方法来解决问题。例如，如果每一行包含一个应用程序序列号，那么将行记录移动到它自己的块中并增加一个大的固定长度的列，或将块的pct_free属性设置为一个很高的值来保证存储最少的行记录。这种解决方法是痛苦，但没有buffer busy waits痛苦。

多个忙表的解决方案

多个忙表块最有可能出现buffer busy waits与read by other session等待事件。在这种情况下，每次检查busybuffers都是不同的，busy buffers是数据段并且它们不是头段。当出现这种情况时，原因通常是查询或者DML与查询的混合。

对于纯查询来说，这将发生在多个会话查询相关数据块时，并且相同块没有存放在buffer cache。第一个会话调用IO子系统并post一个db file sequential read或db file scattered read等待事件。对于其它会话也调用IO子系统来查询数据块是愚蠢的，因此它们不仅仅要等待第一个会话完成IO调用，还要等待数据块被存入buffer cache中。那么其它会话就可以像任何其它会话一样来访问buffer。当其它会话正等待第二个会话完成时，从Oracle 10g开始，等待会话将post一个read by other session等待事件，对于之前的版本，等待会话将post一个buffer busy waits等待事件。因此会话实际上是在等待另一个会话完成读取操作。

解决方案非常简单，并且主要集中在增加数据块存放在buffer cache中的可能性。如果buffer存放在buffer cache中，那么这种busy buffer waits的情况将不会发生。因此请考虑如何增加数据块在buffer cache中的可能性。

应用程序所关注的解决方案集中在找到top物理IO SQL语句并优化语句，关注减少物理IO。这个解决方案不仅由于块访问的减少而提高了语句性能，而且还减少了缓冲区繁忙等待的机会，并允许将来自其他对象的块存储在缓冲区缓存中。Oracle所关注的解决方案是增加buffer cache来增加数据块存放在buffer cache中的可能性因此而不需要进行IO调用。另一种有创意但不切实际的是减小数据库块大小。小块与随机行访问模式组合会造成更有效的buffer cache.换句话说，缓存更有效的存放真正被频繁访问的行。操作系统所关注的解决方案是减小IO读取响应时间。快速检索数据块，会话将等待更少的时间。

就我个人而言，我会同时认真考虑每个选项。但我预测性能变化是吞吐量增加且响应时间减少。

还有一些我可能一无所知的问题和事情需要考虑，也许还要考虑相关的业务与预算。因此我不会简单地从SQL或增加buffer cache开始。我将收集信息并帮助他人收集信息。然后一起共同制订计划。

另一种常见的非header数据块问题是在buffer被改变与被查询时出现的。通常这会调用DML与查询SQL语句，但DML SQL也可以在过滤时touch大量的buffers。可能出现的问题是DML正在更新内部buffer结构时而另一个进程想要查询数据块的内容或者也正想要更新内部buffer结构。记住这些改变不是行改变而是内部Oracle结构改变。如果有行或表锁问题，一个队列等待将会被posted。好的策略是集中减少并发。考虑减小块地密度(移动行到其它块或增加块的pct_free属性)，在高峰期间减小工作负载，并发活动，减少由DML与查询SQL所touched的buffers数量。

表段头块解决方案

表的第一个区的第一个块叫作段头块。与所有的段头块一样，它们包含非常特定的内部Oracle结构与它们的段类型相关。对于表段，头块的部分内容是关于块可以接受额外插入记录的位置。这些块也叫作free块。当一个进程必须插入行记录到一个表中，为了找到一个free块首先检索表的段头块，检索buffer，然后插入行记录。如果有许多进程并发插入行记录到相同的表中，一个表段头块将导致buffer busy waits等待事件。

幸运地是解决方案非常简单并且工作的很好。如果正使用手动段空间管理，那么段空间管理由free lists控制。Oracle的free list方法通常工作的很好，但在高并发情况下，现有的free lists无法处理工作负载。幸运地是，我们能很容易地修改表来在另一个段块中创建额外的free lists。这将导致一个头块减少被频繁访问的机率，因此减少buffer busy wait竞争。只要增加free lists直到竞争平息。free lists的数量可以在dba_segments视图中的free lists列中找到。如果这个列为空，那么我们知道free lists没有被使用而是使用自动段空间管理(ASSM)。

另一种选项更侧重于长期并且在维护期间可以执行。这就是将高并发段移动到使用ASSM的本地管理表空间中。ASSM不使用free lists，但使用位图来管理可用空间。这通常增加了表段头块的并发性。

Undo段头块解决方案

Undo段不同于表段因为它包括了与事务回滚和读一致性相关的信息。对于回滚与读一致性有一个Oracle结构叫作事务表。简单地说，事务表是到一个undo段内容的映射。每个undo段包含一个单独的事务表，它位于undo段头块中。当出现大量的DML，特别是与读一致性活动组合出现时，事务表将成为竞争点，在undo段头块上会出现buffer busy waits等待事件。在讨论如何解决这个问题之前先来了解事务表。

缺省情况下，每个Oracle事务会生成redo(前滚)与undo(回滚)信息，就像实际数据更改与可能的索引更改一样。undo信息被存储在undo段中，并且每个undo段的映射被存储在它的事务表中。每个undo段的事务表可以持有多个事务条目。从关系型数据库角度来考虑，每个条目关联事务表中的一行记录。有关键字表作为名字的一部分是不幸的，因为事务表不是一个关系型数据库结构。事实上，Oracle内核开发人员将事务表条目称作为slots(插槽)。

因为事务表被存储在Oracle块中，所以它能持有的slots数是有限的，这依赖于Oracle块大小。如果事务表被填满并且有新条目必须被添加，旧的非活动事务条目将被新条目覆盖。如果事务是活动的且在事务表中没有空间或者多个会话需要改变事务表，那么将出现buffer busy waits等待事件。

如果数据库没有使用自动undo管理(AUM)，而是使用了传统回滚段，那么解决方案很简单。就是创建额外的回滚段，这也将创建额外的事务表，因此分布事务表活动。将会注意到buffer busy waits竞争消失。记住增加额外的回滚段直到buffer busy waits等待事件从top等待事件中消失。

大多数Oracle系统现在都利用Oracle AUM功能。缺省情况下，Oracle尝试给每个undo段只指派一个活动事务。如果每个undo段有一个活动事务并且在undo表空间中有可用空间，Oracle将会自动创建额外的undo段。这通常要小心buffer busy waits等待。然而，如果在undo表空间中没有可用空间，多个事务将会被指派到一个undo段上，并且这最终将导致undo段头块竞争。解决方案就是向undo表空间增加另外的数据文件，因此能让Oracle创建额外的undo段，就是这么简单。

索引叶子块解决方案

简单地说，索引是有序结构。这种有序结构允许索引被用来执行快速搜索。对索引的任何改变必须导致对有序结构进行维护。如果有序结构不被维护，搜索将不能快速完成，并且索引将毫无价值且被损坏。因此有序结构是一个索引必须被维护的。这对性能有深远的影响。

当索引 排序 后能使用快速搜索，在高并发插入的情况下，它可能造成严重的性能问题。例如，假设一个索引是基于一个增长序列号(例如,1,2,3等等)，这通常叫作单调递增值。如果一个表列包含这个序列号并且列被索引，在索引叶子块中，各个索引项将彼此相邻放置。因为索引必须维护顺序，并且索引是基于升序排列的。

当许多会话并发插入记录时会出现问题。当每个插入语句获得下一个序列号时，索引条目很可能被物理地放置在相同的索引叶子块中，或者不太可能被放置在相邻的索引叶子块中。如果并发数足够，会话将需要等待另一个会话完成索引的叶子块更改而等待很长时间。当会话等待时，它将post一个buffer busy waits等待事件，并且被等待的buffer是一个索引叶子块。这种情况可能变得非常严重并且严重影响性能。

一种解决方案是让Oracle对该列创建反向键索引。从DBA角度来说，序列号和以前一样，但它的内部字节被反转了。因为索引条目必须以内部排序的方式来存储，索引条目将可能被存放在不同的叶子块中，这将有效的消除buffer busy waits等待。假设序列号用4个字节来表示。因此前四个序列号(1,2,3,4)将以0001,0010,0011与0100来表示。如果这四个值被索引，因为索引排序结构必须被维护，它们将彼此相邻而存储。然而如果它们的字节被反转，那么它们将以1000,0100,1100与0010来表示。因为它们必须被以排序的方式存储索引，它们将很可能不会被存储在相同的索引叶子块中。事实，它们将分散到所有的索引叶子块中。由于索引键反转的结果因此buffer busy waits将会消除。

创建一个反向键索引非常怎么着，使用与正常创建索引相同的DDL语句，只是简单地在结尾处增加了一个reverse关键字。

SQL> create unique index special_cases_rk_u1 on special_cases (object_id) reverse;
Index created.

因为在每个索引叶子块中有频繁的索引插入操作，为了减少叶子块的频繁分裂，录创建反向键索引时可以考虑增加pctfree 50的存储参数。

反向键的优点与缺点

反向键的缺点是当解决了buffer busy wait问题时，可能对查询性能有显著的影响。假设我们基于序列号列创建的索引优化了查询。但因为buffer busy waits问题，我们删除了nonreversed索引，然后创建了一个reversed索引。现在这些排序的序列号被分散到所有的索引叶子块中。高性能的索引范围扫描可能不能执行地很好。事实上基于成本的优化器应该能识别并选择另一种执行计划。否则查询可能潜在touch每个索引叶子块和大量的数据块。

当反向键索引可能增加插入并发与吞吐量时，也可能对之前已经优化过的查询有一些负面影响。对于性能优化来说，你的职责就是找到一种性能折中的方案或实现一种创造性解决方案(比如对索引或表进行分区)来对插入与查询操作来实现最大性能优化。