Buffer busy waits

成都創(chuàng)新互聯(lián)專注于云龍企業(yè)網(wǎng)站建設(shè),自適應(yīng)網(wǎng)站建設(shè),商城系統(tǒng)網(wǎng)站開發(fā)。云龍網(wǎng)站建設(shè)公司,為云龍等地區(qū)提供建站服務(wù)。全流程按需制作，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

一： B uffer busy waits 說明

二： B uffer bus y waits 解決思路

三： B uffer busy waits 重現(xiàn)過程

四： B uffer busy waits 官方文檔

一： B uffer busy waits 說明

--- 參考《 Oracle 內(nèi)核技術(shù)解密》

進(jìn)程在Buffer Cache中搜索buffer過程(邏輯讀過程)：

(1)進(jìn)程根據(jù)要訪問的文件號、塊號，計(jì)算HASH值。

(2)根據(jù)HASH值找到HASH Bucket。

(3)搜索Bucket后的鏈表，查找哪個(gè)BH是目標(biāo)BH(Buffer Header)。

(4)找到目標(biāo)BH，從中取出Buffer的BA(Buffer Address)。

(5)按BA訪問Buffer。

其中：

每個(gè)HASH Bucket都保存一個(gè)指向Cache Buffers Cache鏈表(CBC鏈表)的鏈表頭。

BH(Buffer Header)記錄了具體的文件號，塊號，BA，BUFFER Pin等信息。

第(3) 步 ) 搜索 Bucket 后的鏈表需要 Latch 保護(hù)。

第(4) 步 ) 訪問 BH 中的 BA 需要 Latch 保護(hù)。

這個(gè)Latch就是Cache Buffer Chain Latch(簡稱CBC Latch)。

在訪問CBC鏈表之前，先獲取CBC Latch，查找對應(yīng)的BH，并在找到的BH上加Buffer Pin，修改鎖的狀態(tài)(S共享,X獨(dú)占)，修改完成后就可以進(jìn)一步訪問Buffer。

在開始訪問Buffer的時(shí)候，CBC Latch已經(jīng)釋放了，Buffer的訪問是在Buffer pin鎖的保護(hù)下完成的。

訪問完Buffer后，需要修改Buffer Pin鎖，修改Buffer Pin時(shí)也需要CBC Latch保護(hù)，最后才可以釋放Buffer Pin鎖。

CBC Latch作用:保護(hù)鏈表，保護(hù)BH；

如果在找到BH后，無法馬上申請到Buffer Pin時(shí)，就會出現(xiàn) Buffer busy waits等待；

http://bbs.chinaunix.net/thread-3635879-1-1.html

進(jìn)程之所以無法獲得buffer header pin，是因?yàn)闉榱吮ＷC數(shù)據(jù)的一致性，同一時(shí)刻一個(gè)block只能被一個(gè)進(jìn)程pin住進(jìn)行存取，因此當(dāng)一個(gè)進(jìn)程需要存取buffer cache中一個(gè)被其他進(jìn)程使用的block的時(shí)候，這個(gè)進(jìn)程就會產(chǎn)生對該block的buffer busy waits事件。

例如：

會話A嘗試修改內(nèi)存中某個(gè)Buffer某一行數(shù)據(jù)時(shí)，發(fā)現(xiàn)會話B正在修改內(nèi)存中的相同Buffer不同行數(shù)據(jù)時(shí)，會話A會出現(xiàn)Buffer busy waits等待；

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

如果你的數(shù)據(jù)庫里讀極多，寫極少，由于各個(gè)讀之間的buffer pin是兼容的，都是s模式，因此不會產(chǎn)生任何的爭用。
如果你的數(shù)據(jù)庫里寫極多，讀極小，就會產(chǎn)生buffer busy waits等待，但是這種等待的代價(jià)比cbc latch的等待代價(jià)要小的多，latch的spin機(jī)制是非常耗cpu的，而buffer pin的管理本質(zhì)上類似于enq 鎖的機(jī)制，沒有spin機(jī)制，不需要自旋耗費(fèi)大量的cpu。
如果你的數(shù)據(jù)庫是讀寫混合的場景，那么寫會阻塞讀，產(chǎn)生buffer busy waits，但是讀不會阻塞寫，不會產(chǎn)生這個(gè)等待。

Oracle 訪問或修改 buffer 步驟：

