enqueue一般以兩個字母來表示其不同的型別，比如TX表示事務鎖，TM表示DML鎖，UL表示使用者自定義鎖等。從X$KSQST(x$ksqst[K]ernel [S]ervice en[Q]ueue [S]tatistics [T]ypes)表中可以查到系統中各中enqueue的詳細統計資訊，而v$enqueue_stat檢視中則是基於x$ksqst的聚合統計資訊。

在x$ksqst中，還提供了一個說明列，用於解釋在什麼場景下會產生該enqueue。下面是oracle10.2.0.1中的一個例子：

select distinct ksqsttyp,ksqstexpl from x$ksqst order by ksqsttyp;

No	KSQSTTYP	KSQSTEXPL
1
2	AD	Synchronizes accesses to a specific ASM disk AU
3	AF	This enqueue is used to serialize access to an advisor task
4	AG	Synchronizes generation use of a particular workspace
5	AM	Prevent DB instance registration during ASM instance shutdown
6	AM	Registers DB instance to ASM client state object hash
7	AM	Reserve a Rollback COD entry
8	AO	Synchornizes access to objects and scalar variables
9	AS	Synchronizes new service activation
10	AS	modifying service attributes
11	AT	Serializes 'alter tablespace' operations
12	AU	lock held to operate on the audit index file
13	AW	Global access synchronization to the AW$ table
14	AW	In-use generation state for a particular workspace
15	AW	Row lock synchronization for the AW$ table
16	AW	Synchronizes user accesses to a particular workspace
17	BF	Allocate a bloom filter in a parallel statement
18	BR	Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
19	BR	Lock held to prevent file from decreasing in physical size during RMAN backup
20	CF	Synchronizes accesses to the controlfile
21	CI	Coordinates cross-instance function invocations
22	CL	Synchronizes accesses to label cache for label comparison
23	CL	Synchronizes accesses to label cache when dropping a label
24	CM	indicate ASM diskgroup is mounted
25	CM	serialize access to instance enqueue
26	CN	during descriptor initialization
27	CN	during registration
28	CN	during transaction commit to see concurrent registrations
29	CT	Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
30	CT	Lock held during change tracking space management operations that affect just the data for one thread
31	CT	Lock held during change tracking space management operations that affect the entire change tracking file
32	CT	Lock held to ensure that change tracking data remains in existence until a reader is done with it
33	CT	Lock held to ensure that only one CTWR process is started in a single instance
34	CT	Lock held while enabling or disabling change tracking in RAC
35	CT	Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
36	CU	Recovers cursors in case of death while compiling
37	DB	Synchronizes modification of database wide supplementallogging attributes
38	DD	Synchronizes local accesses to ASM disk groups
39	DF	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
40	DG	Synchronizes accesses to ASM disk groups
41	DL	Lock to prevent index DDL during direct load
42	DM	Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
43	DN	Serializes group number generations
44	DP	Synchronizes access to LDAP parameters
45	DR	Serializes the active distributed recovery operation
46	DS	Prevents a database suspend during LMON reconfiguration
47	DT	Serializes changing the default temporary table spaceand user creation
48	DV	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
49	DX	Serializes tightly coupled distributed transaction branches
50	FA	Synchronizes accesses to open ASM files
51	FB	Ensures that only one process can format data blcoks in auto segment space managed tablespaces
52	FC	LGWR opens an ACD thread
53	FC	SMON recovers an ACD thread
54	FD	Synchronization
55	FG	only 1 process in the cluster may do ACD relocation in a disk group
56	FG	resolve race condition to acquire Disk Group Redo Generation Enqueue
57	FL	Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
58	FL	Synchronization
59	FM	Synchronizes access to global file mapping state
60	FP	Synchronizes various File Object(FOB) operations
61	FR	begin recovery of disk group
62	FS	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
63	FT	allow LGWR to generate redo in this thread
64	FT	prevent LGWR from generating redo in this thread
65	FU	This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
66	HD	Serializes accesses to ASM SGA data structures
67	HP	Synchronizes accesses to queue pages
68	HQ	Synchronizes the creation of new queue IDs
69	HV	Lock used to broker the high water mark during parallel inserts
70	HW	Lock used to broker the high water mark during parallel inserts
71	IA
72	ID	Lock held to prevent other processes from performing controlfile transaction while NID is running
73	IL	Synchronizes accesses to internal label data structures
74	IM	Serializes block recovery for IMU txn
75	IR	Synchronizes instance recovery
76	IR	Synchronizes parallel instance recovery and shutdown immediate
77	IS	Enqueue used to synchronize instance state changes
78	IT	Synchronizes accesses to a temp object's metadata
79	JD	Synchronizes dates between job queue coordinator and slave processes
80	JI	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
81	JQ	Lock to prevent multiple instances from running a single job
82	JS	Lock got during event notification
83	JS	Lock got when adding subscriber to event q
84	JS	Lock got when doing window open/close
85	JS	Lock got when dropping subscriber to event q
86	JS	Lock obtained when cleaning up q memory
87	JS	Lock on internal scheduler queue
88	JS	Lock to prevent job from running elsewhere
89	JS	Lock to recover jobs running on crashed RAC inst
90	JS	Scheduler non-global enqueues
91	JS	Synchronizes accesses to the job cache
92	KK	Lock held by open redo thread, used by other instances to force a log switch
93	KM	Synchronizes various Resource Manager operations
94	KO	Coordinates fast object checkpoint
95	KP	Synchronizes kupp process startup
96	KT	Synchronizes accesses to the current Resource Manager plan
97	MD	Lock held during materialized view log DDL statements
98	MH	Lock used for recovery when setting Mail Host for AQ e-mail notifications
99	MK	changing values in enc$
100	ML	Lock used for recovery when setting Mail Port for AQ e-mail notifications
101	MN	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
102	MR	Lock used to coordinate media recovery with other uses of datafiles
103	MS	Lock held during materialized view refresh to setup MV log
104	MW	This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
105	OC	Synchronizes write accesses to the outline cache
106	OL	Synchronizes accesses to a particular outline name
107	OQ	Synchronizes access to olapi history allocation
108	OQ	Synchronizes access to olapi history closing
109	OQ	Synchronizes access to olapi history flushing
110	OQ	Synchronizes access to olapi history globals
111	OQ	Synchronizes access to olapi history parameter CB
112	OW	initializing the wallet context
113	OW	terminate the wallet context
114	PD	Prevents others from updating the same property
115	PE	Synchronizes system parameter updates
116	PF	Synchronizes accesses to the password file
117	PG	Synchronizes global system parameter updates
118	PH	Lock used for recovery when setting Proxy for AQ HTTP notifications
119	PI	Communicates remote Parallel Execution Server Process creation status
120	PL	Coordinates plug-in operation of transportable tablespaces
121	PR	Synchronizes process startup
122	PS	Parallel Execution Server Process reservation and synchronization
123	PT	Synchronizes access to ASM PST metadata
124	PV	Synchronizes instance shutdown_slvstart
125	PV	Synchronizes slave start_shutdown
126	PW	DBWR 0 holds enqueue indicating prewarmed buffers present in cache
127	PW	Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
128	RB	Serializes ASM rollback recovery operations
129	RF	Anoints 1 instance's DMON as chief to other instances' DMONs
130	RF	Anoints 1 instance's DMON as healthcheck master
131	RF	Ensures atomicity of log transport setup
132	RF	Ensures r/w atomicity of DG configuration metadata
133	RF	Means for detecting whether database has been observed recently
134	RF	Means for detecting whether standby has connectivity to primary
135	RF	Means for sharing Fast-Start Failover state changes around a RAC
136	RF	Means for synchronizing Fast-Start Failover lock operations
137	RF	Means for waiting for lock request to timeout
138	RF	Synchronizes apply instance failure detection and failover operation
139	RF	Synchronizes critical apply instance among primary instances
140	RF	Synchronizes selection of the new apply instance
141	RN	Coordinates nab computations of online logs during recovery
142	RO	Coordinates fast object reuse
143	RO	Coordinates flushing of multiple objects
144	RP	Enqueue held when resilvering is needed or when datablock is repaired from mirror
145	RS	Lock held to make alert level persistent
146	RS	Lock held to prevent aging list update
147	RS	Lock held to prevent deleting file to reclaim space
148	RS	Lock held to prevent file from accessing during space reclaimation
149	RS	Lock held to prevent file from accessing while reusing circular record
150	RS	Lock held to read alert level
151	RS	Lock held to write alert level
152	RT	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
153	RU	Results of rolling upgrade CIC
154	RU	Serializes rolling upgrade operations
155	RW	Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
156	SB	Synchronizes Logical Standby metadata operations
157	SE	Synchronizes transparent session migration operations
158	SF	Lock used for recovery when setting Sender for AQ e-mail notifications
159	SH	Should seldom see this contention as this Enqueue is always acquired in no-wait mode
160	SI	Prevents multiple streams tabel instantiations
161	SK	Serialize shrink of a segment
162	SQ	Lock to ensure that only one process can replenish the sequence cache
163	SR	Coordinates replication / streams operations
164	SS	Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
165	ST	Synchronizes space management activities in dictionary-managed tablespaces
166	SU	Serializes access to SaveUndo Segment
167	SW	Coordinates the 'alter system suspend' operation
168	TA	Serializes operations on undo segments and undo tablespaces
169	TB	Synchronizes writes to the SQL Tuning Base Existence Cache
170	TC	Lock held to guarantee uniqueness of a tablespace checkpoint
171	TC	Lock of setup of a unqiue tablespace checkpoint in null mode
172	TD	KTF dumping time/scn mappings in SMON_SCN_TIME table
173	TE	KTF broadcasting
174	TF	Serializes dropping of a temporary file
175	TL	Serializes threshold log table read and update
176	TM	Synchronizes accesses to an object
177	TO	Synchronizes DDL and DML operations on a temp object
178	TQ	TM access to the queue table
179	TS	Serializes accesses to temp segments
180	TT	Serializes DDL operations on tablespaces
181	TW	Lock held by one instance to wait for transactions on all instances to finish
182	TX	Allocating an ITL entry in order to begin a transaction
183	TX	Lock held by a transaction to allow other transactions to wait for it
184	TX	Lock held on a particular row by a transaction to prevent other transactions from modifying it
185	TX	Lock held on an index during a split to prevent other operations on it
186	UL	Lock used by user applications
187	US	Lock held to perform DDL on the undo segment
188	WA	Lock used for recovery when setting Watermark for memory usage in AQ notifications
189	WF	This enqueue is used to serialize the flushing of snapshots
190	WL	Coordinates access to redo log files and archive logs
191	WP	This enqueue handles concurrency between purging and baselines
192	WR	Coordinates access to logs by Async LNS and ARCH/FG
193	XH	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
194	XQ	prevent relocation during _recovery_asserts checking
195	XQ	wait for recovery before doing relocation
196	XR	Lock held during database force logging mode
197	XR	Lock held during database quiesce
198	XY	Lock used for internal testing
199	ZG	Coordinates file group operations

注意到，同樣的enqueue可能在不同的場景中發生。比如TX，就可能在下列情況下發生: ITL分配，其他事務佔用，資料行被鎖定，索引塊分裂等。所以在處理enqueue等待時，除了要清楚enqueue的型別，還要清楚是什麼原因導致該enqueue的，才好對症下藥。

出處: http://ningoo.itpub.net/post/2149/234876

enqueue的種類(zt)

相關文章