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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-756375/
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
