Oracle : buffer busy waits

 http://www.adp-gmbh.ch/ora/tuning/buffer_busy_waits.html

 

buffer busy waits

A buffer busy wait occurs if multiple processes want to access a buffer in the buffer cache concurrently.

The type of buffer that causes the wait can be queried with v$waitstat. v$waitstat lists the waits per buffer type for buffer busy waits only. Therefore, the following SQL statement always returns 0

select
  (select total_waits from v$system_event
  where event='buffer busy waits')
-  /* minus */
  (select sum(count) from v$waitstat)
from
  dual;

Type of block waited for

• segment header:
  Problem: probably a freelist contention.
• data block:
  
• undo header:
  Problem: too few rollback segments (if not using undo tablespaces)
• undo block:
  Problem: too small rollback segments (if not using undo tablespaces)

Buffer busy waits is one of the events that Oracle records while it is running.

http://www.itpub.net/thread-815871-1-1.html

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

当进程需要存取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+1 and (a.header_block+a.freelist_groups) 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 a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where
header_file=b.p1 and header_block= b.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进行分析，采取相应的调整措施

 

 

http://www.dba-oracle.com/art_builder_bbw.htm

Causes of Oracle Buffer Busy Waits

One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:

Top 5 Timed Events
                                                          % Total
 Event                         Waits        Time (s)     Ela Time
 --------------------------- ------------ ----------- -----------
 db file sequential read       2,598        7,146           48.54
 db file scattered read       25,519        3,246           22.04
 library cache load lock         673        1,363            9.26
 CPU time                      2,154          934            7.83
 log file parallel write      19,157          837            5.68

The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.

The resolution of a "buffer busy wait"  events is one of the most confounding problems with Oracle.  In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes.  Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists to a table and index, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), and, of course, and adding a missing index to reduce buffer touches.

In order to look at system-wide wait events, we can query the v$system_event performance view. This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

select *

from

   v$system_event

where

   event like ‘%wait%’;

 

EVENT                       TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

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

buffer busy waits                636528           1557      549700   .863591232

write complete waits               1193              0       14799   12.4048617

free buffer waits                  1601              0         622   .388507183

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where COUNT is the sum of all waits for the class of block, and TIME is the sum of all wait times for that class:
select * from v$waitstat;

CLASS                   COUNT       TIME
 ------------------ ---------- ----------
 data block            1961113    1870278
 segment header          34535     159082
 undo header            233632      86239
 undo block               1886       1706

Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

• The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
   
• Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.

The v$session_wait performance view, shown below, can give some insight into what is being waited for and why the wait is occurring.

SQL> desc v$session_wait

 

 Name                                      Null?    Type

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

 SID                                                NUMBER

 SEQ#                                               NUMBER

 EVENT                                              VARCHAR2(64)

 P1TEXT                                             VARCHAR2(64)

 P1                                                 NUMBER

 P1RAW                                              RAW(4)

 P2TEXT                                             VARCHAR2(64)

 P2                                                 NUMBER

 P2RAW                                              RAW(4)

 P3TEXT                                             VARCHAR2(64)

 P3                                                 NUMBER

 P3RAW                                              RAW(4)

 WAIT_TIME                                          NUMBER

 SECONDS_IN_WAIT                                    NUMBER

 STATE                                              VARCHAR2(19)

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

• P1—The absolute file number for the data file involved in the wait.
   
• P2—The block number within the data file referenced in P1 that is being waited upon.
   
• P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'buffer busy waits';

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment:

select
   owner,
   segment_name,
   segment_type
from
   dba_extents
where
   file_id = &P1
and
  &P2 between block_id and block_id + blocks -1;

Once the segment is identified, the v$segment_statistics performance view facilitates real-time monitoring of segment-level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

select

   object_name,

   statistic_name,

   value

from

   V$SEGMENT_STATISTICS

where

   object_name = 'SOURCE$';

 

 

 

OBJECT_NAME   STATISTIC_NAME               VALUE

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

SOURCE$       logical reads                     11216

SOURCE$       buffer busy waits                   210

SOURCE$       db block changes                     32

SOURCE$       physical reads                    10365

SOURCE$       physical writes                       0

SOURCE$       physical reads direct                 0

SOURCE$       physical writes direct                0

SOURCE$       ITL waits                             0

SOURCE$       row lock waits

We can also query the dba_data_files to determine the file_name for the file involved in the wait by using the P1 value from v$session_wait for the file_id.

 

SQL> desc dba_data_files

 

 Name                                      Null?    Type

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

 FILE_NAME                                          VARCHAR2(513)

 FILE_ID                                            NUMBER

 TABLESPACE_NAME                                    VARCHAR2(30)

 BYTES                                              NUMBER

 BLOCKS                                             NUMBER

 STATUS                                             VARCHAR2(9)

 RELATIVE_FNO                                       NUMBER

 AUTOEXTENSIBLE                                     VARCHAR2(3)

 MAXBYTES                                           NUMBER

 MAXBLOCKS                                          NUMBER

 INCREMENT_BY                                       NUMBER

 USER_BYTES                                         NUMBER

 USER_BLOCKS                                        NUMBER

