Oracle Enqueues Wait Events 三
什麼是TX鎖,我們這裡借鑑MOS上的概述簡單描述以下,後面我們根據具體的場景具體分析。
當一個事務開始對事務內的一個物件進行改變時,會獲取一個TX鎖,並一直持有到事務執行提交或回滾為止。它主要作用是一種排隊機制,以便其他會話可以等待事務完成。TX鎖的鎖名(ID1和ID2)反映活動事務的事務ID。
(A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.)
注意:TX鎖是一個應用程式編碼、設計和使用的問題,只能透過更頻繁、更明確的提交語句和任何其他較小的程式碼更改來修改應用程式程式碼。Oracle支援除了幫助識別引起等待的物件和命令外,無法徹底解決TX鎖等待問題。還是需要協助開發人員一起修復程式碼,以此來緩解TX鎖等待。
(NOTE: TX lock is an application coding, design and usage problem and can ONLY be fixed by changing application code with more frequent and explicit COMMIT statements and any other minor code changes. Oracle Support cannot fix TX lock wait issues other than helping to identify the objects and commands causing the waits. Please work with Developers to fix the code and to alleviate TX lock waits.)
我們接下來主要描述幾種TX鎖,希望對大家的後期學習和解決問題有用。
'enq: TX - row lock contention'
'enq: TX - index contention'
'enq: TX - allocate ITL entry'
'enq: TX - contention'
下面是我們確認TX等待索引的語句
如果遇到與鎖相關的掛起場景,可以使用以下SQL語句來幫助隔離等待器和攔截器:
顯示所有等待鎖的會話:
顯示列的意思:event:會話正在等待的資源或事件,
P1—與等待相關的資料檔案的全部檔案數量。
P2—P1中的資料檔案的塊數量。
P3—描述等待產生原因的程式碼。
SELECT event, p1, p2, p3
FROM v$session_wait
WHERE wait_time= 0
AND event like 'enq%';
在10g開始,存在一個不同的更具描述性的事件名稱,用於更頻繁的佇列,您可以查詢TX等待事件如下:
P1RAW、P2RAW和P3RAW列顯示的值與P1、P2和P3列相同,只是數字是以十六進位制顯示的。
SELECT sid, p1,p1raw, p2,p2raw, p3,p3raw
SELECT sid, p1raw, p2, p3
FROM v$session_wait
WHERE wait_time = 0
AND event like 'enq: TX%';
顯示當前等待TX鎖的會話:
SELECT * FROM v$lock WHERE type='TX' AND request>0;
顯示當前持有TX鎖的會話:
SELECT * FROM v$lock WHERE type='TX' AND lmode > 0;
顯示哪些段經歷了最多的行鎖等待:
SELECT owner, object_name, subobject_name, value
FROM v$segment_statistics
WHERE statistic_name='row lock waits'
AND value > 0
ORDER BY 4 DESC;
V$SESSION_WAIT檢視各列解釋引用地址
https://blog.csdn.net/licheng6302/article/details/7329675
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26342786/viewspace-2709282/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Enqueues Wait Events 二OracleENQAI
- Oracle Enqueues Wait Events 一OracleENQAI
- Oracle Enqueues Wait Events 三 enq: TX - row lock contentionOracleENQAI
- Oracle RAC Wait EventsOracleAI
- 幾個ORACLE wait eventsOracleAI
- wait eventsAI
- 【Oracle】Master Note for Parallel Execution Wait EventsOracleASTParallelAI
- oracle中 Wait Events 和可能原因(oracle doc)OracleAI
- ORACLE等待事件型別【Classes of Wait Events】Oracle事件型別AI
- Data Guard Wait EventsAI
- Parallel Query Wait EventsParallelAI
- Oracle RAC Cache Fusion系列十八:Oracle RAC Statisticsand Wait EventsOracleAI
- 【DATAGUARD】Data Guard Wait EventsAI
- oracle11g_Descriptions of Wait Events_等待事件全列表OracleAI事件
- High "Resmgr:Cpu Quantum" Wait EventsAI
- 轉:latch wait events 說明分析AI
- zt_關於wait events asynch descriptor resize_wait eventAI
- Buffer Cache I/O-Related Wait EventsAI
- oracle enqueues 很全面的解釋OracleENQ
- oracle eventsOracle
- wait event_Additional Statistics_that do not have corresponding wait eventsAI
- ORACLE EVENTS(轉)Oracle
- oracle set eventsOracle
- Wait Events的分類及分類依據AI
- 0317Library Cache Pin/Lock Wait EventsAI
- Master Note Parallel Execution Wait Events [ID 1097154.1]ASTParallelAI
- Redo Logging I/O-Related Wait EventsAI
- dump oracle events(轉)Oracle
- Oracle RAC Cache Fusion 系列十:Oracle RAC Enqueues And Lock Part 1OracleENQ
- oracle wait!OracleAI
- oracle troubleshooting waits for locks/Enqueues other than 'TM','TX' and 'UL'OracleAIENQ
- Oracle跟蹤事件 -- set eventsOracle事件
- Deal With "SGA: allocation forcing component growth" Wait Events_1270867.1AI
- Statspack報告分析—第6部分: Wait Events - Complete listAI
- Oracle所有診斷事件列表eventsOracle事件
- alter session set events /Oracle跟蹤SessionOracle
- [zt]Oracle跟蹤事件 - set eventsOracle事件
- Oracle跟蹤事件:set events 整理Oracle事件