讀書筆記-高階owi與oracle效能調整-cache buffer

latch:cache buffers chains
發生cache buffers chains鎖存器爭用的代表性情況是:低效的sql和hot block.
低效的sql:
每個邏輯讀取需要一個latch get操作和一個cpu.從latch get例程中獲得的唯一方法是獲得鎖存器.必須確定爭用cache buffers chains鎖存器的sql語句,並且調整它們以減少邏輯讀取的數量.每次executions都帶有高buffer_gets的sql語句的主要原因.
適當地使用parallelquery也能成為減少cache buffers chains鎖存器爭用的方法.parallel query不經過sga,而是直接讀取資料檔案
上所需的資料,因此高速緩衝區的掃用問題本身不存在.但是parallel query是為了處理大量資料使用的,所以不推薦為鎖存器爭用的一般解決方案.
hot block:
(1)透過賦予較高的pctfree值或使用小塊,減少塊爭用.
(2)使用partition方法儘量將行物理地插入到另外的塊.
(3)只對有問題的塊行刪除後再執行插入工作,此方法只對表可行.
解決表的cache buffers chains鎖存器爭用相對比較容易,因為分散行的方法非常多.但索引上的爭用問題相當複雜.

latch:cache buffers lru chain
oracle在如下情況下必須獲得cache buffers lru chain鎖存器.
(1)程式欲讀取還沒有裝載到記憶體上的塊時,透過查詢lru列分配到所需空閒緩衝區,在此過程中需要cache buffers lru chain鎖存器.
(2)dbwr為了將髒緩衝區記錄到檔案上,查詢lruw列,將相應緩衝區移動到lru列的過程中也要獲得cache buffers lru chain鎖存器.以下情況將髒緩衝區記錄到檔案裡.oracle 程式為了獲得空閒緩衝區,向dbwr請求記錄髒緩衝區時;oracle程式為執行parallel query或tablespace backup,truncate/drop等工作,請求記錄相關物件的髒緩衝區時;由於週期性或管理上的原因檢查點(checkpointing)被執行 時.
cache buffers lru chain鎖存器爭用的最重要的原因是過多請求空閒緩衝區.低效的sql語句是過多請求空閒緩衝區的最典型情況,若多個會話同時執行低效的sql語句,則 在查詢空閒緩衝區過程中和記錄髒緩衝區的過程中,為了獲取buffers lru chain鎖存器發生爭用.調整語句,減少邏輯讀和物理讀.
cache buffers chains和cache buffers lru chain的差別是:若是多個會話同時掃描同一個表或索引時,則發生cache buffers chains鎖存器爭用的機率高,因為對相同chain發生了爭用.但是多個會話同時掃描不同表或索引時,發生cache buffers lru chains鎖存器爭用的機率高.cache buffers lru chain爭用的另一個重要特點就是伴隨著物理i/o.
高速緩衝區過小或檢查點週期過短時,也會增加cache buffers lru chain爭用.

buffer busy waits/ready by other session
發生物理i/o後將新塊載入到sga需要建立新的緩衝區,因此最實建立緩衝區的程式以exclusive模式獲得buffer lock.
減少select/select引起的read by other session等待的方法,整理結果如下:
應該透過對sql進行最佳化,以便能以最少的i/o獲得所需的結果.
若sga大小(或高速緩衝區大小)比系統全域性的i/o小,就不能只透過sql調優解決問題,還需要增加sga物理大小.
select/update引起的buffer lock爭用會在如下情況下發生:
特定程式修改特定表,資料的過去映象記錄在撤銷塊.
很多程式試圖同時(或之後)讀取已修改的資料.
撤銷塊爭用的原因在於oracle的最基本的機制中的一致性讀取.select會話讀取資料塊時,由於執行了update成為已修改狀態,
基本上利用撤銷塊讀取過去狀態的資料.這就是一致性讀取,在此過程中建立cr塊.這時,許多會話在同一時刻試圖讀取撤銷塊,撤銷塊發生與 select/select情況相同狀況的buffer lock爭用,因此發生對read by other session事件的等待.
insert/insert引起的buffer lock爭用,大部份是錯誤的空閒列的值引發的.
減少因insert/insert引起的buffer busy wait等待的方法:
如果在不能使用assm環境下,考慮系統的負荷適當賦予freelists,freelist groups值,與空閒列相關的屬性使用預設值將相當危險.
9i以上版本起儘量使用assm,使用assm時,在任何環境下都能避免非常嚴重的效能下降.
update/update引發buffer lock爭用時有多種多樣的解決方法,發生buffer lock爭用的根本原因就是不同的行位於同一個塊,因此將不同行分散到不同塊,這就是最普遍使用的解決方法.(1)取較高的pctfree值(2)利用 partitioning方法將各行物理分配到其它的塊(3)使用較小的塊.
減少buffer lock爭用的方法:
(1)減少select/select引發的read by other session等待的最好方法是透過sql的最最佳化利用最少的i/o獲得需要的結果.如果透過這個操作也不能解決問題,就應該檢查sga大小適當與否.
(2)select/update引發的read by other session等待與select/select引起的read by other session等待的解決方法相同.
(3)insert/insert引發的buffer busy waits等待,透過使用適當的段空間管理方法得以解決.oracle 9i以上版本推薦使用assm.
oracle 8i則要合理設定freelists值.與事務量相比freelists值過小時,buffer lock引起的爭用廣泛出現.光靠freelists值不能解決問題時,調高_bump_highwater_mark_count隱含數值也是有幫助的.
(4)update/update引發的buffer busy waits等待,可以透過採用避免對相同塊同時執行update的方法得以解決.建立把update形式考慮周全的最優的partitioning,將成 為最好的解決方法.透過取高pctfree或使用較小的塊可以將塊分散,因此能減少buffer lock爭用.但必須透過測試檢驗是否有負面影響.

