【等待事件】等待事件系列（5.1）--Enqueue(队列等待)

【等待事件】等待事件系列（5.1）--Enqueue(队列等待)

1 BLOG文档结构图

2 前言部分

2.1 导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① Enqueue队列等待

② Enq数据字典

③ enq: AE - lock

④ enq: MR锁

⑤ enq: DX - contention

⑥ enq: SQ - contention 序列等待

2.2 相关参考文章链接

【推荐】等待事件系列（1）--User I/O类型（下）	http://blog.itpub.net/26736162/viewspace-2124435/
【推荐】等待事件系列（1）--User I/O类型（上）	http://blog.itpub.net/26736162/viewspace-2124417/
2016-09-23	【等待事件】日志类等待事件（4.7）--LGWR wait for redo copy、switch logfile 等	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771555&idx=1&sn=3c488fabff8d508fbb942c5bd206c532&chksm=fe8bba1bc9fc330d9a422b7795fc5ed41d72d4fb0c12aff97b5be37e8334de4a2f3663841630&scene=21#wechat_redirect
2016-09-22	【等待事件】日志类等待事件（4.6）--log file single write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771552&idx=1&sn=bafa6049cf16da92f07b1d50da7f274e&chksm=fe8bba18c9fc330eb3e3b636a1021e57814ea6b4cadc4ac2e4e34ce88c5be6a636a6b724e8ec&scene=21#wechat_redirect
2016-09-21	【等待事件】日志类等待事件（4.5）--log file sequential read	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771549&idx=1&sn=08452a61dcebf9aca50b1483799510fe&chksm=fe8bba25c9fc3333bbd3298ac9a3ecf54e86e7c71e5429a3a034179585a412b77e32ae745c96&scene=21#wechat_redirect
2016-09-20	【等待事件】日志类等待事件（4.4）--log buffer space（日志缓冲空间）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771546&idx=1&sn=a3397cc535a453ed4a5e77c04a6fa767&chksm=fe8bba22c9fc33346ca821489faee74732d876201a6d082e789d1653316461f27dfc81fa4275&scene=21#wechat_redirect
2016-09-19	【等待事件】日志类等待事件（4.3）--log file parallel write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771527&idx=1&sn=a5564f7c056fa89f59257ca7e8aaa898&chksm=fe8bba3fc9fc33292b365784c327ec2d6a682fc92de5de08e6cc3c22690462c2d092aa4e9f4b&scene=21#wechat_redirect
2016-09-18	【等待事件】日志类等待事件（4.2）--log file sync（日志文件同步）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771524&idx=1&sn=70969b743781b035eb50e9c993192d99&chksm=fe8bba3cc9fc332a7d3a0fda5d589b8803379e6cb90d2a86397f31f003a621414849c77e0cfc&scene=21#wechat_redirect
2016-09-17	【等待事件】日志类等待事件（4.1）--log file switch（日志文件切换）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771523&idx=1&sn=99224ba8fc53353c5c6f03ac7babdb5e&chksm=fe8bba3bc9fc332d558890b7278c2b6f51e194b958bd11b7f6d33d443ec45245ec6800461a7e&scene=21#wechat_redirect
2016-09-07	【等待事件】System I/O类等待事件（3.4）--control file single write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771471&idx=1&sn=5922a52ac6294acf2802f44e2bb0d724&chksm=fe8bba77c9fc336151a61bdf876cb058df0d61d1404d8450cb7771330b6d44309d86dae4bb54&scene=21#wechat_redirect
2016-09-06	【等待事件】System I/O类等待事件（3.3）--control file sequential read	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771468&idx=1&sn=fc7d83d1a9b12911f3c93d3b5b444e9a&chksm=fe8bba74c9fc3362b58717fca9e95c68d45e701fa2f733a643ba01db7969cca668858272fbfc&scene=21#wechat_redirect
2016-09-04	【等待事件】System I/O类等待事件（3.2）--control file parallel write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771458&idx=1&sn=e949dfa5bff65ce4a596005955c5be5a&scene=21#wechat_redirect
2016-09-03	【等待事件】System I/O类等待事件（3.1）--db file parallel write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771454&idx=1&sn=e90248954475dfd2c78bdec592405735&scene=21#wechat_redirect
2016-09-01	【等待事件】User I/O类等待事件（2.10）--所有User I/O类等待事件总结	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771447&idx=1&sn=22ae192f0d8a161f65514339ad763985&scene=21#wechat_redirect
2016-08-31	【等待事件】User I/O类等待事件（2.9）--local write wait	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771443&idx=1&sn=02b4ad5ca03052013b69ae6bcb7e3487&scene=21#wechat_redirect
2016-08-30	【等待事件】User I/O类等待事件（2.8）--read by other session	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771439&idx=1&sn=b3c01eed444cd6e597a63a3ed0687768&scene=21#wechat_redirect
2016-08-29	【等待事件】User I/O类等待事件（2.7）--direct path read/write temp	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771429&idx=1&sn=50b5684e699165a34087db88e07edb34&scene=21#wechat_redirect
2016-08-27	【等待事件】User I/O类等待事件（2.6）--direct path write（直接路径写、DRW）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771420&idx=1&sn=458eb18dc26da94debcea62643d15181&scene=21#wechat_redirect
2016-08-26	【等待事件】User I/O类等待事件（2.5）--direct path read（直接路径读、DPR）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771416&idx=1&sn=b26c3135584c5b60ce14cc0749ac58a7&scene=21#wechat_redirect
2016-08-20	【等待事件】User I/O类等待事件（2.4）--db file single write	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771403&idx=1&sn=054dd852dac5ac8837fa251f0e84332e&scene=21#wechat_redirect
2016-08-16	【等待事件】User I/O类等待事件（2.3）--db file parallel read	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771387&idx=1&sn=0037fb89470d8e6dd5ff72714b18a3b7&scene=21#wechat_redirect
2016-08-15	【等待事件】User I/O类等待事件（2.2）--db file scattered read（数据文件离散读）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771379&idx=1&sn=5887eee02885000c1d293adfd04ee044&scene=21#wechat_redirect
2016-08-14	【等待事件】User I/O类等待事件（2.1）--db file sequential read（数据文件顺序读）	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771376&idx=1&sn=42de046e73190f4e265f81bbb6e3ae00&scene=21#wechat_redirect
2016-08-13	【等待事件】等待事件概述（1）--等待事件的源起和分类	http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771373&idx=1&sn=1e55af795aae5f641b2c3cc610814ead&scene=21#wechat_redirect

