效能調整一則:buffer busy waits導致主要issue

xueji03發表於2008-07-25
這是一套生產環境的3節點RAC環境,RHEL3+9208,突出等待事件為buffer busy wait及cluster wait。[@more@]

top,vmstat,free過後發現系統較為空閒狀態,抓statspack:

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,312M Std Block Size: 16K
Shared Pool Size: 448M Log Buffer: 1,024K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 103,989.15 5,199.31
Logical reads: 4,307.61 215.37
Block changes: 670.76 33.54
Physical reads: 6.72 0.34
Physical writes: 14.18 0.71
User calls: 329.68 16.48
Parses: 128.77 6.44
Hard parses: 8.52 0.43
Sorts: 18.64 0.93
Logons: 0.03 0.00
Executes: 320.35 16.02
Transactions: 20.00

% Blocks changed per Read: 15.57 Recursive Call %: 55.47
Rollback per transaction %: 2.37 Rows per Sort: 21.30

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.88 Redo NoWait %: 99.99
Buffer Hit %: 99.84 In-memory Sort %: 100.00
Library Hit %: 97.90 Soft Parse %: 93.38
Execute to Parse %: 59.80 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 98.16 % Non-Parse CPU: 89.86

因應用改動起來太大一直沒有使用繫結變數,軟解析不高,目前強制cursor_sharing為force,shared_pool_size有些偏大,線上將其改小這種做法當然不可取,db_cache_size在後面的advice看起來處於合適大小狀態。

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
global cache cr request 147,324 698 19.66
buffer busy global CR 7,435 682 19.22
global cache null to x 85,665 613 17.27
CPU time 574 16.17
buffer busy global cache 8,012 221 6.22

從owi方面看起來是符合之前講的主要issue為buffer busy waits及RAC相關的wait的。

分析:

目前MLB21上的主要等待事件為buffer busy wait,等待類別為data block,實際上因data block而觸發的buffer busy wait等待事件下面兩類: 1、一種是高併發會話在對相同的物件執行DML,同時db_block_size尺寸較大(因同一個塊包含更多的行)

從抓取到的二條TOP合此型別(UPDATE&INSERT),較為合適的方法就是增大PCTFREE。

2、另外就是多個session併發請求相同的資料塊,但因該資料塊不在buffer_cache中而必須從磁碟讀取,處理這種情況,oracle會只讓其中一個sesion進行磁碟讀取,此時其它session等待塊從磁碟上讀取進buffer_cache而丟擲buffer busy wait等待事件。

這種情況也存在於當前環境中,從抓取到的第3條語句,表現為執行較為頻繁且伴隨有db file sequential read等待事件,可以先試著先最佳化TOP N邏輯讀的SQL。

再比對一下,發現導致buffer busy wait的SQL語句與導致cluster wait的SQL語句一模一樣。

於是問題清晰了,當前DB環境的block_size為16k,RAC環境下的OLTP再加上那幾個超頻繁被執行update、insert的大表操作下,顯得有些慘不忍睹,當前那幾個表的pctfree為10,準備加大到30左右,index的pctfree低於50會導致索引走範圍掃描,且快速全域性掃描會較慢。
上面提到的第2種buffer busy waits會造成少量的db file scattered read(即oracle允許其中一個session去磁碟讀取資料塊,當然,這個動作也有可能是db file sequential read)和大量的db file sequential read(即其他session會對讀進來後的資料塊進行邏輯讀取),需要去調優statspack中的TOP N的邏輯讀。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14517718/viewspace-1007818/,如需轉載,請註明出處,否則將追究法律責任。

相關文章