1）依據(jù)數(shù)據(jù)塊的地址計(jì)算出數(shù)據(jù)塊所在的bucket
2）獲得保護(hù)這個(gè)bucket的cbc latch
3）在這個(gè)鏈表上找尋我們需要的數(shù)據(jù)塊，找到后，pin這個(gè)buffer（讀取s，修改x）
4）釋放cbc latch
5）讀取/修改數(shù)據(jù)塊的內(nèi)容
6）獲取cbc latch
7）unpin這個(gè)buffer
8）釋放cbc latch

最后我們可以來一個(gè)總結(jié)了：
1）buffer busy waits是產(chǎn)生在buffer block上的等待，由于n個(gè)進(jìn)程想以不兼容的模式獲得buffer block的buffer pin，進(jìn)而引起buffer busy waits等待。
2）buffer lock的管理模式非常類似enq鎖的管理模式，先進(jìn)先出，有隊(duì)列去記錄鎖的擁有者和等待著。
3）寫寫，讀寫都會產(chǎn)生buffer busy wiats等待。寫寫的兩個(gè)會話，都會產(chǎn)生buffer busy wiaits等待，而讀寫的兩個(gè)會話，只有讀的session會產(chǎn)生，因?yàn)樗荒苋ズ唵蔚腸lone一個(gè)內(nèi)存塊，正在發(fā)生寫的內(nèi)存塊發(fā)生克隆是不安全的
4）oracle為了解決cbc latch持有時(shí)間過長的問題，以每次訪問buffer block的會話獲取兩次cbc latch，再配合在內(nèi)存塊上加buffer pin來解決這個(gè)問題。

說明：oracle并不是針對所有的內(nèi)存塊都采取兩次獲取cbc latch的機(jī)制，比如針對索引root,索引branch，唯一索引的葉子節(jié)點(diǎn)，都是采取的一次獲取機(jī)制。

https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.

二： B uffer busy waits 解決思路

1：查找buffer busy waits對應(yīng)的對象及對象類型

(1) 查找對象號

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

(2)  通過對象號，查找對應(yīng)的對象信息

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

或者通過SID查找對應(yīng)塊號，文件號，類型

select event, sid, p1, p2, p3

  from v$session_wait

 where sid in (69, 75)

   and event like '%buffer busy waits%';

---

P1: File ID

P2: Block ID

P3: Class ID

p1、p2參數(shù)和dba_extents進(jìn)行聯(lián)合查詢得到block所在的segment名稱和segment類型

(3) 查找對應(yīng)的 SQL 信息

select sql_text

  from V$sqlarea

 where (address, hash_value) in

       (select sql_address, sql_hash_value

          from v$session

         where event like '%buffer busy waits%');

或

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

2：按照不同的對象類型，有不同的解決方案

http://bbs.chinaunix.net/thread-3635879-1-1.html

對于不同的等待塊類型，我們采取不同的處理辦法：
1.data segment header
進(jìn)程經(jīng)常性的訪問 data segment header通常有兩個(gè)原因

(1) 獲取或修改process freelists信息

進(jìn)程頻繁訪問process freelists信息導(dǎo)致freelist爭用，我們可以增大相應(yīng)的segment對象的存儲參數(shù)freelist或者freelist groups；若由于數(shù)據(jù)塊頻繁進(jìn)出freelist而導(dǎo)致進(jìn)程經(jīng)常要修改freelist，則可以將pctfree值和pctused值設(shè)置較大的差距，從而避免數(shù)據(jù)塊頻繁進(jìn)出freelist；

(2)擴(kuò)展高水位標(biāo)記 

由于該segment空間消耗很快，而設(shè)置的next extent過小，導(dǎo)致頻繁擴(kuò)展高水位標(biāo)記，解決的辦法是增大segment對象的存儲參數(shù)next extent或者直接在創(chuàng)建表空間的時(shí)候設(shè)置extent size uniform；

2. data block
某一或某些數(shù)據(jù)塊被多個(gè)進(jìn)程同時(shí)讀寫，成為熱點(diǎn)塊，可以通過如下這些辦法來解決這個(gè)問題：
(1)降低程序的并發(fā)度，如果程序中使用了parallel查詢，降低parallel degree，以免多個(gè)parallel slave同時(shí)訪問同樣的數(shù)據(jù)對象而形成等待降低性能；
(2)調(diào)整應(yīng)用程序使之能讀取較少的數(shù)據(jù)塊就能獲取所需的數(shù)據(jù)，減少buffer gets和physical reads；
(3)減少同一個(gè)block中的記錄數(shù)，使記錄分布于更多的數(shù)據(jù)塊中，這可以通過若干途徑實(shí)現(xiàn)：可以調(diào)整segment對象的pctfree值，可以將segment重建到block size較小的表空間中，還可以用alter table minimize records_per_block語句減少每塊中的記錄數(shù)；
(4)若熱點(diǎn)塊對象是類似自增id字段的索引，則可以將索引轉(zhuǎn)換為反轉(zhuǎn)索引，打散數(shù)據(jù)分布，分散熱點(diǎn)塊；

