合 Oracle等待事件详解
队列等待
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
- Enqueue队列等待
- Enq数据字典
- enq: AE - lock
- enq: MR锁
- enq: DX - contention
- enq: SQ - contention 序列等待
Enqueue(队列等待)
简介
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 |
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)