Interrogating the P3 (reason code) value from v$session_wait for a buffer busy wait event will tell us why the session is waiting. The reason codes range from 0 to 300 and can be decoded.

Code Reason for wait - A modification is happening on a SCUR or XCUR buffer but has not yet completed. 0 The block is being read into the buffer cache. 100 We want to NEW the block, but the block is currently being read by another session (most likely for undo). 110 We want the CURRENT block either shared or exclusive but the block is being read into cache by another session, so we have to wait until its read() is completed. 120 We want to get the block in current mode, but someone else is currently reading it into the cache. Wait for the user to complete the read. This occurs during buffer lookup. 130 Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block. 200 We want to NEW the block, but someone else is using the current copy, so we have to wait for that user to finish. 210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock, so does not show up as waiting very long. In this case, the statistic: "exchange deadlocks" is incremented, and we yield the CPU for the "buffer deadlock" wait event. 220 During buffer lookup for a CURRENT copy of a buffer, we have found the buffer but someone holds it in an incompatible mode, so we have to wait. 230 Trying to get a buffer in CR/CRX mode, but a modification has started on the buffer that has not yet been completed. 231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.

Reason codes

As I mentioned at the beginning of this article, buffer busy waits are prevalent in I/O-bound systems. I/O contention, resulting in waits for data blocks, is often due to numerous sessions repeatedly reading the same blocks, as when many sessions scan the same index.
In this scenario, session one scans the blocks in the buffer cache quickly, but then a block has to be read from disk. While session one awaits the disk read to complete, other sessions scanning the same index soon catch up to session one and want the same block currently being read from disk. This is where the buffer busy wait occurs—waiting for the buffer blocks that are being read from disk.

The following rules of thumb may be useful for resolving each of the noted contention situations:

• Data block contention—Identify and eliminate HOT blocks from the application via changing PCTFREE and or PCTUSED values to reduce the number of rows per data block. Check for repeatedly scanned indexes. Since each transaction updating a block requires a transaction entry, increase the INITRANS value.
   
• Freelist block contention—Increase the FREELISTS value. Also, when using Parallel Server, be certain that each instance has its own FREELIST GROUPs.
   
• Segment header contention—Again, increase the number of FREELISTs and use FREELIST GROUPs, which can make a difference even within a single instance.
   
• Undo header contention—Increase the number of rollback segments.

Mark Bobak notes that buffer busy waits with P3=0 indicate disk I/O contention, indicating the freelists will not improve concurrency:

"buffer busy wait w/ P3=0 means the buffer is locked because the contents are being read from disk by another session. (See MOSC Doc ID 34405.1 for more details.)

This is most likely caused by multiple, concurrent sessions that are reading the same table or set of tables. In my experience, it's most often due to multiple, concurrent queries doing full table scans on the same table.

Since this a read concurrency problem, changing freelists will NOT help."

 In these cases, buffering-up the tables (e.g. KEEP pool), or using faster storage (SSD) can remove this disk enqueue wait evsnts. Rewards

The identification and resolution of buffer busy waits can be very complex and confusing. Oracle provides the v$segment_statistics view to help monitor buffer busy waits, and the v$system_event views to identify the specific blocks for the buffer busy wait. While identifying and correcting the causes of buffer busy waits is not an intuitive process, the results of your efforts can be quite rewarding.


 http://allappsdba.blogspot.tw/2012/04/troubleshooting-buffer-busy-waits.html

Troubleshooting BUFFER BUSY WAITS

Note:

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.

BUFFER BUSY WAITS COUNT ON DATABASE:

set pagesize 5000

set lines 180

set long 5000

col username for a15

col osuser for a15

col program for a20

col "LOGON_TIME" for a23

col status for a8

col machine for a15

col SQL_TEXT for a90

col EVENT for a50

col P1TEXT for a10

col P2TEXT for a10

col P3TEXT for a10

col p1 for 9999999999999

col p2 for 9999999999999

col p3 for 9999999999999

col "LAST_CALL_HRS" for 99999.999

select event,count(event) "BUFFER_BUSY_WAITS/LOCK_COUNT" from v$session_wait having count(event)>= 1 and event like '%buffer busy waits%' group by event;

BUFFER_BUSY_WAITS SESSIONS DETAIL:

col event for a10

select s.sid,username,osuser,program,machine,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",last_call_et/3600 "LAST_CALL_HRS",sw.event from

v$session s,v$session_wait sw where s.sid=sw.sid and sw.event like '%buffer busy waits%';

SQL_TEXT OF BUFFER_BUSY_WAITS SESSIONS:

col "EVENT" for a25

select s.sid,username "USERNAME",sql_text "SQL_TEXT",sw.event "EVENT" from v$session s,v$session_wait sw,v$sqltext sq where s.sid=sw.sid and

sq.address = s.sql_address and sw.event like '%buffer busy waits% order by sw.sid,piece';

TYPE_OF_SEGMENT_CONTENDED_FOR

SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;

USE THE BELOW SQL_FILE TO IDENTIFY THE SEGMENT

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

SELECT owner,segment_name,segment_type FROM dba_extents WHERE file_id=&file AND &block_id BETWEEN block_id AND block_id + blocks -1 ;