3.undo segment header
undo segment header爭用是因?yàn)橄到y(tǒng)中undo segment不夠，需要增加足夠的undo segment，根據(jù)undo segment的 管理方法，若是手工管理模式，需要修改rollback_segments初始化參數(shù)來增加rollback segment，若是自動(dòng)管理模式，可以減小transactions_per_rollback_segment初始化參數(shù)的值來使oracle自動(dòng)增多rollback segment的數(shù)量

4.undo block
undo block爭用是由于應(yīng)用程序中存在對數(shù)據(jù)的讀和寫同時(shí)進(jìn)行，讀進(jìn)程需要到undo segment中去獲得一致性數(shù)據(jù)，解決辦法是錯(cuò)開應(yīng)用程序修改數(shù)據(jù)和大量查詢數(shù)據(jù)的時(shí)間

小結(jié)：buffer busy waits事件是oracle等待事件中比較復(fù)雜的一個(gè)，其形成原因很多，需要根據(jù)p3參數(shù)對照Oracle提供的原因代碼表進(jìn)行相應(yīng)的診斷，10g以后則需要根據(jù)等待的block類型結(jié)合引起等待時(shí)間的具體SQL進(jìn)行分析，采取相應(yīng)的調(diào)整措施

一：帶有原因碼130的數(shù)據(jù)塊(類#1)爭用

1)等待集中在數(shù)據(jù)塊上，并且原因碼是130，則意味著多個(gè)會話并發(fā)請求相同的數(shù)據(jù)塊，但該數(shù)據(jù)塊并不在緩沖存儲器中，并且必須從磁盤讀取。

2)當(dāng)多個(gè)會話請求不在buffer cache中的相同數(shù)據(jù)塊時(shí)，ORACLE為了防止每個(gè)會話進(jìn)行相同的操作系統(tǒng)I/O調(diào)用。否則，這可能嚴(yán)重地增加系統(tǒng)I/O的數(shù)量，所以，ORACLE只允許一個(gè)會話執(zhí)行實(shí)際的I/O，而其他的會話在buffer busy waits上等待塊，執(zhí)行I/O的會話在db file sequential read或db file scattered read等待事件上等待。

3)可在v$session視圖中檢查SESSION的注冊時(shí)間，并且等待事件db file sequential(scattered) read和buffer busy waits等待相同的文件號和塊號。

4)解決方法：優(yōu)化SQL語句，盡可能地減少邏輯讀和物理讀；

二： 帶有原因碼220的數(shù)據(jù)塊(類#1)爭用

1)等待集中在數(shù)據(jù)塊上，并且原因碼是220，則意味著多個(gè)會話同時(shí)在相同的對象上執(zhí)行DML(相同塊中的不同行)。

2)如果數(shù)據(jù)塊的尺寸較大(>=16K)，則可能強(qiáng)化這種現(xiàn)象，因?yàn)檩^大的塊一般在每個(gè)塊中包含更多的行。

3)減少這種情況的等待的方法：減少并發(fā)；減少塊中行的數(shù)量；在另一個(gè)具有較小塊尺寸的表空間中重新構(gòu)建對象。

4)具體方法說明：

使用較大的PCTFREE重新構(gòu)建表或索引；

使用alter table  minimize records_pre_block命令改變表以最小化每個(gè)塊的最小行數(shù)

從ORACLE9i開始，可以在另一個(gè)具有較小塊尺寸的表空間中移動(dòng)或重新構(gòu)建對象。

注：雖然這些方法可以最小化buffer busy waits問題，但它們無疑會增加全表掃描時(shí)間和磁盤空間利用率。

三： 數(shù)據(jù)段頭(類#4)的爭用

1)如果buffer busy waits的等待事件主要集中在數(shù)據(jù)段頭(即表或索引段頭，并且不是UNDO段頭)上，這意味著 數(shù)據(jù)庫中一些表或索引有高段頭活動(dòng)。

注：進(jìn)程出于兩個(gè)主要原因訪問段頭，一是，獲得或修改FREELISTS信息；二是，為了擴(kuò)展高水位標(biāo)記(HWM)。

2)減少這種情況的等待的方法：

>> 對使用自由表進(jìn)行段管理的表，增加確認(rèn)對象的FREELISTS和FREELIST GROUPS(注：FREELIST GROUPS的增加也是必須的)；

