buffer busy waits 等待事件

虽说是buffer busy，还不如说是热块争用。（因为对于一个给定的数据块在buffer cache中的链表位置是固定的）
首先大概描述一下oracle怎么去修改数据块
1.首先根据块的地址（数据文件编号，块编号）计算出块所在的散列桶（bucket）。
2.找到保护该散列桶的cache buffer chain（cbc）latch地址，获取该闩锁。
3.搜索cache buffer chain，找到数据块所在的buffer，附加一个x模式的pin在buffer head的pin队列里。
4.释放cbc栓锁。
5.修改数据块。
6.再次获取闩锁，移除pin。
buffer busy wait等待会发生在第三步。如果其它进程已在buffer head中设置了pin，当前会话只能在pin等待队列中，直到其它会话释放pin后。
所以一般来说 buffer busy wait等待往往伴随有cache buffer chain（cbc）latch的等待事件。
有些buffer在buffer cache中被多个进程并发访问，就会造成buffer busy waits等待。

查找问题原因（主要确定buffer 所容纳的数据块的类型）
可以查询V$WAITSTAT视图的统计数据了解buffer等待的数据块类型，buffer中可能包括data block（数据块）, segment header（段头块）, undo header（undo段头块（事务表)）, undo block（undo 块）。
相关视图查询
检查v$session_wait，v$session_wait_history视图的如下列
P1: File ID（文件ID）P2: (块编号）P3: Class ID（分类号）
当会话处于buffer busy waits事件时首先查询V$SESSION视图定位到对象编号，列如

SELECT row_wait_obj# 
FROM V$SESSION 
WHERE EVENT = 'buffer busy waits';

查询具体的对象及对象类型，根据上面查询得到的值查询DBA_OBJECTS

SELECT owner, object_name, subobject_name, object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;

解决方法，根据具体的数据块类型，采用不同的方式去处理
1.Segment Header（段头块）
mssm管理下会存在段头块争用，这种争用更像是free list的争用。
(自动段空间管理在本地管理的表空间里不需要定义 PCTUSED, FREELISTS, FREELIST GROUPS个参数，如果可能，将手动段空间管理转换成自动段空间管理。)
字典管理的表空间不能切换到ASSM（自动段空间管理）。
空闲链表（free list)链接有空间空闲的块在同一个段的不同区间中（空闲块包括空闲空间没有超过pctfree或使用的空间在ptcused 以下）。
FREE LIST的个数受参数FREELISTS控制，缺省的freelist是1，最大freelist大小依赖于段头块数据块的大小。

SELECT SEGMENT_NAME, FREELISTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = segment name
AND SEGMENT_TYPE = segment type;

增大freelist，如果增大free list个数不起作用，可以使用free list 组，在rac环境，确保每个实例有其自己的 free list group(s)。

2.表中的数据块

由于多个进程同时读取或修改同一数据块，导致buffer的争用
如果争用的是表的数据块，从数据库层面来说，可以通过hash分区表来分散行所在的数据块。

这里做实验来体会。

首先随便找到表中的一个数据块，如下sql：

select dbms_rowid.rowid_block_number(rowid) from testtab where rownum<=1;

找到处于同一个块下的行，如下sql：

select rowid, t.*
  from testtab t
 where dbms_rowid.rowid_block_number(rowid) = 523

通过rowid频繁update 这个块重的不同行，如下代码：

会话一：（预先查询sid为191)

begin
  for i in 1 .. 1000000 loop
    update testtab
       set object_name = 'xxx'
     where rowid = 'AAASwgAAEAAAAILAAB';
  end loop;
  commit;
end;

会话二：（预先查询sid为131）

begin
  for i in 1 .. 1000000 loop
    update testtab
       set object_name = 'xxx'
     where rowid = 'AAASwgAAEAAAAILAAA';
  end loop;
  commit;
end;

　结果查询 

select * from v$session_wait_history  where sid in ('131','191') 

　

结果多半是buffer busy waits 等待以及 cache buffers chains latch的等待。　

3.索引块

检查是不是右手索引,例如使用使用序列生成键值，多个进程会并发插入到同一个叶子块。

考虑使用assm,全局散列分区索引或者反向键索引来分散争用点。

这里做实验来体会。

创建测试表以及序列

-- Create table
create table testtab2
(
  c1 number,
  c2 nvarchar2(200),
  c3 nvarchar2(200)
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table testtab2
  add constraint c1prm primary key (C1);
-- Create sequence 
create sequence testseq;

　开两个会话同时执行如下语句：（预先查询sid为12,70)　,

begin
for x in 1 .. 1000000 loop
      insert into testtab2 values (testseq.nextval, x - 1, x + 1);
end loop;
end;

　结果查询

select * from v$session_wait_history  where sid in ('12','70')

　

检查数据块类型 

select owner, segment_name, segment_type, tablespace_name
  from dba_extents
 where file_id = 4
   and 83284 between block_id and block_id + blocks - 1;

　

争用的数据块类型确实是索引。

4.撤销段头块
如果没有使用自动undo 管理，增加更多的回滚段。
undo块
如果没有使用自动undo管理，考虑更大的回滚段。