write complete waits
write complete waits等待與buffer busy waits等待相同,可以透過buffer lock爭用引起的等待進行分類.dbwr將髒緩衝區記錄到磁碟上的期間,對緩衝區以exclusive模式佔有buffer lock.這時,讀取或修改緩衝區的其它程式需要等待此項工作結束,這時等待write
complete waits 事件.
write complete waits等待普遍出現時,與其說是應用程式的問題,不如說是dbwr效能問題的可能性非常高.實際上,伺服器程式讀取正在寫入到磁碟的緩衝區的機率不高,媽便如此還存在等待,這說明dbwr將髒緩衝區的資料寫入磁碟的時間過長.
dbwr效能緩慢的理由:
(1)i/o系統的效能緩慢時.如果dbwr上顯示的db file parallel write等待時間較長,就可以判斷存在i/o系統問題.在dbwr上,db file parallel write等待時間變長,則伺服器程式連續經歷free buffer waits等待或write complete waits等待.大家都知道,組合使用祼裝置和非同步i/o是改善i/o效能的最好方法.os層面上使用direct i/o也有助於效能.使用direct i/o時,cpu數量若充分多,應該調整db_writer_processes值,可以增加dbwr的數量.oracle的基本dbwr數量等於 cpu_count/8.
(2)dbwr的工作量過多時.頻繁發生檢查點時,dbwr的活動量過多,因此dbwr的效能可能下降.dbwr的效能下降,導致系統全域性效能下降,取過 小的fast_start_mttr_target值時,頻繁發生增量檢查點.重做日誌檔案過小時,發生頻繁的日誌檔案切換,因此檢查點工作會增 加.parallel query引發direct path read時,還有truncdate,drop,host backup時也會發生檢查點.雖然i/o系統不存在效能問題,但還是出現write complete waits等待,就應該檢視是否存在給dbwr帶來不必要的負荷的因素.
(3)作為間接改善dbwr效能的方法,也被推薦適當使用多重緩衝池.透過提高高速緩衝區的效率,以此降低記憶體上的資料寫入到磁碟上的頻度,進而降低write complete waits等待發生的機率.

free buffer waits
發生free buffer waits等待的理由:
(1)低效的sql
(2)過小的高速緩衝區
(3)dbwr的效能下降

enq:tc-contention
tc:thread checkpoint lock或tablespace checkpoint lock
enq:tc-contention等待即使在沒有多個程式引起爭用的情況下,也可以發生.只有在進行由伺服器程式引發的檢查點工作同步過程中發生.
enq:tc-contention等待發生的代表案例:parallel query,tablespace host backup.
paralle query發生檢查點的原因是slave session引起的direct path read.
以下三種情況下使用direct path read(或physical read direct)方式的讀取
(1)記憶體區域上不能完成排序工作時,在臨時段的區域裡儲存和讀取的過程中,發生direct path write,direct path read.這時的等待事件可透過direct path read temp,direct path write temp觀察.
(2)slave session為了掃描直接讀取的資料檔案時,使用direct path read.這時等待事件透過direct path read事件觀察.
(3)若判斷是因i/o系統的效能下降,導致不能將塊以足夠塊的速度讀取,oracle為了臨時方便會使用direct path read.
執行alter tablespace ...begin backup後,將屬於此表空間的所有高速緩衝區的髒塊記錄到磁碟上,這個過程經歷enq:tc-contention等待.

enq:ci-contention,enq:ro-contention
ci:cross instance
oracle對特定表執行truncate之前,應對位於高速緩衝區上的表資料的髒緩衝區執行檢查點工作.因為即使在執行truncate過程中發生故 障,也需要能恢復資料.掃行truncate的伺服器程式,需要等到truncate工作結束,通常是透過enq:ci-contention等待現象觀 察的.
解決方法:
(1)升級為oracle 10gr2.10gr2上大幅改善了物件檢查點演算法,因此它引起的效能問題大大減少.
(2)改善dbwr的效能.
(3)確認sga是否過大.為執行檢查點檢索髒緩衝區,高速緩衝區越大,需要管理的髒緩衝區量也越多.
truncate或drop等刪除物件使用空間的工作,一直等到因ckpt和dbwr徹底刪除,在這期間等待enq:ro-fast object reuse事件.