>> 確保FCTFREE和PCTUSED之間的間隙不是太小，從而可以最小化FREELIST的塊循環(huán)。

>> 下一區(qū)的尺寸不能太小，當(dāng)區(qū)高速擴(kuò)張時(shí)，建立的新區(qū)需要修改在段頭中區(qū)映射表。可以考慮將對象移動(dòng)到合理的、統(tǒng)一尺寸的本地管理的表空間中。

四： 撤銷段頭(類#17)的爭用

1)如果buffer busy waits等待事件主要集中在撤銷段頭，這表明數(shù)據(jù)庫中的回滾段過少或者是它們的區(qū)尺寸太小，從而造成對段頭的頻繁更新。如果使用ORACLE9I的由數(shù)據(jù)庫系統(tǒng)管理UNDO段，就不需要處理這種問題，因?yàn)镺RACLE會根據(jù)需要增加額外的的UNDO段。

2)可以創(chuàng)建并啟用私有回滾段，以減少每個(gè)回滾段的事務(wù)數(shù)量。需要修改init.ora文件中的ROLLBACK_SEGMENTS參數(shù)。

3)如果使用公用回滾段可以減少初始化參數(shù)transactions_per_rollback_segment的值，ORACLE通過transactions/transactions_per_rollback_segment來獲取公有回滾段的最小數(shù)量。

五： 撤銷塊的爭用(類#18)

1)如果buffer busy waits等待事件主要集中在撤銷塊上，這表明有多個(gè)并發(fā)會話為保證一致性讀同時(shí)查詢更新的數(shù)據(jù)。

2)這是應(yīng)用程序存在問題，當(dāng)應(yīng)用程序在不同時(shí)間內(nèi)運(yùn)行查詢和DML時(shí)，這種問題不會存在。

三： B uffer busy waits 重現(xiàn)過程
---conn chenjch/a

---1  sid=69

declare

    j number;

begin

for i in 1..30000000 loop

    update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAA';

end loop;

end;

/

---2  sid=75

begin

for i in 1..30000000 loop

    update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAB';

end loop;

commit;

end;

/

---查看會話等待事件

select event, sid, p1, p2, p3

  from v$session_wait

 where sid in (69, 75)

   and event like '%buffer%';

select event, sid, p1, p2, p3

  from v$session_wait_history

 where sid in (69, 75)

   and event like '%buffer%';

---判斷等待事件訪問的塊類型

select * from v$waitstat where count >0;

---查看引起‘buffer busy waits’事件的sql語句

select sql_text

  from V$sqlarea

 where (address, hash_value) in

       (select sql_address, sql_hash_value

          from v$session

         where event like '%buffer busy%');

---查看引起‘buffer busy waits’事件的塊類型

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

---查看引起‘buffer busy waits’事件的segment

select owner,segment_name,segment_type,block_id from dba_extents where file_id=6

---查詢含有t1的sql

select sql_text from v$sqlarea where sql_text like ‘%t1%’;

SELECT *

  FROM v$segment_statistics s

 WHERE s.statistic_name = 'buffer busy waits'

   AND s.owner = 'CHENJCH';

SELECT row_wait_obj#

  FROM V$SESSION

 WHERE EVENT = 'buffer busy waits';

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

四： B uffer busy waits 官方文檔

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

Wait Event :buffer busy waits

General Area :Buffer cache, DBWR

Possible Causes :

Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.

Look for / Examine :

Examine V$SESSION while the problem is occurring to determine the type of block in contention.

10.3.1  buffer busy waits

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, segmentheader, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

P1: File ID

P2: Block ID

P3: Class ID

10.3.1.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.1.2  Actions

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

