oracle enqueue lock
enqueue (enq:) waits
1,enqueue也是一種鎖，保證對於資料庫資源的訪問；
2,如發生enqueue wait,表明會話正在等待另一個會話持有的enqueue lock;
3,等待事件的構成：enq:enqueue型別-等待的資源
--前後端程式皆會持enqueue lock
SQL> select program from v$session where sid in (select distinct sid from v$enqueue_lock);
PROGRAM
----------------------------------------------------------------
ORACLE.EXE (CKPT)
plsqldev.exe
plsqldev.exe
ORACLE.EXE (SMON)
ORACLE.EXE (DBRM)
ORACLE.EXE (DBW0)
ORACLE.EXE (LGWR)
ORACLE.EXE (MMON)
8 rows selected
--與euqueue相關的等待事件
SQL> select * from v$event_name where name like '%enq%';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------
83 3141712284 enq: PW - flush prewarm buffers name|mode 0 0 4217450380 1 Application
103 3500532018 enq: RO - contention name|mode 2 0 4217450380 1 Application
104 143262751 enq: RO - fast object reuse name|mode 2 0 4217450380 1 Application
105 4205197519 enq: KO - fast object checkpoint name|mode 2 0 4217450380 1 Application
149 1567037747 enq: MV - datafile move name|mode type file # 4166625743 3 Administrative
231 668627480 enq: TM - contention name|mode object # table/partition 4217450380 1 Application
232 1649608974 enq: ST - contention name|mode 0 0 3290255840 2 Configuration
237 310662678 enq: TX - row lock contention name|mode usn<<16 | slot sequence 4217450380 1 Application
238 281768874 enq: TX - allocate ITL entry name|mode usn<<16 | slot sequence 3290255840 2 Configuration
239 1035026728 enq: TX - index contention name|mode usn<<16 | slot sequence 3875070507 4 Concurrency
240 1435178951 enq: TW - contention name|mode 0 operation 4166625743 3 Administrative
250 1645217925 enq: HW - contention name|mode table space # block 3290255840 2 Configuration
251 3890744969 enq: SS - contention name|mode tablespace # dba 3290255840 2 Configuration
256 2322460838 enq: SQ - contention name|mode object # 0 3290255840 2 Configuration
268 122034066 enq: WG - lock fso name|mode kdlw lobid first half kdlw lobid sec half 3875070507 4 Concurrency
278 3304404527 enq: BB - 2PC across RAC instances name|mode gtrid hash value bqual hash value 3386400367 5 Commit
309 3835660459 enq: DB - contention name|mode EnqMode 0 4166625743 3 Administrative
324 2649722911 enq: RC - Result Cache: Contention name|mode chunkNo blockNo 4217450380 1 Application
325 1636695715 enq: JX - SQL statement queue name|mode sqlid execid 2396326234 10 Scheduler
326 3822543692 enq: JX - cleanup of queue name|mode sqlid execid 2396326234 10 Scheduler
--enqueue lock的相關資訊
L> select * from dba_lock;
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------- -------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------ ----------------------------------------
3 XR Null None 4 0 3398 Not Blocking
3 RD Null None 1 0 3398 Not Blocking
3 Control File Row-S (SS) None 0 0 3393 Not Blocking
3 RS Row-S (SS) None 25 1 3385 Not Blocking
9 AE Share None 100 0 2250 Not Blocking
133 Redo Thread Exclusive None 1 0 3386 Not Blocking
46 Temp Segment Row-X (SX) None 12 1 3362 Not Blocking
7 AE Share None 100 0 2265 Not Blocking
134 AE Share None 100 0 3354 Not Blocking
89 Media Recovery
---oracle enqueue型別-----
The Oracle enqueues are:
BL, Buffer Cache Management
BR, Backup/Restore
CF, Controlfile Transaction
CI, Cross-instance Call Invocation
CU, Bind Enqueue
DF, Datafile
DL, Direct Loader Index Creation
DM, Database Mount
DR, Distributed Recovery Process
DW, SecureFiles
DX, Distributed Transaction
FP, File Object
FS, File Set
HW, High-Water Lock
IN, Instance Number
IR, Instance Recovery
IS, Instance State
IV, Library Cache Invalidation
JI, Enqueue used during AJV snapshot refresh
JQ, Job Queue
KK, Redo Log "Kick"
KP, contention in Oracle Data Pump startup and shutdown processes
KO, Multiple Object Checkpoint
L[A-P], Library Cache Lock
LS, Log Start or Switch
MM, Mount Definition
MR, Media Recovery
N[A-Z], Library Cache Pin
PE, ALTER SYSTEM SET PARAMETER = VALUE
PF, Password File
PI, Parallel Slaves
PR, Process Startup
PS, Parallel Slave Synchronization
Q[A-Z], Row Cache
RO, Object Reuse
RT, Redo Thread
RW, Row Wait
SC, System Commit Number
SM, SMON
SN, Sequence Number
SQ, Sequence Number Enqueue
SR, Synchronized Replication
SS, Sort Segment
ST, Space Management Transaction
SV, Sequence Number Value
TA, Transaction Recovery
TC, Thread Checkpoint
TE, Extend Table
TM, DML Enqueue
TO, Temporary Table Object Enqueue
TS, Temporary Segment (also TableSpace)
TT, Temporary Table
TX, Transaction
UL, User-defined Locks
UN, User Name
US, Undo Segment, Serialization
WL, Being Written Redo Log
XA, Instance Attribute Lock
XI, Instance Registration Lock
--enqueue lock的細節資訊
SQL> select * from dba_lock_internal;
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
---------- -------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------
3 XR Null None 4 0
3 RD Null None 1 0
3 Control File Row-S (SS) None 0 0
3 RS Row-S (SS) None 25 1
9 AE Share None 100 0
133 Redo
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756375/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- enable table lock 的enqueue等待ENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- sequence可能引發的各種lock/enqueueENQ
- oracle enqueue(zt)OracleENQ
- oracle enqueue typeOracleENQ
- 故障排除："WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析AIENQ
- Oracle Enqueue WaitsOracleENQAI
- mutex,latch,lock,enqueue hash chains latch基礎概念MutexENQAI
- 一次WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCKAIENQ
- 【原創】說說你是如何理解Oracle資料庫中latch和enqueue lock的?Oracle資料庫ENQ
- Metlink:Troubleshooting:WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 與 dc_tablespcesAIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK-[ID 278316.1]AIENQ
- Oracle enqueue wait tipsOracleENQAI
- ORACLE 中的Enqueue 種類OracleENQ
- 翻譯metalink關於WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章AIENQ
- oracle lock鎖_v$lock_轉Oracle
- [Oracle Script] LockOracle
- About Oracle LockOracle
- Oracle Latch & LockOracle
- Oracle 11g Enqueue Waits DescOracleENQAI
- ORACLE LOCK,LATCH,PINOracle
- ORACLE LOCK MODE 1.2.3.4.5.6Oracle
- Oracle Lock Information QueriesOracleORM
- Dead lock - oracleOracle
- ORACLE lock 轉貼Oracle
- ORACLE查LOCK表Oracle
- oracle lock系列一Oracle
- Oracle 9i RAC enqueue等待測試OracleENQ
- TC ENQUEUEENQ
- oracle v$lock詳解Oracle
- [Oracle Script] check lock infoOracle
- Oracle 之pin和lockOracle
- Oracle Lock Management Services (365)Oracle
- 【ASK_ORACLE】Row Cache Enqueue鎖之概念篇OracleENQ
- Oracle Global Enqueue Services Deadlock detected錯誤詳解OracleENQ
- oracle dead lock與效能Oracle