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

enq:tm-contention
執行dml期間,為防止對與dml相關的物件進行修改,執行dml的程式必須對該表獲得tm鎖.若在獲取tm鎖的過程中發生爭用,則等待
enq:tm-contenttion事件.
一般發生tm鎖爭用的情況如下:
(1)修改無索引外來鍵的父鍵時
(2)dml和ddl之間的tm鎖爭用
(3)lock table...引起的tm鎖爭用
(4)direct load工作引起的tm鎖爭用
解決辦法:
(1)從oracle 9i開始,沒有索引的外來鍵列,演算法大幅得到改善,不再發生類似的爭用現象.
(2)若對於資料多的表執行不當的ddl,則訪問此表的所有dml會話都會陷入等待狀態,可能發展至故障狀態.透過合理的管理從根本上防止才是最好的方 法.執行ddl時,最好使用online選項.隨著oracle版本的升級,online狀態下可執行的ddl逐步增加.使用parallel ddl將ddl的執行速度最大化.對擁有大量資料的表執行ddl時,若恰當使用parallel選項,可將ddl本身效能最大化,而且同時使用 nologging選項也比較好.如果提升ddl執行速度,tx鎖爭用引起的等待時間相應地也會下降.
(3)發生tm鎖引起的爭用時,收集鎖擁有者在會話上執行的sql語句尤為重要.與其為了同步對整個表上鎖,不如考慮使用dmbs_lock程式包或使用select...for update等,減少鎖範圍的方法.
(4)direct load工作不經過sga,而是直接寫入到資料檔案裡,所以在執行工作期間不允許對錶進行任何修改.以exclusive模式獲得tm鎖,對於表不允許發生任何修改.

enq:tx-row lock contention
(1)多個會話修改相同行時,mode=6
修改相同行伴隨著的tx鎖爭用,完全是應用程式問題.長時間執行的update或delete命令最好是在事務較少的時段執行.還有,最佳化update或 delete命令本身也是改善效能的另一種方法.特別是對大量資料執行update,不公引起tx鎖爭用問題,而且會引起許多效能
問題.
(2)多個會話引起唯一健衝突時,mode=4
唯一鍵衝突引起的tx鎖爭用完全是應用程式的問題.為建立唯一鍵,使用特別演算法或從已存表裡篩選最大值等方法時,唯一鍵衝突引起的tx鎖爭用隨時可能發生.最好的解決方法就是使用sequence建立唯一鍵.
(3)多個會話引起點陣圖索引衝突時,mode=4
一個葉節點管理大範圍的rowid.每當表的行被修改時,對點陣圖索引相應的值,每次都要重新計算行所屬葉節點的點陣圖.因此,兩個會話同時對相同的葉節點執 行點陣圖運算時,為保障順序,應該獲取tx鎖.對dml頻繁的表取消其點陣圖索引,改用基於decode的索引或普通索引,也可以使用 materializedview之類的功能.

enq:tx-allocate itl entry
itl條目不足,mode=4
所有事務在修改塊之前,必須在塊頭的itl上登記條目.若itl超過maxtrans指定的最大值或塊內的剩餘空間不足,而不能登記條目時,為了以 shared模式獲得,早已在itl上登記條目的其它程式以exclusive模式獲得的tx鎖而等待.這時的等待現象可能過enq:tx- allocate itl entry事件觀察.
影響itl的三個屬性:initrans,maxtrans,pctfree.
一般情況下,itl條目不足伴隨著的tx鎖爭用現象不會發生,這是因為數十或數百個會話不大可能同時對一個塊的不同行進行修改.但若是發生了行連結或行遷移的行,則更新一個行時,對多個塊都要分配itl條目,因此發生itl條目不足引起的tx鎖爭用的機率會升高.
建立表時設定較高的initrans值,是解決itl空間不足伴隨著的tx鎖爭用現象的一般方法.更為重要的是,判斷是否是錯誤的應用程式設計引發的爭 用,若的確是錯誤的設計引發的,則修改應用程式是唯一的解決方法.利用v$segment_statistics檢視,可以得知對哪些段較多發生itl不 是引起的爭用.利用statistic_name='ITL waits'這個條件,可以瞭解對哪些段較多發生itl不足引起的爭用.

enq:tx-index contention
索引葉節點上發生分割時,mode=4
b*tree索引在新增資料的過程中,如果葉節點已滿就會進行分割,以此達到平衡.會話A在exclusive模式已獲tx鎖的情況下,執行分割的過程 中,會話B正要修改葉節點時,會話B為了以shared模式獲得會話A擁有的tx鎖只好等待,在此期間會發生enq:tx- indexcontention等待事件.
一般情況下enq:tx-index contention等待不會發生,它主要是在多個會話對已有索引的表執行較多量的dml時發生.這個等待現象
雖然不經常發生,但建立的數量多,組成索引的列值大而指標葉節點的塊頻繁被分割時,成為效能下降的原因.特別是使用sequence等方式生成值的列在創 建索引時,一直出現只在最後的葉節點新增值的現象,所以可能經常發生索引分割.這是以排序形式保持葉節點的b*tree索引引起的,因此多個會話將大量數 據執行insert時,與buffer busy waits等待一起發生enq:tx-index contention等待.
減少索引分割引發的爭用的基本方法,就是阻止在相同的葉節點塊裡集中新增資料的現象.另一種解決方法是將索引的塊設定得較大.
使用較大的塊時,一個塊上的條目數量多,因此較少發生分割,但是若塊大小增加,就可能引發buffer lock爭用引起的buffer busy waits等待現象,所以要謹慎使用.
修改沒有建立索引的表過程中,有時也能發生enq:tx-index contention等待,表裡有lob列時,就會從內部建立對於lob資料的索引(稱為lob索引),因此多個會話同時修改lob資料時會發生索引爭用.

enq:tx-contention
分散式事務環境下,透過prepared transaction讀取已獲得鎖的行時,直到事務結束為止,為了以shared模式獲取tx鎖而需要等待.
將flm以段空間管理方法使用時,想要分配tfl(transaction free list)的程式無法分配到tfl時,為了以shared模式獲得已經佔有tfl的事務的tx鎖,而需要等待.
回滾段頭的事務表上想要分配到新的slot時,應該以exclusive模式獲得tx鎖.

enq:ul-contention
使用dbms_lock程式包,可以對任意假想的資源掛起鎖.如果是因為dml發生的鎖時,雖然必須需要物理資源,但使用dbms_lock程式包
時沒有這種限制.利用dbms_lock程式包獲取的鎖稱為ul(userdefined lock)鎖.為獲得ul鎖而等待的會話,將發生enq:ul-contention等待事件.
利用dbms_lock.request函式,可以將ul鎖以exclusive模式獲得,利用dbms_lock.release函式,可以釋放鎖.
ul鎖引起的爭用是在沒能有效使用dbms_lock程式包時發生的.ul鎖的釋放只能在擁有鎖的會話上實現.若特定會話因長時間擁有ul鎖,而引起併發 性問題,則除了強制結束會話之外沒有其它方法.使用dbms_lock.request函式時,儘量使用release_on_commit選項,以避免 多餘地擁有鎖.使用這個選項若發生提交或回滾,則自動釋放該事務所擁有的ul鎖

pl/sql lock timer
利用dbms_lock.sleep暫時中斷事務時,該程式則等待pl/sql lock timer事件.
此等待不會引起效能問題,如果等待時間比預期的長,就應該重新檢查應用程式的實現邏輯是否存有問題.