10.3.1.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 FREELISTGROUPS 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 ASSM (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 several 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 RAC, then ensure that each instance has its own free list group(s).

See Also:

Oracle Database Concepts for information about automatic segment-space management, free lists, PCTFREE, and PCTUSED

10.3.1.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 ASSM, global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.

10.3.1.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.1.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.

https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

buffer busy waits

Wait until a buffer becomes available.

There are four reasons that a session cannot pin a buffer in the buffer cache, and a separate wait event exists for each reason:

1." buffer busy waits": A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

2." read by other session": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.

3." gc buffer busy acquire": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.

4." gc buffer busy release": A session cannot pin the buffer in the buffer cache because another session on another instance is taking the buffer from this cache into its own cache so it can pin it.

Prior to release 10.1, all four reasons were covered by "buffer busy waits."

 In release 10.1, the "gc buffer busy" wait event covered both the "gc buffer busy acquire" and "gc buffer busy release" wait events.

Wait Time: Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds.

https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=347530962988293&id=34405.1&_afrWindowMode=0&_adf.ctrl-state=gvalc855z_4

WAITEVENT: "buffer busy waits" Reference Note (文檔 ID 34405.1)

***Checked for relevance on 14-Jun-2012***

"buffer busy waits" Reference Note

This is a reference note for the wait event  "buffer busy waits" which includes the following subsections:

·  Brief definition

·  Individual wait details  (eg: For waits seen in <>)

·  Systemwide wait details  (eg: For waits seen in <>)

·  Reducing waits / wait times

See  Note:61998.1 for an introduction to Wait Events.

Definition:

·  Versions:7.0 - 10.2  Documentation:  9.0

· This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy". The two main cases where this can occur are:

1. Another session is reading the block into the buffer

2. Another session holds the buffer in an incompatible mode to our request

Individual Waits:

   Parameters:

·  P1 =   file# (Absolute File# in Oracle8 onwards)

·  P2 =   block#

·  P3 =   id (Reason Code)/Block Class# in 10g

file# (Absolute File# in Oracle8 onwards)

This is the file number of the data file that contains the block that the waiting session wants.

block#

This is the block number in the above  file# that the waiting session wants access to.
See  Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.

id (Reason Code)

The buffer busy wait event is called from different places in the Oracle code. Each place in the code uses a different "Reason Code" . These codes can differ between versions thus:

Buffer Busy Waits ID's and Meanings

  Wait Time:

Normal wait time is 1 second. If the session has been waiting for an exclusive buffer during the last wait then it waits 3 seconds this wait. The session will keep timing-out/waiting until it acquires the buffer.

   Finding Blockers:

Finding the blocking process can be quite difficult as the information required is not externalised. If  P3 (Reason Code) shows that the "buffer busy wait" is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file sequential read" or "db file scattered read") for the same  file# and  block#.

If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon. If not then it is advisable to contact Oracle Support and get 3  SYSTEMSTATE dumps at one minute intervals as the blocking session may be spinning. (Look for ACTIVE sessions with high CPU utilisation).

Systemwide Waits:

If the TIME spent waiting for buffers is significant then it is best to determine which segment/s is/are suffering from contention. The "Buffer busy wait statistics" section of the Bstat/estat or STATSPACK reports shows which block type/s are seeing the most contention. This information is derived from <> which can be queried in isolation:

  SELECT time, count, class

    FROM V$WAITSTAT

   ORDER BY time,count

  ;

This shows the  class of block with the most waits at the BOTTOM of the list.

Oracle Support may also request that the following query be run to show where the block is held from when a wait occurs:

  SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"

    FROM x$kcbsw s, x$kcbwh w

   WHERE s.indx=w.indx

     and s."OTHER_WAIT">0

   ORDER BY 3

  ;

  Note: "OTHER_WAIT" is "OTHER WAIT" in Oracle8i (a space rather than an underscore)

Additional information regarding which files contain the blocks being waited for can be obtained from the internal <> thus:

  SELECT count, file#, name

    FROM x$kcbfwait, v$datafile

   WHERE indx + 1 = file#

   ORDER BY count

  ;

This shows the file/s with the most waits (at the BOTTOM of the list) so by combining the above of information we know what block type/s in which file/s are causing waits. The segments in each file can be seen using a query like:

  SELECT distinct owner, segment_name, segment_type

    FROM dba_extents

   WHERE file_id= &FILE_ID

  ;

If there are a large number of segments of the type listed then monitoring <> may help isolate which object is causing the waits.
Eg: Repeatedly run the following statement and collect the output. After a period of time sort the results to see which file & blocks are showing contention:

  SELECT p1 "File", p2 "Block", p3 "Reason"

    FROM v$session_wait

   WHERE event='buffer busy waits'

  ;

Note:
In the above query there is no reference to WAIT_TIME as you are not interested in whether a session is currently waiting or not, just what buffers are causing waits.

If a particular block or range of blocks keep showing waits you can try to isolate the object using the queries in  Note:181306.1.

One can also look at:

· Capturing session trace and noting the "buffer busy waits" may help - See  Note:62160.1.

Reducing Waits / Wait times:

As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in.

The following hints may be useful for particular types of contention - these are things that MAY reduce contention for particular situations:

Related:

Bug can cause "buffer busy waits" and latch contention in 817/901  Note:176129.1
Tracing User sessions  Note:62160.1 

歡迎關(guān)注我的微信公眾號"IT小Chen"，共同學(xué)習(xí)，共同成長！?。?/strong>