3 Enqueue(队列等待)

3.1 简介

Enqueue是一种保护共享资源的锁定机制,是协调访问数据库资源的内部锁。该锁定机制保护共享资源，以避免因并发操作而损坏数据，比如通过锁定保护一行记录，避免多个用户同时更新。Enqueue采用排队机制，即FIFO(先进先出)来控制资源的使用。

Enqueue是一组锁定事件的集合，如果数据库中这个等待事件比较显著，还需要进一步追踪是哪一个类别的锁定引发了数据库等待。

Enqueue这个词其实是LOCK的另一种描述语。当我们在AWR报告中发现长时间的Enqueue等待事件时，说明数据库中出现了阻塞和等待，可以关联AWR报告中的Enqueue Activity部分来确定是哪一种锁定出现了长时间等待。

所有以“enq：”打头的等待事件都表示这个会话正在等待另一个会话持有的内部锁释放，它的名称格式是enq:enqueue_type - related_details。数据库动态性能视图v$event_name提供所有以“enq：”开头的等待事件的列表。

SELECT * FROM V$EVENT_NAME WHERE NAME LIKE 'enq%';

SELECT D.PARAMETER1, COUNT(1)

FROM V$EVENT_NAME D

WHERE NAME LIKE 'enq%'

GROUP BY D.PARAMETER1;

可以看出11.2.0.4中大约有512种Enqueue等待事件。

这一类的等待事件P1参数一般有“name|mode”和“type|mode”2种形式，其中：

Name： enqueue 的名称和类型。

Mode： enqueue的模式。

可以使用如下SQL查看当前会话等待的enqueue名称和类型（当然，这里的视图不仅仅可以是v$session_wait，只要包含p1的值即可，比如v$session、DBA_HIST_ACTIVE_SESS_HISTORY等视图）：

SELECT CHR (TO_CHAR (BITAND (P1, -16777216)) / 16777215)

|| CHR (TO_CHAR (BITAND (P1, 16711680)) / 65535)

"LOCK",

TO_CHAR (BITAND (P1, 65535)) "MODE"

FROM V$SESSION_WAIT

WHERE EVENT = 'ENQUEUE'

Oracle 的enqueue 包含以下模式：

模式代码	解释
1	Null mode
2	Sub-Share
3	Sub-Exclusive
4	Share
5	Share/Sub-Exclusive
6	Exclusive

Oracle的enqueue有如下类型：

