Oracle等待事件详解

0    720    1

Tags:

👉 本文共约81230个字,系统预计阅读时间或需306分钟。

队列等待

前言部分

导读和注意事项

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

  1. Enqueue队列等待
  2. Enq数据字典
  3. enq: AE - lock
  4. enq: MR锁
  5. enq: DX - contention
  6. 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%';

Oracle等待事件详解

SELECT D.PARAMETER1, COUNT(1)

FROM V$EVENT_NAME D

WHERE NAME LIKE 'enq%'

GROUP BY D.PARAMETER1;

Oracle等待事件详解

可以看出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 包含以下模式:

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

Oracle的enqueue有如下类型:

Enqueue 缩写缩写解释
BLBuffer Cache management
BRBackup/Restore
CFControlfile transaction
CICross-instance Call Invocation
CUBind Enqueue
DFDatafile
DLDirect Loader Index Creation
DMDatabase Mount
DRDistributed Recovery Process
DXDirstributed Transaction
FPFile Object
FSFile Set
HWHigh-water Lock
INInstance Number
IRInstance Recovery
ISInstance State
IVLibrary Cache Invalidation
JIEnqueue used during AJV snapshot refresh
JQJob Queue
KKRedo Log “Kick”
KOMultiple Object Checkpoint
L[A-p]Library Cache Lock
LSLog start or switch
MMMount Definition
MRMedia recovery
N[A-Z]Library Cache bin
PEAlter system set parameter =value
PFPassword file
PIParallel slaves
PRProcess startup
PSParallel slave synchronization
Q[A-Z]Row Cache
ROObject Reuse
RTRedo Thread
RWRow Wait
SCSystem Commit Number
SMSMON
SNSequence Number
SQSequence Number Enqueue
SRSynchronized replication
SSSort segment
STSpace management transaction
SVSequence number Value
TATransaction recovery
TCThread Checkpoint
TEExtend Table
TMDML enqueue
TOTemporary Table Object Enqueue
TSTemporary Segement(also TableSpace)
TTTemporary Table
TXTransaction
ULUser-defined Locks
UNUser name
USUndo segment, Serialization
WLBeing Written Redo Log
XAInstance Attribute Log
XIInstance Registration Lock

所有队列等待锁:

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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle等待事件详解后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部