Oracle資料庫buffer busy wait等待事件 （2）

--查詢等待塊型別

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 b.P2 BETWEEN a.Header_Block + 1 AND (a.Header_Block + a.Freelist_Groups)

AND a.Header_File = b.P1

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 b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1

AND a.File_Id = b.P1

AND b.Event = 'buffer busy waits'

AND NOT EXISTS (SELECT 1

FROM Dba_Segments

WHERE Header_File = b.P1

AND Header_Block = b.P2);

◆ 帶有原因碼130的資料塊(類#1)爭用

如果buffer busy waits的等待事件主要集中在資料塊(類#1)上，並且原因碼130，則

表明應用程式執行在同一時刻查詢相同資料集的多個會話，採用如下三件事最小化問題：

§減少併發級別或該表在執行現成直接內分割槽工作的方法。

§優化SQL語句，減少物理讀取和邏輯讀取的數量。

§增加freeLists和freeList Groups的數量。

◆ 帶有原因碼220的資料塊(類#1)爭用

多個會話同時在相同的物件上DML。採用如下三件事最小化問題：

減少併發級別或改變劃分部分的方法。

減少塊中行的數量。

在另一個具有較小塊尺寸的表空間中重新構建物件(Oracle 9i或以上版本)。

可以使用較大的PCTFREE重新構建表或索引。可以使用命令改變表以最小化每個塊的最

小行數：

ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;

從Oracle 9i開始，可以在另外一個具有較小的塊尺寸的表空間中移動或重新構建物件。

雖然這些動作可以最小化buffer busy waits問題，但是他們無疑將增加全表掃描時間

和磁碟空間利用率。常言道，世上沒有免費的午餐。

◆ 資料段頭(類#4)的爭用

如果buffer busy waits的等待時間主要集中在資料段頭(即表或索引段頭，並且不是

插銷段頭)上，這意味著資料庫中的一些表或索引具有高段頭活動。如下解決問題：

增加已經確定物件的程式FreeLists和FreeList Groups的數量。

確保PctFree和PctUsed之間內的間隙不會太小。

確保下一個區尺寸不會太小。

如果不希望混浠FreeLists和FreeList Groups，可以依靠自動段空間管理(Automatic

Segment Space Management,ASSM)特性，以分散從插入語句中引入的資料(9i特性)。

◆ 撤消段都(類#17)的爭用

如果buffer busy waits等待時間主要集中在撤消段頭上，這表明資料庫中的回滾段

過少，或者他們的尺寸太小，從而造成對段頭的頻繁更新。如果在Oracle 9i中引入的系

統管理撤消，就不需要處理這種問題，因為Oracle將根據需要增加額外的撤消段。

◆ 撤消塊的爭用(類#18)

如果buffer busy waits等待時間主要集中在撤消塊上，這通常意味著多個併發會話同

時查詢更新的資料。當應用程式可以在不同的時間內查詢和DML時，這種問題就不會存在。

◆ 系統級診斷

--檔案等待次數

SELECT b.File_Id,

b.File_Name,

a.COUNT

FROM X$kcbfwait a,

Dba_Data_Files b

WHERE a.Indx = b.File_Id-1

AND a.COUNT > 0

ORDER BY a.COUNT;