mysql busy buffer_与buffer cache相关的等待事件—buffer busy waits等待事件！

在讲latch free事件的时候就说到，当一个session在读取或修改buffer cache里的内存的数据块时，首先需要获得cache buffers chains latch，获得之后，到hash chain上遍历直到找到需要的buffer header后，该session必须在该buffer header上以share或者exclusive模式获得一个buffer lock或一个buffer pin。一旦buffer header被lock或者pin住，session就会释放cache buffers chains latch，然后就可以在该buffer上进行操作了。如果暂时无法获得buffer pin，那么该session就会等待buffer busy waits等待事件，该等待事件不会出现在session的PGA里。

oracle提供了v$waitstat视图，视图记录的都是buffer busy waits等待事件发生时进行更新的，这个等待事件不能像latch free事件那样容易跟踪，也就是说在视图v$waitstat记录的都是buffer busy waits等待事件的统计数据，但这个视图统计的并不是明细的等待事件信息，如果要是诊断该等待事件，只有在碰见发生这个等待事件的时候，查询v$session_wait视图，从而才能找到解决办法，在处理buffer busy wait等待事件时，通过如下sql可以查询出发生等待的数据块类别和对应的segment：然后再根据不同的类型进行处理

select 'Segment Header' class,

a.segment_type, a.segment_name, a.partition_name

from dba_segments a, v$session_wait b

where a.header_file = b.p1

and a.header_block = b.p2

and b.event = 'buffer busy waits'

union

select 'Freelist Groups' class,

a.segment_type, a.segment_name, a.partition_name

from dba_segments a, v$session_wait b

where b.p2 between a.header_block + 1 and (a.header_block + a.freelist_groups)

and a.header_file = b.p1

and a.freelist_groups > 1

and b.event = 'buffer busy waits'

union

select a.segment_type || ' block' class,

a.segment_type, a.segment_name, a.partition_name

from dba_extents a, v$session_wait b

where b.p2 between a.block_id and a.block_id + a.blocks - 1

and a.file_id = b.p1

and b.event = 'buffer busy waits'

and not exists (select 1

from dba_segments

where header_file = b.p1

and header_block = b.p2);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

select'Segment Header'class,

a.segment_type,a.segment_name,a.partition_name

fromdba_segmentsa,v$session_waitb

wherea.header_file=b.p1

anda.header_block=b.p2

andb.event='buffer busy waits'

union

select'Freelist Groups'class,

a.segment_type,a.segment_name,a.partition_name

fromdba_segmentsa,v$session_waitb

whereb.p2betweena.header_block+1and(a.header_block+a.freelist_groups)

anda.header_file=b.p1

anda.freelist_groups>1

andb.event='buffer busy waits'

union

selecta.segment_type||' block'class,

a.segment_type,a.segment_name,a.partition_name

fromdba_extentsa,v$session_waitb

whereb.p2betweena.block_idanda.block_id+a.blocks-1

anda.file_id=b.p1

andb.event='buffer busy waits'

andnotexists(select1

fromdba_segments

whereheader_file=b.p1

andheader_block=b.p2);

1) 如果数据块类型为data block，如果版本为10g之前，则可以同时参照p3列的值来共同诊断。如果p3为130意味着同时有很多session在访问同一个data block，而且该data block没有在内存里，而必须从磁盘上获取。有三种方法可以降低该事件出现的频率：

a、降低并发性。这个比较难实现。

b、找出并优化含有这些segment的SQL语句，以降低物理和逻辑读。

c、增加freelists和freelist groups。

如果没有足够的freelists，当同时对同一个表进行insert时，这就很容易引起buffer busy waits等待。如果正在等待buffer busy waits的session正在进行insert操作，那么需要检查以下那个表有多少freelists了。当然，由于freelists的不足主要会导致对于segment header的buffer busy waits等待。

如果p3为220意味着有多个session同时修改在一个block(该block已经被读入内存了)里的不同的行。这种情况通常出现在高DML并发性的环境里。有三种方法可以降低该事件出现的频率：

a、降低并发性。这个比较难实现。

b、通过增加pctfree减少block里含有的行数。

c、将该对象移到拥有较小block尺寸的表空间里(9i或以上)。

2) 如果数据块类型为data segment header(表或索引的segment header，不是undo segment header)上发生buffer busy waits等待事件，通常表明数据库里有些表或索引的段头具有频繁的活动。

进程访问segment header主要有两种原因：一是获得或修改process freelists信息；二是扩展HWM。有三种方法可以降低该事件出现的频率：

a、增加争用对象的freelists和freelist groups的数量。

b、确定pctfree和pctused之间的间隔不要太小。

c、确保next extent的尺寸不要太小。

d、9i以后，使用ASSM特性来管理block。

3) 如果数据块类型为undo segment headers的争用等待，表明数据库中的rollback segments太少，或者他们的extent size太小，导致对于同一个segment header的大量更新。如果使用了9i以后的auto undo management，则不用处理，因为oracle会根据需要自动创建新的undo segments。如果是9i之前，则可以创建新的private rollback segments，并把它们online，或者通过降低transactions_per_rollback_segment参数来减轻该等待。

4) 如果数据块类型为undo block，说明有多个session同时访问那些被更新过的block。这是应用系统的问题，在数据库来说对此无能为力。

转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

最后编辑：2014-01-21作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL