buffer busy waits-优快云博客

本文介绍了解决Oracle数据库中bufferbusywaits问题的方法。首先通过查询V$SESSION找到等待的具体对象类型，然后根据ROW_WAIT_OBJ#进一步确定冲突对象。针对不同类型的冲突块（如段头、数据块、撤销头等），提供了相应的解决措施，例如调整自由列表参数、使用自动段空间管理等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

buffer busy waits -----

解决思路：

查找等待的对象类型：

查找等待事件对应的SQL ，优化

[@more@]

Check the following V$SESSION_WAIT parameter columns:

P1 - File ID
P2 - Block ID
P3 - Class ID

10.3.2.1 Causes

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

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

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

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

10.3.2.2 Actions

The action required depends on the class of block contended for and the actual segment.

10.3.2.2.1 segment header

If the contention is on the segment header, then this is most likely free list contention.

Automatic segment-space management in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters. If possible, switch from manual space management to automatic segment-space management (ASSM).

The following information is relevant if you are unable to use automatic segment-space management (for example, because the tablespace uses dictionary space management).

A free list is a list of free data blocks that usually includes blocks existing in a number of different extents within the segment. Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED. Specify the number of process free lists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.

To find the current setting for free lists for that segment, run the following:

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

Set free lists, or increase the number of free lists. If adding more free lists does not alleviate the problem, then use free list groups (even in single instance this can make a difference). If using Oracle Real Application Clusters, then ensure that each instance has its own free list group(s).

10.3.2.2.2 data block

If the contention is on tables or indexes (not the segment header):

Check for right-hand indexes. These are indexes that are inserted into at the same point by many processes. For example, those that use sequence number generators for the key values.
Consider using automatic segment-space management (ASSM), global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.

10.3.2.2.3 undo header

For contention on rollback segment header:

If you are not using automatic undo management, then add more rollback segments.

10.3.2.2.4 undo block

For contention on rollback segment block:

If you are not using automatic undo management, then consider making rollback segment sizes larger.

获取sql

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';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66233/viewspace-1022949/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66233/viewspace-1022949/