首页 > Linux操作系统 > Linux操作系统 > enqueue的种类


原创 Linux操作系统 作者:NinGoo 时间:2019-06-23 16:06:07 0 删除 编辑

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的聚合统计信息。


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

2ADSynchronizes accesses to a specific ASM disk AU
3AFThis enqueue is used to serialize access to an advisor task
4AGSynchronizes generation use of a particular workspace
5AMPrevent DB instance registration during ASM instance shutdown
6AMRegisters DB instance to ASM client state object hash
7AMReserve a Rollback COD entry
8AOSynchornizes access to objects and scalar variables
9ASSynchronizes new service activation
10ASmodifying service attributes
11ATSerializes 'alter tablespace' operations
12AUlock held to operate on the audit index file
13AWGlobal access synchronization to the AW$ table
14AWIn-use generation state for a particular workspace
15AWRow lock synchronization for the AW$ table
16AWSynchronizes user accesses to a particular workspace
17BFAllocate a bloom filter in a parallel statement
18BRLock held to allow cleanup from backup mode during an RMAN proxy-copy backup
19BRLock held to prevent file from decreasing in physical size during RMAN backup
20CFSynchronizes accesses to the controlfile
21CICoordinates cross-instance function invocations
22CLSynchronizes accesses to label cache for label comparison
23CLSynchronizes accesses to label cache when dropping a label
24CMindicate ASM diskgroup is mounted
25CMserialize access to instance enqueue
26CNduring descriptor initialization
27CNduring registration
28CNduring transaction commit to see concurrent registrations
29CTLock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
30CTLock held during change tracking space management operations that affect just the data for one thread
31CTLock held during change tracking space management operations that affect the entire change tracking file
32CTLock held to ensure that change tracking data remains in existence until a reader is done with it
33CTLock held to ensure that only one CTWR process is started in a single instance
34CTLock held while enabling or disabling change tracking in RAC
35CTLock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
36CURecovers cursors in case of death while compiling
37DBSynchronizes modification of database wide supplementallogging attributes
38DDSynchronizes local accesses to ASM disk groups
39DFEnqueue held by foreground or DBWR when a datafile is brought online in RAC
40DGSynchronizes accesses to ASM disk groups
41DLLock to prevent index DDL during direct load
42DMEnqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
43DNSerializes group number generations
44DPSynchronizes access to LDAP parameters
45DRSerializes the active distributed recovery operation
46DSPrevents a database suspend during LMON reconfiguration
47DTSerializes changing the default temporary table spaceand user creation
48DVSynchronizes access to lower-version Diana (PL/SQL intermediate representation)
49DXSerializes tightly coupled distributed transaction branches
50FASynchronizes accesses to open ASM files
51FBEnsures that only one process can format data blcoks in auto segment space managed tablespaces
52FCLGWR opens an ACD thread
53FCSMON recovers an ACD thread
55FGonly 1 process in the cluster may do ACD relocation in a disk group
56FGresolve race condition to acquire Disk Group Redo Generation Enqueue
57FLEnqueue used to synchronize Flashback Database and and deletion of flashback logs.
59FMSynchronizes access to global file mapping state
60FPSynchronizes various File Object(FOB) operations
61FRbegin recovery of disk group
62FSEnqueue used to synchronize recovery and file operations or synchronize dictionary check
63FTallow LGWR to generate redo in this thread
64FTprevent LGWR from generating redo in this thread
65FUThis enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
66HDSerializes accesses to ASM SGA data structures
67HPSynchronizes accesses to queue pages
68HQSynchronizes the creation of new queue IDs
69HVLock used to broker the high water mark during parallel inserts
70HWLock used to broker the high water mark during parallel inserts
72IDLock held to prevent other processes from performing controlfile transaction while NID is running
73ILSynchronizes accesses to internal label data structures
74IMSerializes block recovery for IMU txn
75IRSynchronizes instance recovery
76IRSynchronizes parallel instance recovery and shutdown immediate
77ISEnqueue used to synchronize instance state changes
78ITSynchronizes accesses to a temp object's metadata
79JDSynchronizes dates between job queue coordinator and slave processes
80JILock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
81JQLock to prevent multiple instances from running a single job
82JSLock got during event notification
83JSLock got when adding subscriber to event q
84JSLock got when doing window open/close
85JSLock got when dropping subscriber to event q
86JSLock obtained when cleaning up q memory
87JSLock on internal scheduler queue
88JSLock to prevent job from running elsewhere
89JSLock to recover jobs running on crashed RAC inst
90JSScheduler non-global enqueues
91JSSynchronizes accesses to the job cache
92KKLock held by open redo thread, used by other instances to force a log switch
93KMSynchronizes various Resource Manager operations
94KOCoordinates fast object checkpoint
95KPSynchronizes kupp process startup
96KTSynchronizes accesses to the current Resource Manager plan
97MDLock held during materialized view log DDL statements
98MHLock used for recovery when setting Mail Host for AQ e-mail notifications
99MKchanging values in enc$
100MLLock used for recovery when setting Mail Port for AQ e-mail notifications
101MNSynchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
102MRLock used to coordinate media recovery with other uses of datafiles
103MSLock held during materialized view refresh to setup MV log
104MWThis enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
105OCSynchronizes write accesses to the outline cache
106OLSynchronizes accesses to a particular outline name
107OQSynchronizes access to olapi history allocation
108OQSynchronizes access to olapi history closing
109OQSynchronizes access to olapi history flushing
110OQSynchronizes access to olapi history globals
111OQSynchronizes access to olapi history parameter CB
112OWinitializing the wallet context
113OWterminate the wallet context
114PDPrevents others from updating the same property
115PESynchronizes system parameter updates
116PFSynchronizes accesses to the password file
117PGSynchronizes global system parameter updates
118PHLock used for recovery when setting Proxy for AQ HTTP notifications
119PICommunicates remote Parallel Execution Server Process creation status
120PLCoordinates plug-in operation of transportable tablespaces
121PRSynchronizes process startup
122PSParallel Execution Server Process reservation and synchronization
123PTSynchronizes access to ASM PST metadata
124PVSynchronizes instance shutdown_slvstart
125PVSynchronizes slave start_shutdown
126PWDBWR 0 holds enqueue indicating prewarmed buffers present in cache
127PWDirect Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
128RBSerializes ASM rollback recovery operations
129RFAnoints 1 instance's DMON as chief to other instances' DMONs
130RFAnoints 1 instance's DMON as healthcheck master
131RFEnsures atomicity of log transport setup
132RFEnsures r/w atomicity of DG configuration metadata
133RFMeans for detecting whether database has been observed recently
134RFMeans for detecting whether standby has connectivity to primary
135RFMeans for sharing Fast-Start Failover state changes around a RAC
136RFMeans for synchronizing Fast-Start Failover lock operations
137RFMeans for waiting for lock request to timeout
138RFSynchronizes apply instance failure detection and failover operation
139RFSynchronizes critical apply instance among primary instances
140RFSynchronizes selection of the new apply instance
141RNCoordinates nab computations of online logs during recovery
142ROCoordinates fast object reuse
143ROCoordinates flushing of multiple objects
144RPEnqueue held when resilvering is needed or when datablock is repaired from mirror
145RSLock held to make alert level persistent
146RSLock held to prevent aging list update
147RSLock held to prevent deleting file to reclaim space
148RSLock held to prevent file from accessing during space reclaimation
149RSLock held to prevent file from accessing while reusing circular record
150RSLock held to read alert level
151RSLock held to write alert level
152RTThread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
153RUResults of rolling upgrade CIC
154RUSerializes rolling upgrade operations
155RWLock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
156SBSynchronizes Logical Standby metadata operations
157SESynchronizes transparent session migration operations
158SFLock used for recovery when setting Sender for AQ e-mail notifications
159SHShould seldom see this contention as this Enqueue is always acquired in no-wait mode
160SIPrevents multiple streams tabel instantiations
161SKSerialize shrink of a segment
162SQLock to ensure that only one process can replenish the sequence cache
163SRCoordinates replication / streams operations
164SSEnsures that sort segments created during parallel DML operations aren't prematurely cleaned up
165STSynchronizes space management activities in dictionary-managed tablespaces
166SUSerializes access to SaveUndo Segment
167SWCoordinates the 'alter system suspend' operation
168TASerializes operations on undo segments and undo tablespaces
169TBSynchronizes writes to the SQL Tuning Base Existence Cache
170TCLock held to guarantee uniqueness of a tablespace checkpoint
171TCLock of setup of a unqiue tablespace checkpoint in null mode
172TDKTF dumping time/scn mappings in SMON_SCN_TIME table
173TEKTF broadcasting
174TFSerializes dropping of a temporary file
175TLSerializes threshold log table read and update
176TMSynchronizes accesses to an object
177TOSynchronizes DDL and DML operations on a temp object
178TQTM access to the queue table
179TSSerializes accesses to temp segments
180TTSerializes DDL operations on tablespaces
181TWLock held by one instance to wait for transactions on all instances to finish
182TXAllocating an ITL entry in order to begin a transaction
183TXLock held by a transaction to allow other transactions to wait for it
184TXLock held on a particular row by a transaction to prevent other transactions from modifying it
185TXLock held on an index during a split to prevent other operations on it
186ULLock used by user applications
187USLock held to perform DDL on the undo segment
188WALock used for recovery when setting Watermark for memory usage in AQ notifications
189WFThis enqueue is used to serialize the flushing of snapshots
190WLCoordinates access to redo log files and archive logs
191WPThis enqueue handles concurrency between purging and baselines
192WRCoordinates access to logs by Async LNS and ARCH/FG
193XHLock used for recovery when setting No Proxy Domains for AQ HTTP notifications
194XQprevent relocation during _recovery_asserts checking
195XQwait for recovery before doing relocation
196XRLock held during database force logging mode
197XRLock held during database quiesce
198XYLock used for internal testing
199ZGCoordinates file group operations

注意到,同样的enqueue可能在不同的场景中发生。比如TX,就可能在下列情况下发生: ITL分配,其他事务占用,数据行被锁定,索引块分裂等。所以在处理enqueue等待时,除了要清楚enqueue的类型,还要清楚是什么原因导致该enqueue的,才好对症下药。

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量