【等待事件】等待事件系列（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 xksqstst,xksqeqtyp 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://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

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
mode	See
id1	See
id2	See

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)和個人微信公眾號（）上有同步更新

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

● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/5947406.html

● 本文pdf版：（提取碼：ed9b）

● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(642808185)，註明新增緣由

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

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解！