Oracle Wait Interface读书笔记之Buffer busy waits事件 [转]

时间:2021-02-14 04:04:04
来自 http://lancexu1212.spaces.live.com/

当进程需要存取SGA中的buffer的时候,它会依次执行如下步骤的操作:

1.获得cache buffers chains latch,遍历那条buffer chain直到找到需要的buffer header
2.根据需要进行的操作类型(读或写),它需要在buffer header上获得一个共享或独占模式的buffer pin或者buffer lock
3.若进程获得buffer header pin,它会释放获得的cache buffers chains latch,然后执行对buffer block的操作
4.若进程无法获得buffer header pin,它就会在buffer busy waits事件上等待

进程之所以无法获得buffer header pin,是因为为了保证数据的一致性,同一时刻一个block只能被一个进程pin住进行存取,因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候,这个进程就会产生对该block的buffer busy waits事件。

截至Oracle 9i,buffer busy waits事件的p1,p2,p3三个参数分别是file#,block#和id,分别表示等待的buffer block所在的文件编号,块编号和具体的等待原因编号,到了Oracle 10g,前两个参数没变,第3个参数变成了块类型编号,这一点可以通过查询v$event_name视图来进行验证:
  
  
  
   
   
   PHP code:
  
  
  


Oracle 9i

SQL
select parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits';

PARAMETER1                  PARAMETER2                 PARAMETER3

------------------------ ------------------------ ------------------------

file#                             block#                          id

Oracle 10g

PARAMETER1                  PARAMETER2                 PARAMETER3

------------------------ ------------------------ ------------------------

file#                             block#                          class#

在诊断buffer busy waits事件的过程中,获取如下信息会很有用:
1.获取产生buffer busy waits事件的等待原因编号,这可以通过查询该事件的p3参数值获得
2.获取产生此事件的SQL语句,可以通过如下的查询获得:
select sql_text from v$sql t1,v$session t2,v$session_wait t3
where t1.address=t2.sql_address and t1.hash_value=t2.sql_hash_value
and t2.sid=t3.sid and t3.event='buffer busy waits';
3.获取等待的块的类型以及所在的segment,可以通过如下查询获得:
  
  
  
   
   
   PHP code:
  
  
  


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 a
.header_file=b.p1 and b.p2 between a.header_block+and (a.header_block+a.freelist_groups) and a.freelist_groups>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 a
.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-and b.event='buffer busy waits' and not exists(select 1 from dba_segments where 

header_file
=b.p1 and header_blockb.p2);

查询的第一部分:如果等待的块类型是segment header,那么可以直接拿buffer busy waits事件的p1和p2参数去dba_segments视图中匹配header_file和header_block字段即可找到等待的segment名称和segment类型,进行相应调整
查询的第二部分:如果等待的块类型是freelist groups,也可以在dba_segments视图中找出对应的segment名称和segment类型,注意这里的参数p2表示的freelist groups的位置是在segment的header_block+1到header_block+freelist groups组数之间,并且freelist groups组数大于1
查询的第三部分:如果等待的块类型是普通的数据块,那么可以用p1、p2参数和dba_extents进行联合查询得到block所在的segment名称和segment类型

对于不同的等待块类型,我们采取不同的处理办法:
1.data segment header:
进程经常性的访问data segment header通常有两个原因:获取或修改process freelists信息、扩展高水位标记,针对第一种情况,进程频繁访问process freelists信息导致freelist争用,我们可以增大相应的segment对象的存储参数freelist或者freelist groups;若由于数据块频繁进出freelist而导致进程经常要修改freelist,则可以将pctfree值和pctused值设置较大的差距,从而避免数据块频繁进出freelist;对于第二种情况,由于该segment空间消耗很快,而设置的next extent过小,导致频繁扩展高水位标记,解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform
2.data block:
某一或某些数据块被多个进程同时读写,成为热点块,可以通过如下这些办法来解决这个问题:
(1)降低程序的并发度,如果程序中使用了parallel查询,降低parallel degree,以免多个parallel slave同时访问同样的数据对象而形成等待降低性能
(2)调整应用程序使之能读取较少的数据块就能获取所需的数据,减少buffer gets和physical reads
(3)减少同一个block中的记录数,使记录分布于更多的数据块中,这可以通过若干途径实现:可以调整segment对象的pctfree值,可以将segment重建到block size较小的表空间中,还可以用alter table minimize records_per_block语句减少每块中的记录数
(4)若热点块对象是类似自增id字段的索引,则可以将索引转换为反转索引,打散数据分布,分散热点块
3.undo segment header:
undo segment header争用是因为系统中undo segment不够,需要增加足够的undo segment,根据undo segment的管理方法,若是手工管理模式,需要修改rollback_segments初始化参数来增加rollback segment,若是自动管理模式,可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量
4.undo block:
undo block争用是由于应用程序中存在对数据的读和写同时进行,读进程需要到undo segment中去获得一致性数据,解决办法是错开应用程序修改数据和大量查询数据的时间

小结:buffer busy waits事件是oracle等待事件中比较复杂的一个,其形成原因很多,需要根据p3参数对照Oracle提供的原因代码表进行相应的诊断,10g以后则需要根据等待的block类型结合引起等待时间的具体SQL进行分析,采取相应的调整措施