Enqueue 缩写	缩写解释
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
DX	Dirstributed 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”
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 bin
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 Segement(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 Log
XI	Instance Registration Lock

所有队列等待锁：

Enqueue Type	Description
enq: AD - allocate AU	Synchronizes accesses to a specific OSM disk AU
enq: AD - deallocate AU	Synchronizes accesses to a specific OSM disk AU
enq: AF - task serialization	This enqueue is used to serialize access to an advisor task
enq: AG - contention	Synchronizes generation use of a particular workspace
enq: AO - contention	Synchronizes access to objects and scalar variables
enq: AS - contention	Synchronizes new service activation
enq: AT - contention	Serializes 'alter tablespace' operations
enq: AW - AW$ table lock	Global access synchronization to the AW$ table
enq: AW - AW generation lock	In-use generation state for a particular workspace
enq: AW - user access for AW	Synchronizes user accesses to a particular workspace
enq: AW - AW state lock	Row lock synchronization for the AW$ table
enq: BR - file shrink	Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR - proxy-copy	Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: CF - contention	Synchronizes accesses to the controlfile
enq: CI - contention	Coordinates cross-instance function invocations
enq: CL - drop label	Synchronizes accesses to label cache when dropping a label
enq: CL - compare labels	Synchronizes accesses to label cache for label comparison
enq: CM - gate	Serialize access to instance enqueue
enq: CM - instance	Indicate OSM disk group is mounted
enq: CT - global space management	Lock held during change tracking space management operations that affect the entire change tracking file
enq: CT - state	Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
enq: CT - state change gate 2	Lock held while enabling or disabling change tracking in RAC
enq: CT - reading	Lock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT - CTWR process start/stop	Lock held to ensure that only one CTWR process is started in a single instance
enq: CT - state change gate 1	Lock held while enabling or disabling change tracking in RAC
enq: CT - change stream ownership	Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
enq: CT - local space management	Lock held during change tracking space management operations that affect just the data for one thread
enq: CU - contention	Recovers cursors in case of death while compiling
enq: DB - contention	Synchronizes modification of database wide supplemental logging attributes
enq: DD - contention	Synchronizes local accesses to ASM disk groups
enq: DF - contention	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG - contention	Synchronizes accesses to ASM disk groups
enq: DL - contention	Lock to prevent index DDL during direct load
enq: DM - contention	Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
enq: DN - contention	Serializes group number generations
enq: DP - contention	Synchronizes access to LDAP parameters
enq: DR - contention	Serializes the active distributed recovery operation
enq: DS - contention	Prevents a database suspend during LMON reconfiguration
enq: DT - contention	Serializes changing the default temporary table space and user creation
enq: DV - contention	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DX - contention	Serializes tightly coupled distributed transaction branches
enq: FA - access file	Synchronizes accesses to open ASM files
enq: FB - contention	Ensures that only one process can format data blocks in auto segment space managed tablespaces
enq: FC - open an ACD thread	LGWR opens an ACD thread
enq: FC - recover an ACD thread	SMON recovers an ACD thread
enq: FD - Marker generation	Synchronization
enq: FD - Flashback coordinator	Synchronization
enq: FD - Tablespace flashback on/off	Synchronization
enq: FD - Flashback on/off	Synchronization
enq: FG - serialize ACD relocate	Only 1 process in the cluster may do ACD relocation in a disk group
enq: FG - LGWR redo generation enq race	Resolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FG - FG redo generation enq race	Resolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FL - Flashback database log	Synchronization
enq: FL - Flashback db command	Enqueue used to synchronize Flashback Database and deletion of flashback logs.
enq: FM - contention	Synchronizes access to global file mapping state
enq: FR - contention	Begin recovery of disk group
enq: FS - contention	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
enq: FT - allow LGWR writes	Allow LGWR to generate redo in this thread
enq: FT - disable LGWR writes	Prevent LGWR from generating redo in this thread
enq: FU - contention	This enqueue is used to serialize the capture of the DB Feature, Usage and High Water Mark Statistics
enq: HD - contention	Serializes accesses to ASM SGA data structures
enq: HP - contention	Synchronizes accesses to queue pages
enq: HQ - contention	Synchronizes the creation of new queue IDs
enq: HV - contention	Lock used to broker the high water mark during parallel inserts
enq: HW - contention	Lock used to broker the high water mark during parallel inserts
enq: IA - contention
enq: ID - contention	Lock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL - contention	Synchronizes accesses to internal label data structures
enq: IM - contention for blr	Serializes block recovery for IMU txn
enq: IR - contention	Synchronizes instance recovery
enq: IR - contention2	Synchronizes parallel instance recovery and shutdown immediate
enq: IS - contention	Enqueue used to synchronize instance state changes
enq: IT - contention	Synchronizes accesses to a temp object's metadata
enq: JD - contention	Synchronizes dates between job queue coordinator and slave processes
enq: JI - contention	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ - contention	Lock to prevent multiple instances from running a single job
enq: JS - contention	Synchronizes accesses to the job cache
enq: JS - coord post lock	Lock for coordinator posting
enq: JS - global wdw lock	Lock acquired when doing wdw ddl
enq: JS - job chain evaluate lock	Lock when job chain evaluated for steps to create
enq: JS - q mem clnup lck	Lock obtained when cleaning up q memory
enq: JS - slave enq get lock2	Get run info locks before slv objget
enq: JS - slave enq get lock1	Slave locks exec pre to sess strt
enq: JS - running job cnt lock3	Lock to set running job count epost
enq: JS - running job cnt lock2	Lock to set running job count epre
enq: JS - running job cnt lock	Lock to get running job count
enq: JS - coord rcv lock	Lock when coord receives msg
enq: JS - queue lock	Lock on internal scheduler queue
enq: JS - job run lock - synchronize	Lock to prevent job from running elsewhere
enq: JS - job recov lock	Lock to recover jobs running on crashed RAC inst
enq: KK - context	Lock held by open redo thread, used by other instances to force a log switch
enq: KM - contention	Synchronizes various Resource Manager operations
enq: KP - contention	Synchronizes kupp process startup
enq: KT - contention	Synchronizes accesses to the current Resource Manager plan
enq: MD - contention	Lock held during materialized view log DDL statements
enq: MH - contention	Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq: ML - contention	Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN - contention	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MR - contention	Lock used to coordinate media recovery with other uses of datafiles
enq: MS - contention	Lock held during materialized view refresh to setup MV log
enq: MW - contention	This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
enq: OC - contention	Synchronizes write accesses to the outline cache
enq: OL - contention	Synchronizes accesses to a particular outline name
enq: OQ - xsoqhiAlloc	Synchronizes access to olapi history allocation
enq: OQ - xsoqhiClose	Synchronizes access to olapi history closing
enq: OQ - xsoqhistrecb	Synchronizes access to olapi history globals
enq: OQ - xsoqhiFlush	Synchronizes access to olapi history flushing
enq: OQ - xsoq*histrecb	Synchronizes access to olapi history parameter CB
enq: PD - contention	Prevents others from updating the same property
enq: PE - contention	Synchronizes system parameter updates
enq: PF - contention	Synchronizes accesses to the password file
enq: PG - contention	Synchronizes global system parameter updates
enq: PH - contention	Lock used for recovery when setting Proxy for AQ HTTP notifications
enq: PI - contention	Communicates remote Parallel Execution Server Process creation status
enq: PL - contention	Coordinates plug-in operation of transportable tablespaces
enq: PR - contention	Synchronizes process startup
enq: PS - contention	Parallel Execution Server Process reservation and synchronization
enq: PT - contention	Synchronizes access to ASM PST metadata
enq: PV - syncstart	Synchronizes slave start shutdown
enq: PV - syncshut	Synchronizes instance shutdown_slvstart
enq: PW - perwarm status in dbw0	DBWR 0 holds enqueue indicating prewarmed buffers present in cache
enq: PW - flush prewarm buffers	Direct Load needs to flush pre-warmed buffers if DBWR 0 holds enqueue
enq: RB - contention	Serializes OSM rollback recovery operations
enq: RF - synch: per-SGA Broker metadata	Ensures r/w atomicity of DG configuration metadata per unique SGA
enq: RF - synchronization: critical ai	Synchronizes critical apply instance among primary instances
enq: RF - new AI	Synchronizes selection of the new apply instance
enq: RF - synchronization: chief	Anoints 1 instance's DMON as chief to other instances' DMONs
enq: RF - synchronization: HC master	Anoints 1 instance's DMON as health check master
enq: RF - synchronization: aifo master	Synchronizes apply instance failure detection and fail over operation
enq: RF - atomicity	Ensures atomicity of log transport setup
enq: RN - contention	Coordinates nab computations of online logs during recovery
enq: RO - contention	Coordinates flushing of multiple objects
enq: RO - fast object reuse	Coordinates fast object reuse
enq: RP - contention	Enqueue held when resilvering is needed or when data block is repaired from mirror
enq: RS - file delete	Lock held to prevent file from accessing during space reclamation
enq: RS - persist alert level	Lock held to make alert level persistent
enq: RS - write alert level	Lock held to write alert level
enq: RS - read alert level	Lock held to read alert level
enq: RS - prevent aging list update	Lock held to prevent aging list update
enq: RS - record reuse	Lock held to prevent file from accessing while reusing circular record
enq: RS - prevent file delete	Lock held to prevent deleting file to reclaim space
enq: RT - contention	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
enq: SB - contention	Synchronizes Logical Standby metadata operations
enq: SF - contention	Lock used for recovery when setting Sender for AQ e-mail notifications
enq: SH - contention	Should seldom see this contention as this Enqueue is always acquired in no-wait mode
enq: SI - contention	Prevents multiple streams table instantiations
enq: SK - contention	Serialize shrink of a segment
enq: SQ - contention	Lock to ensure that only one process can replenish the sequence cache
enq: SR - contention	Coordinates replication / streams operations
enq: SS - contention	Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
enq: ST - contention	Synchronizes space management activities in dictionary-managed tablespaces
enq: SU - contention	Serializes access to SaveUndo Segment
enq: SW - contention	Coordinates the 'alter system suspend' operation
enq: TA - contention	Serializes operations on undo segments and undo tablespaces
enq: TB - SQL Tuning Base Cache Update	Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB - SQL Tuning Base Cache Load	Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TC - contention	Lock held to guarantee uniqueness of a tablespace checkpoint
enq: TC - contention2	Lock of setup of a unique tablespace checkpoint in null mode
enq: TD - KTF dump entries	KTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE - KTF broadcast	KTF broadcasting
enq: TF - contention	Serializes dropping of a temporary file
enq: TL - contention	Serializes threshold log table read and update
enq: TM - contention	Synchronizes accesses to an object
enq: TO - contention	Synchronizes DDL and DML operations on a temp object
enq: TQ - TM contention	TM access to the queue table
enq: TQ - DDL contention	TM access to the queue table
enq: TQ - INI contention	TM access to the queue table
enq: TS - contention	Serializes accesses to temp segments
enq: TT - contention	Serializes DDL operations on tablespaces
enq: TW - contention	Lock held by one instance to wait for transactions on all instances to finish
enq: TX - contention	Lock held by a transaction to allow other transactions to wait for it
enq: TX - row lock contention	Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq: TX - allocate ITL entry	Allocating an ITL entry in order to begin a transaction
enq: TX - index contention	Lock held on an index during a split to prevent other operations on it
enq: UL - contention	Lock used by user applications
enq: US - contention	Lock held to perform DDL on the undo segment
enq: WA - contention	Lock used for recovery when setting Watermark for memory usage in AQ notifications
enq: WF - contention	This enqueue is used to serialize the flushing of snapshots
enq: WL - contention	Coordinates access to redo log files and archive logs
enq: WP - contention	This enqueue handles concurrency between purging and baselines
enq: XH - contention	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XR - quiesce database	Lock held during database quiesce
enq: XR - database force logging	Lock held during database force logging mode
enq: XY - contention	Lock used for internal testing

3.1.1 Enq数据字典

受到排队锁影响的数据库资源，我们称之为"排队资源"。Oracle使用内部数组结构来处理排队资源，可以通过x$ksqrs(内核服务排队资源)或v$resource视图来查看。

SELECT S.ADDR, S.TYPE, S.ID1, S.ID2 FROM V$RESOURCE S;

SELECT * FROM x$ksqrs;

v$resource_limit视图可查看排队锁资源的总体使用情况。查询系统资源的使用情况：

SELECT S.RESOURCE_NAME,

S.CURRENT_UTILIZATION AS "当前使用数",

S.MAX_UTILIZATION AS "系统最大使用数",

S.INITIAL_ALLOCATION AS "系统初始化参数分配数",

S.LIMIT_VALUE

FROM V$RESOURCE_LIMIT S

WHERE S.RESOURCE_NAME IN ('enqueue_resources',

'enqueue_locks',

'dml_locks',

'processes',

'processes');

排队锁使用单独的数组而不是排队资源数组来管理排队锁，通过查询x$ksqeq(内核服务排队对象)或v$enqueue_lock视图来看到这种结构。

v$equeue_lock视图(除TX和TM锁)

SELECT S.ADDR,

S.KADDR,

S.SID,

S.TYPE,

S.ID1,

S.ID2,

S.LMODE,

S.REQUEST,

S.CTIME,

S.BLOCK

FROM V$ENQUEUE_LOCK S;

从equeue等待事件中，解码排队类型及模式：

SELECT s.sid,

s.event,

s.p1,

s.p1raw,

chr(bitand(s.p1, -16777216) / 16777215) ||

chr(bitand(s.p1, 16711680) / 65535) AS "TYPE",

MOD(s.p1, 16) AS "MODE"

FROM v$session_wait s

WHERE s.event = 'enqueue'；

V$ENQUEUE_STATISTICS用于显示队列锁的统计数据：

V$ENQUEUE_STATISTICS displays statistics on the number of enqueue (lock) requests for each type of lock. V$ENQUEUE_STATISTICS encompasses V$ENQUEUE_STATand gives more detailed information (several rows for same enqueues with different reasons).

Column	Datatype	Description
EQ_NAME	VARCHAR2(64)	Name of the enqueue request
EQ_TYPE	VARCHAR2(2)	Type of enqueue requested
REQ_REASON	VARCHAR2(64)	Reason for the enqueue request
TOTAL_REQ#	NUMBER	Total number of enqueue requests or enqueue conversions for this type of enqueue
TOTAL_WAIT#	NUMBER	Total number of times an enqueue request or conversion resulted in a wait
SUCC_REQ#	NUMBER	Number of times an enqueue request or conversion was granted
FAILED_REQ#	NUMBER	Number of times an enqueue request or conversion failed
CUM_WAIT_TIME	NUMBER	Total amount of time (in milliseconds) spent waiting for the enqueue or enqueue conversion
REQ_DESCRIPTION	VARCHAR2(4000)	Description of the enqueue request
EVENT#	NUMBER	Event number

其视图结构定义如下：

SELECT st.inst_id, eqt.NAME, st.ksqsttyp, st.ksqstrsn, st.ksqstreq,

st.ksqstwat, st.ksqstsgt, st.ksqstfgt, st.ksqstwtm, st.ksqstexpl,

st.ksqstevidx

FROM x$ksqst st, x$ksqeqtyp eqt

WHERE (st.inst_id = eqt.inst_id)

AND (st.ksqsttyp = eqt.resname)

AND (st.indx > 0);

这里包含了非常重要的一个信息，就是锁定及其描述：

SELECT d.EQ_NAME, d.EQ_TYPE,d.REQ_REASON, d.REQ_DESCRIPTION FROM V$ENQUEUE_STATISTICS d;

SELECT * FROM V$ENQUEUE_STATISTICS;

SELECT * FROM V$ENQUEUE_LOCK;

SELECT * FROM V$ENQUEUE_STAT;

3.2 enq: AE - lock

SELECT * FROM V$EVENT_NAME WHERE NAME LIKE 'enq: AE%';

从Oracle Database 11g开始，除了每个文件要获得MR锁之外，每个登录数据库的会话现在都会缺省获得一个AE锁：

SQL> set line 9999

SQL> select * from v$lock where type='AE' and rownum <5;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000774D8978 00000000774D89D0 132 AE 100 0 4 0 1308736 0

00000000774D9C08 00000000774D9C60 141 AE 100 0 4 0 179 0

00000000774DA308 00000000774DA360 152 AE 100 0 4 0 11 0

00000000774DA3E8 00000000774DA440 153 AE 100 0 4 0 150 0

3.3 enq: MR锁

SELECT * FROM V$EVENT_NAME WHERE NAME LIKE 'enq: MR%';

可能很多朋友都注意过，在V$LOCK视图中，最常见的其实是MR锁，也就是介质恢复锁（Media Recovery）：

SQL> col name format a100

SQL> select file#,name from v$datafile;

FILE# NAME

---------- ----------------------------------------------------------------------------------

1 /u02/app/oracle/oradata/oratest/system01.dbf

2 /u02/app/oracle/oradata/oratest/sysaux01.dbf

3 /u02/app/oracle/oradata/oratest/undotbs01.dbf

4 /u02/app/oracle/oradata/oratest/users01.dbf

5 /u02/app/oracle/oradata/oratest/example01.dbf

6 /u02/app/oracle/oradata/oratest/users02.dbf

7 /u02/app/oracle/oradata/oratest/ts_ogg01.dbf

8 /u02/app/oracle/oradata/oratest/users03.dbf

8 rows selected.

SQL> select FILE#,NAME from v$tempfile;

FILE# NAME

---------- -----------------------------------------------------------------------------------

1 /u02/app/oracle/oradata/oratest/temp01.dbf

SQL> select * from v$lock where type='MR' order by id1;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000774D8C18 00000000774D8C70 5 MR 1 0 4 0 1309241 0

00000000774D86D8 00000000774D8730 5 MR 2 0 4 0 1309241 0

00000000774D8278 00000000774D82D0 5 MR 3 0 4 0 1309241 0

00000000774D8FB0 00000000774D9008 5 MR 4 0 4 0 1309241 0

00000000774D8B38 00000000774D8B90 5 MR 5 0 4 0 1309241 0

00000000774D87B8 00000000774D8810 5 MR 6 0 4 0 1309241 0

00000000774D8CF8 00000000774D8D50 5 MR 7 0 4 0 1309241 0

00000000774D8DF0 00000000774D8E48 5 MR 8 0 4 0 1309241 0

00000000774D8ED0 00000000774D8F28 5 MR 201 0 4 0 1309241 0

9 rows selected.

MR锁用于保护数据库文件，使得文件在数据库打开、表空间Online时不能执行恢复。当进程对数据文件执行恢复时，需要排他的获得MR锁。当数据库打开时，每个文件上都分配一个MR锁。注意在以上输出中ID1代表文件号，其中也包含了201号临时文件。

3.4 enq: DX - contention

SELECT * FROM V$EVENT_NAME WHERE NAME = 'enq: DX - contention';

DX：Distributed transaction entry

enq: DX - contention是一个分布式事务锁。

enq: DX - contention 和inactive transaction branch这两个事件是相伴的。这两个等待事件是和DBLINK相关的，metalink上有相关的文章：High CPU by Sessions Holding DX Enqueue; Others Waiting 'enq: DX - contention' [ID 1275884.1]

大意就是执行dblink语句时候，由于人为取消终止或网络等问题导致语句触发上面的等待事件

在Oracle中enq: DX 队列锁一般用意保护分布式事务(used to protect distributed transactions)，对应的就存在 enq: DX – contention等待事件。

Id1 / Id2 含义

id2总是0。id1 代表其希望锁定的记录，所以总是distributed transaction elements队列中的一个条记录数（一个整数），由实例参数”distributed_transactions”决定。

3.4.1 案例

http://www.ssc.stn.sh.cn/html/zsk/ITyw/2012-04/5793.html

http://blog.itpub.net/4227/viewspace-709121/

http://www.codeweblog.com/oracle-enq-dx-contention-of-the-resolution-process/

3.5 enq: SQ - contention 序列等待

enq: SQ - contention/row cache lock/DFS lock handle这三个等待事件都与Oracle 的Sequence 有关。

SELECT *

FROM V$EVENT_NAME

WHERE NAME IN

('row cache lock', 'enq: SQ - contention', 'DFS lock handle');

使用如下的SQL我们可以查询到锁的名称和请求的MODE，表的mode值参考表格：

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

Table C-1 Lock Mode Values

Mode Value	Description
1	Null mode
2	Sub-Share
3	Sub-Exclusive
4	Share
5	Share/Sub-Exclusive
6	Exclusive

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('SV','SQ');

Oracle 为了管理Sequence 使用了以下三种锁。

① row cache lock:在调用SEQUNECE.NEXTVAL过程中，将数据字典信息进行物理修改时获取。赋予了NOCACHE属性的SEQUENCE上发生，等待事件为row cache lock。

② SQ锁:在内存上缓存（CACHE）的范围内，调用SEQUENCE.NEXTVAL 期间拥有此锁。赋予了CACHE 属性的SEQUENCE 上发生。赋予了CACHE 属性的SEQUENCE 调用NEXTVAL 期间，应该以SSX 模式获得SQ 锁。许多会话同时为了获取SQ 锁而发生争用过程中，若发生争用，则等待enq: SQ - contention事件。enq: SQ - contention 事件的P2 值是Sequence 的OBJECT ID。因此，若利用P2 值与DBA_OBJECTS 的结合，就可以知道对哪个SEQUENCE 发生了等待现象。

③ SV锁:RAC上节点之间顺序得到保障的情况下，调用SEQUENCE.NEXTVAL期间拥有。赋予CACHE + ORDER属性的SEQUENCE 上发生，等待事件为DFS lock handle，解决办法为：尽量设置为NOORDER并增大其CACHE值。

根据创建Sequence时赋予的属性，整理等待事件的结果如下：

v NOCACHE: row cache lock

v CACHE + NOORDER: enq: SQ - contention

v CACHE + ORDER(RAC): DFS lock handle

创建SEQUENCE赋予的CACHE 值较小时，有enq: SQ - contention等待增加的趋势。CACHE值较小时，内存上事先CACHE的值很快被耗尽，这时需要将数据字典信息物理修改后，再次执行CACHE的工作。在此期间，因为一直拥有SQ 锁，相应的enq: SQ - contention 事件的等待时间也会延长。很不幸的是，在创建SEQUENCE 时，将CACHE 值的缺省值设定为较小的20。因此创建使用量多的SEQUENCE 时，CACHE 值应该取1000 以上的较大值。

另外，偶尔一次性同时创建许多会话时，有时会发生enq: SQ - contention 等待事件。其理由是V$SESSION.AUDSID（auditing session id）列值是利用Sequence创建的。Oracle 在创建新的会话后，利用名为SYS.AUDSES$的Sequence 的nextval，创建AUDSID 值。SYS.AUDSES$ Sequence 的CACHE 大小的缺省值设定为20。许多会话同时连接时，可以将SYS.AUDSES$ Sequence 的CACHE大小扩大至1000，以此可以解决enq: SQ - contention 等待问题。 10g下默认20,11g下默认为10000，通过如下的SQL可以查询：

SELECT * FROM dba_sequences d WHERE d.sequence_name ='AUDSES$';

RAC 上创建SEQUENCE 时，在赋予了CACHE属性的状态下，若没有赋予ORDER 属性，则各节点将会把不同范围的SEQUENCE 值CACHE 到内存上。比如，拥有两个节点的RAC 环境下，创建CACHE 值为100 的SEQUENCE 时，1号节点使用1～100，2 号节点使用101～200。若两个节点之间都通过递增方式使用SEQUENCE，必须赋予如下ORDER 属性。

SQL> CREATE SEQUENCE ORDERED_SEQUENCE CACHE 100 ORDER;

如果是已赋予了CACHE+ORDER 属性的SEQUENCE，Oracle 使用SV 锁进行行同步。即，对赋予了ORDER 属性的Sequence 调用nextval 时，应该以SSX模式拥有SV 锁。在获取SV 锁过程中，如果发生争用时，不是等待row cache lock 事件或enq: SQ - contention 事件，而是等待名为DFS lock handle 事件。正因如此，V$EVENT_NAME 视图上不存在类似"enq:SV-contention"的事件。DFS lock handle 事件是在OPS 或RAC 环境下，除了高速缓冲区同步之外，还有行高速缓冲区或库高速缓冲区的为了同步获取锁的过程中等待的事件。若要保障多个节点之间Sequence顺序，应该在全局范围内获得锁，在此过程中会发生DFS lock handle等待。在获取SV 锁的过程中发生的DFS lock handle等待事件的P1、P2值与enq: SQ - contention等待事件相同（P1=mode+namespace、P2=object#）。因此从P1值能确认是否是SV 锁，通过P2值可以确认对哪些Sequence发生过等待。SV锁争用问题发生时的解决方法与SQ 锁的情况相同，就是将CACHE 值进行适当调整，这也是唯一的方法。

在RAC 等多节点环境下，Sequence 的CACHE 值给性能带来的影响比单节点环境更严重。因此，尽量赋予CACHE+NOORDER 属性，并要给予足够大的CACHE值。如果需要保障顺序，必须赋予CACHE+ORDER 属性。但这时为了保障顺序，实例之间不断发生数据的交换。因此，与赋予了NOORODER属性的时候相比性能稍差。

有一点必须要注意，没有赋予CACHE属性时，不管ORDER 属性使用与否或RAC 环境与否，一直等待row cache lock 事件。row cache lock是可以在全局范围内使用的锁，单实例环境或多实例环境同样可以发生。

没有赋予CACHE属性时，不管ORDER属性是否或RAC环境是否，一直等待ROW CACHE事件，ROW CACHE LOCK是否可以在全局范围内使用的锁，单实例环境或多实例环境同时可以发生。

Oracle Sequence默认是NOORDER，如果设置为ORDER；在单实例环境没有影响，在RAC环境此时，多实例实际缓存相同的序列，此时在多个实例并发取该序列的时候，会有短暂的资源竞争来在多实例之间进行同步。因次性能相比noorder要差，所以RAC环境非必须的情况下不要使用ORDER，尤其要避免NOCACHE ORDER组合。

但是如果使用了Cache，如果此时DB 崩溃了，那么sequence会从cache之后重新开始，在cache中没有使用的sequence会被跳过。即sequence不连续。所以只有在多节点高峰并发量很大的情况且对连续性要求不高的情况下，才使用：noorder + cache。

在下面的链接中讲到了RAC 之间序列同步:

Sequences in Oracle 10g RAC

http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

How does RAC synchronize sequences?

In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.

This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequence's next value.

The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn't be more obscure. That view shows overall statistics for the session.

However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.

In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in wait class Other”. So much for consistency.

3.5.1 DFS lock handle

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.

Parameter	Description
name	See "name and type"
mode	See "mode"
id1	See "id1"
id2	See "id2"

The session needs to get the lock handle.

该等待事件的发生，若不是SV锁的话，多半为bug引起。

DFS lock handle"这一event是在RAC环境中，会话等待获取一个全局锁的句柄时产生的。在RAC中，全局锁的句柄是由DLM(Distributed Lock Manager 分布式锁管理器)所管理和分配的。大量发生这一event说明全局锁句柄资源不够分配了。决定DLM锁数量的参数是_lm_locks，9i以后，它是一个隐含参数，默认值是12000。没有特殊情况，这一值对于一个OLTP系统来说是足够的。我们不能盲目地直接增加资源，而是需要找到导致资源紧张的根本原因。锁资源紧张，说明存在大量事务获取了锁，但是事务没有提交、回滚。那么，又是什么导致了这些事务不结束呢?应用程序代码不完善，没有提交事务?或者那些事务还在等待别的资源?

3.5.2 我碰到的案例

【故障处理】序列cache值过小导致CPU利用率过高：http://blog.itpub.net/26736162/viewspace-2123996/

2016-08-24

【故障处理】序列cache值过小导致CPU利用率过高

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771414&idx=1&sn=0cbc454bc7d5a513bbca6083958e2f34&scene=21#wechat_redirect

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2126079/

● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/5947406.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）

● 小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(642808185)，注明添加缘由

● 于 2016-10-09 10:00~ 2016-10-107 22:20 在公寓完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解！