谈到Oracle数据库等待事件的知识库问题。Oracle的锁实际上也是十分重要的等待事件,遇到锁的时候如何解读锁的含义,从而定位问题的原因也是DBA需要掌握的一种技巧。今天我把我们团队这些年梳理的一些知识开放出来,和大家分享。D-SMART的知识库也是这样一点点的建设起来的。这300多个锁分析的知识库是Oracle 近2000个等待事件的知识库中的一个十分重要的组成部分,希望我分享的这个内容能够对大家在今后的运维工作中有索帮助。
锁名称 | 风险 | 说明 | 根因 |
enq: AB - ABMR process initialized | Auto BMR锁,持有锁以确保ABMR 进程初始化完成 | ABMR | |
enq: AB - ABMR process start/stop | Auto BMR锁,持有锁以确保集群中只有一个ABMR进程启动 | ABMR | |
enq: AD - allocate AU | ASM Disk AU锁,同步访问特定asm磁盘AU | ASM | |
enq: AD - deallocate AU | ASM Disk AU锁,同步访问特定asm磁盘AU | ASM | |
enq: AD - relocate AU | ASM Disk AU锁,同步访问特定asm磁盘AU | ASM | |
enq: AE - lock | Edition Lock,用于防止正在使用中的edition被删除。 | ||
enq: AF - task serialization | Advisor Framework,This enqueue is used to serialize access to an advisor task | ADVISOR | |
enq: AG - contention | Analytic Workspace Generation, Synchronizes generation use of a particular workspace | OLAP | |
enq: AK - contention | AK锁等待 | ||
enq: AM - ASM ACD Relocation | ASM Enqueue,Block ASM cache freeze | ASM | |
enq: AM - ASM Amdu Dump | ASM Enqueue,Allow only one AMDU dump when block read failure | ASM | |
enq: AM - ASM cache freeze | ASM Enqueue, Start ASM cache freeze | ASM | |
enq: AM - ASM DD update SrRloc | ASM Enqueue,Serializes DD update for SrRloc | ASM | |
enq: AM - ASM disk based alloc/dealloc | ASM Enqueue,Synchronizes disk based allocations/deallocations | ASM | |
enq: AM - ASM file descriptor | ASM Enqueue,Serializes access to ASM file descriptors | ASM | |
enq: AM - ASM File Destroy | ASM Enqueue, Prevent same file deletion race | ASM | |
enq: AM - ASM file relocation | ASM Enqueue,Serializes file relocation shrink and drop | ASM | |
enq: AM - ASM Grow ACD | ASM Enqueue,Serializes growing ASM ACD | ASM | |
enq: AM - ASM Password File Update | ASM Enqueue,Allow one ASM password file update per cluster at a time | ASM | |
enq: AM - ASM reserved | ASM Enqueue,保留的锁,检查ID1确定 | ASM | |
enq: AM - ASM User | ASM Enqueue,Prevents a user from being dropped if it owns any open files | ASM | |
enq: AM - background COD reservation | ASM Enqueue,Reserve a background COD entry | ASM | |
enq: AM - block repair | ASM Enqueue,Serializes block repairs | ASM | |
enq: AM - client registration | ASM Enqueue,Registers DB instance to ASM client state object hash | ASM | |
enq: AM - disk offline | ASM Enqueue,Synchronizes disk offlines | ASM | |
enq: AM - group block | ASM Enqueue, ASM group block | ASM | |
enq: AM - group use | ASM Enqueue, Client group use | ASM | |
enq: AM - rollback COD reservation | ASM Enqueue,Reserve a rollback COD entry | ASM | |
enq: AM - shutdown | ASM Enqueue,Prevent DB instance registration during ASM instance shutdown | ASM | |
enq: AO - contention | MultiWriter Object Access,Synchornizes access to objects and scalar variables | ||
enq: AP - contention | SPM Autopurge,用于防止同时进行SQL PLAN BASELINE的PURGE操作 | SPM | |
enq: AR - contention | enq: AR - contention | ||
enq: AS - service activation | 高 | 同步新服务激活锁,一般在系统启动阶段出现,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | 数据库健康 |
enq: AT - contention | Alter Tablespace锁 | TABLESPACE | |
enq: AV - add/enable first volume in DG | ASM volume locks,Serialize taking the AVD DG enqueue | ASM卷操作 | |
enq: AV - AVD client registration | ASM volume locks,Serialize inst reg and first DG use | ASM卷操作 | |
enq: AV - persistent DG number | ASM volume locks,防止DG号码冲突 | ASM卷操作 | |
enq: AV - volume relocate | ASM volume locks, Serialize relocating volume extents | ASM卷操作 | |
enq: AW - AW generation lock | Analytic Workspace ,In-use generation state for a particular workspace | OLAP | |
enq: AW - AW state lock | Analytic Workspace ,Row lock synchronization for the AW$ table | OLAP | |
enq: AW - AW$ table lock | Analytic Workspace , Global access synchronization to the AW$ table | OLAP | |
enq: AW - user access for AW | Analytic Workspace ,Synchronizes user accesses to a particular workspace | OLAP | |
enq: AY - contention | KSXA Test Affinity Dictionary,Affinity Dictionary test affinity synchronization | ||
enq: BB - 2PC across RAC instances | 高 | RAC环境下的跨实例两阶段提交相关的锁等待,检查 _CLUSTERWIDE_GLOBAL_TRANSACTIONS设置,再进行后续分析 | RAC DTP |
enq: BF - allocation contention | BLOOM FILTER,Allocate a bloom filter in a parallel statement | BLOOM | |
enq: BF - PMON Join Filter cleanup | BLOOM FILTER,PMON bloom filter recovery | BLOOM | |
enq: BM - clonedb bitmap file write | 克隆数据库时位图文件写锁等待 | CLONEDB | |
enq: BO - contention | BO锁等待 | ||
enq: BR - file shrink | Backup/Restore锁,锁定以防止在RMAN备份期间文件的物理大小减小 | 备份恢复 | |
enq: BR - multi-section restore header | Backup/Restore锁,在多个分段恢复的时候串行化文件头访问 | 备份恢复 | |
enq: BR - multi-section restore section | Backup/Restore锁,在多个分段恢复的时候串行化分段访问 | 备份恢复 | |
enq: BR - perform autobackup | Backup/Restore锁,正在自动备份控制文件时候的锁 | 备份恢复 | |
enq: BR - proxy-copy | Backup/Restore锁,锁定以允许在RMAN代理副本备份期间从备份模式进行清除 | 备份恢复 | |
enq: BR - request autobackup | Backup/Restore锁,自动备份控制文件时的锁 | 备份恢复 | |
enq: BR - space info datafile hdr update | Backup/Restore锁,在并行恢复的时候防止多个进程同时更新文件头 | 备份恢复 | |
enq: BS - krb spare0 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare1 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare2 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare3 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare4 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare5 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare6 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare7 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare8 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: BS - krb spare9 | Backup/Restore spare enqueue锁, | 备份恢复 | |
enq: CA - contention | enq: CA - contention,同步各种IO校准指标 | ||
enq: CF - contention | 控制文件访问锁,,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题,检查系统IO情况、文件系统健康状态、是否处于数据库备份状态、日志频率是否过高等 | 数据库健康、数据库IO、REDO LOG、 | |
enq: CI - contention | 高 | CI锁等待,由于一些内部操作出现了CI锁等待,如果该等待持续存在,说明系统存在较为严重的健康问题 | 数据库并发 |
enq: CL - compare labels | Label Security cache锁 | ||
enq: CL - drop label | Label Security cache锁,删除一个标签的时候同步CACHE | ||
enq: CM - diskgroup dismount | ASM Instance Enqueue, serialize asm diskgroup dismount | ASM实例 | |
enq: CM - gate | ASM Instance Enqueue,serialize access to instance enqueue | ASM实例 | |
enq: CM - instance | ASM Instance Enqueue,indicate ASM diskgroup is mounted | ASM实例 | |
enq: CN - race with init | KTCN REG enq锁,描述符初始化期间 | ||
enq: CN - race with reg | KTCN REG enq锁,在事务提交期间查看并发注册 | ||
enq: CN - race with txn | KTCN REG enq锁,在注册阶段 | ||
enq: CO - master slave det | KTUCLO Master Slave enq锁,enqueue held be Master in Cleanout Optim | ||
enq: CQ - contention | Cleanup querycache registrations,Serializes access to cleanup client query cache registrations | QUERYCACHE | |
enq: CR - block range reuse ckpt | REUSE BLOCK RANG锁,串行化fast block range reuse ckpt,如果该锁经常出现或者阻塞了大量的会话,说明系统中存在大量的DROP TABLE/TRUNCATE等操作 | CHECKPOINT | |
enq: CT - change stream ownership | Block Change Tracking锁,启用更改跟踪时由一个实例保留锁定,以保证对特定于线程的资源的访问 | BCT | |
enq: CT - CTWR process start/stop | Block Change Tracking锁,锁定以确保在单个实例中仅启动一个CTWR进程 | BCT | |
enq: CT - global space management | Block Change Tracking锁,在更改跟踪空间管理操作期间锁定,影响整个更改跟踪文件 | BCT | |
enq: CT - local space management | Block Change Tracking锁,在更改跟踪空间管理操作期间锁定,仅影响一个线程的数据 | BCT | |
enq: CT - reading | Block Change Tracking锁,锁定以确保更改跟踪数据保持存在,直到读取者完成读取操作 | BCT | |
enq: CT - state | Block Change Tracking锁,在启用或禁用更改跟踪时锁定,以确保一次只能由一个用户启用或禁用 | BCT | |
enq: CT - state change gate 1 | Block Change Tracking锁,在RAC中启用或禁用更改跟踪时锁定 | BCT | |
enq: CT - state change gate 2 | Block Change Tracking锁,在RAC中启用或禁用更改跟踪时锁定 | BCT | |
enq: CU - contention | 高 | Cursor锁,用于恢复CURSOR编译时死掉的会话 | CURSOR |
enq: CX - TEXT: Index Specific Lock | TEXT: Index Specific Lock,Index Specific Lock on CTX index | 全文索引 | |
enq: DB - contention | 数据库锁等待。 | ||
enq: DD - contention | ASM Local Disk Group,Synchronizes local accesses to ASM disk groups | ASM磁盘组 | |
enq: DF - contention | 高 | Datafile Online in RAC锁,在RAC环境中将某个数据文件ONLINE的时候的锁,一般持有者为前台进程或者DBWR | 数据文件 |
enq: DG - contention | ASM Disk Group Modification,Synchronizes accesses to ASM disk groups | ASM磁盘组 | |
enq: DI - contention | DI锁等待 | ||
enq: DL - contention | Direct Loader Index Creation锁,阻止被LOAD的表上的索引DDL操作的锁 | SQLLDR | |
enq: DM - contention | Database Mount/Open锁,前台进程或者DBWR持有该锁用于同步数据库MOUNT/OPEN或者PLUGGABLE数据库的OPEN操作,防止与其他操作冲突 | 数据库锁 | |
enq: DN - contention | enq: DN - contention | ||
enq: DO - disk online | ASM Disk Online Lock ,Synchronizes disk onlines and their recovery | ASM磁盘 | |
enq: DO - disk online operation | ASM Disk Online Lock , disk online operation | ASM磁盘 | |
enq: DO - disk online recovery | ASM Disk Online Lock ,Synchronizes disk onlines and their recovery | ASM磁盘 | |
enq: DO - Staleness Registry create | ASM Disk Online Lock ,Synchronizes Staleness Registry creation | ASM磁盘 | |
enq: DO - startup of MARK process | ASM Disk Online Lock ,Synchronizes startup of MARK process | ASM磁盘 | |
enq: DP - contention | LDAP Parameter | LDAP | |
enq: DQ - contention | enq: DQ - contention | ||
enq: DR - contention | 高 | 分布式恢复锁,用于分布式恢复操作。如果该锁出现较多,说明系统中的失败的分布式事务较多 | 数据库并发,数据库健康 |
enq: DS - contention | 高 | alter system suspend'命令要挂起系统,DS锁是LMON RECONFIGURATION集群的时候阻止该命令挂起系统的。平时系统不应该出现该锁,如果该锁经常出现或者阻塞了大量的会话,则说明当前系统健康存在问题 | 数据库健康 |
enq: DT - contention | Default Temporary Tablespace锁,串行化修改缺省临时表空间操作和用户创建操作 | 临时表空间 | |
enq: DV - contention | Diana Versioning, Synchronizes access to lower-version Diana (PL/SQL intermediate representation) ,和PL/SQL编译代码有关 | PLSQL | |
enq: DW - contention | In memory Dispenser,串行化in memory dispenser操作 | IMDB | |
enq: DX - contention | 高 | 分布式事物锁,如果该锁出现较多,说明分布式事物的性能存在问题 | 数据库并发,数据库性能 |
enq: FA - access file | ASM File Access Lock,Synchronizes accesses to open ASM files | ASM文件 | |
enq: FB - contention | 高 | 格式化数据块锁,如果该锁经常出现或者阻塞了大量会话,说明数据库存在增长较快的表,格式化数据块和高水位提升影响了应用的性能 | 数据库性能、数据库IO,数据库并发 |
enq: FC - open an ACD thread | Disk Group Chunk Mount,LGWR opens an ACD thread | ASM磁盘组 | |
enq: FC - recover an ACD thread | Disk Group Chunk Mount,SMON recovers an ACD thread | ASM磁盘组 | |
enq: FD - Flashback coordinator | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FD - Flashback logical operations | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FD - Flashback on/off | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FD - Marker generation | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FD - Restore point create/drop | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FD - Tablespace flashback on/off | Flashback Database锁,同步日志 | FLASHBACK | |
enq: FE - contention | KTFA Recovery锁,串行化闪回日志恢复 | FLASHBACK | |
enq: FG - FG redo generation enq race | ACD Relocation Gate Enqueue,resolve race condition to acquire Disk Group Redo Generation Enqueue | ASM ACD | |
enq: FG - LGWR redo generation enq race | ACD Relocation Gate Enqueue,resolve race condition to acquire Disk Group Redo Generation Enqueue | ASM ACD | |
enq: FG - serialize ACD relocate | ACD Relocation Gate Enqueue,only 1 process in the cluster may do ACD relocation in a disk group | ASM ACD | |
enq: FL - Flashback database log | Flashback database log锁,同步日志 | FLASHBACKDB | |
enq: FL - Flashback db command | Flashback database log锁,防止在做闪回数据库的时候删除相关的闪回日志 | FLASHBACKDB | |
enq: FM - contention | 高 | 对全局文件映射表状态同步访问的锁,如果该锁长时间出现或者大量出现,说明数据库已经出现了较为严重的问题 | 数据库性能、数据库健康 |
enq: FP - global fob contention | 高 | 全局文件对象表锁冲突,一般由于BUG导致,如果该等待出现较多,说明系统性能状态存在严重问题 | 数据库配置、数据库性能 |
enq: FR - contention | Disk Group Recovery,begin recovery of disk group | DG RECOVERY | |
enq: FR - recover the thread | Disk Group Recovery,wait for lock domain detach | DG RECOVERY | |
enq: FR - use the thread | Disk Group Recovery,indicate this ACD thread is alive | DG RECOVERY | |
enq: FS - contention | 高 | File Set / Dictionary Check锁, 用于同步恢复和文件操作、数据字典检查的锁。如果该锁经常出现,并且阻塞了大量会话,说明系统存在异常,REDO方面出现了问题 | 数据库健康 |
enq: FT - allow LGWR writes | Disk Group Redo Generation,allow LGWR to generate redo in this thread | ASM REDO | |
enq: FT - disable LGWR writes | Disk Group Redo Generation,prevent LGWR from generating redo in this thread | ASM REDO | |
enq: FU - contention | DBFUS,此队列用于序列化数据库功能使用和高水位标记统计的捕获 | 数据库功能组件 | |
enq: FW - contention | enq: FW - contention | ||
enq: FX - issue ACD Xtnt Relocation CIC | ACD Xtnt Info CIC ,ARB relocates ACD extent | ASM ACD | |
enq: HD - contention | ASM Disk Header ,Serializes accesses to ASM SGA data structures | ASM磁盘组 | |
enq: HP - contention | Queue Page锁,同步QUEUE PAGE的访问 | ||
enq: HQ - contention | Hash Queue锁,串行化HASH queue ID的变化 | ||
enq: HV - contention | 高 | 和高水位,文件扩展有关的锁等待,该等待较多说明数据库对象(表或索引)数据扩展较快,对象的高水位推进十分频繁,需要检查应用 | 高水位 |
enq: HW - contention | 高 | 高水位锁等待,该等待较多说明数据库对象(表或索引)数据扩展较快,对象的高水位推进十分频繁,需要检查应用 | 数据库并发 |
enq: IA - contention | Internal | ||
enq: ID - contention | NID 命令锁,当某个NID命令正在执行时避免其他命令执行,在正常生产环境不应该出现 | 数据库改名 | |
enq: IL - contention | 安全标签锁,用于安全标签内部数据访问的同步操作 | 安全标签 | |
enq: IM - contention for blr | 高 | Kti blr lock锁,串行化由于IMU(in memory undo)事物发起的块恢复操作,如果该锁经常出现或者阻塞了大量会话说明系统健康度存在问题 | 数据库健康,UNDO |
enq: IR - contention | Instance Recovery锁,用于同步实例RECOVERY | 实例恢复 | |
enq: IR - contention2 | Instance Recovery锁,用于同步并行实例恢复和实例关闭操作 | 实例恢复 | |
enq: IS - contention | Instance State锁,同步实例状态变化的锁 | 实例状态 | |
enq: IT - contention | In-Mem Temp Table Meta Creation,Synchronizes accesses to a temp object's metadata | IMDB | |
enq: JD - contention | Job Queue Date,Synchronizes dates between job queue coordinator and slave processes | JOB | |
enq: JI - contention | Materialized View,在物化视图操作期间保持锁定(如刷新,更改)以防止在同一物化视图上进行并发操作 | 物化视图 | |
enq: JQ - contention | Job Queue,防止多个实例同时执行同一个JOB | JOB | |
enq: JS - contention | Job Scheduler, | JOB | |
enq: JS - job recov lock | Job Scheduler, | JOB | |
enq: JS - job run lock - synchronize | Job Scheduler, | JOB | |
enq: JS - queue lock | Job Scheduler, | JOB | |
enq: JS - sch locl enqs | Job Scheduler, | JOB | |
enq: JX - cleanup of queue | JX锁,等待队列清理完成,和并行查询有关 | 并行查询 | |
enq: JX - SQL statement queue | JX锁,SQL语句队列锁等待,和并行查询有关 | 并行查询 | |
enq: KD - determine DBRM master | enq: KD - determine DBRM master,和资源管理有关 | 资源管理器 | |
enq: KE - contention | enq: KE - contention | 资源管理器 | |
enq: KL - contention | enq: KL - contention | 资源管理器 | |
enq: KM - contention | enq: KM - contention,同步资源管理操作的锁等待,和资源管理有关 | 资源管理器 | |
enq: KO - fast object checkpoint | 快速对象REUSAGE锁,等待对象级检查点完成一般TRUNCATE,DROP对象等出现,如果存在较多此类等待说明检查点性能出现问题 | 数据库并发、数据库IO | |
enq: KP - contention | Kupp Process Startup | datapump | |
enq: KQ - access ASM attribute | ASM Attributes Enque,Synchronization of ASM cached attributes | ASM | |
enq: KT - contention | enq: KT - contention,同步访问到当前的资源管理计划,和资源管理有关 | 资源管理器 | |
enq: MD - contention | Materialized View Log DDL ,Lock held during materialized view log DDL statements | 物化视图 | |
enq: MH - contention | AQ Notification Mail Host,Lock used for recovery when setting Mail Host for AQ e-mail notifications | AQ | |
enq: MK - contention | Master Key 锁,在enc$修改值的时候做互斥访问 | wallet | |
enq: ML - contention | AQ Notification Mail Host,Lock used for recovery when setting Mail Host for AQ e-mail notifications | AQ | |
enq: MN - contention | LogMiner 锁,同步修改LOGMINER字典数据,防止同一个LOGMINER会话中同时启动多个分析实例 | LOGMINER | |
enq: MO - contention | MMON restricted session | MMON | |
enq: MR - contention | Media Recovery锁,用于同步介质恢复和其他会话访问用户的请求 | 介质恢复 | |
enq: MR - datafile online | Media Recovery锁,用于在RAC环境中串行化数据文件离线 | 介质恢复 | |
enq: MR - standby role transition | Media Recovery锁,用于串行化并发的实例角色转换操作 | 介质恢复 | |
enq: MS - contention | Materialized View Refresh Log,Lock held during materialized view refresh to setup MV log | 物化视图 | |
enq: MV - datafile move | 高 | 数据文件移动锁等待 | 数据库IO\操作系统IO |
enq: MW - contention | 高 | Online Datafile Move,在线数据文件迁移锁 | 数据文件 |
enq: MX - sync storage server info | 当实例启动时,为了向STORAGE SERVER发送消息而加的锁,正常系统中不应该出现该锁 | ||
enq: OC - contention | Outline Cache锁,删除不适用的OUTLINES的时候对ols$profile表的同步锁 | OUTLINES | |
enq: OD - Serializing DDLs | Online DDLs ,防止同一个DDL操作被同时执行 | OUTLINES | |
enq: OL - contention | Outline Name锁,访问某个OUTLINES的时候的锁 | OUTLINES | |
enq: OQ - xsoq*histrecb | OLAPI Histories ,Synchronizes access to olapi history globals | OLAP | |
enq: OQ - xsoqhiAlloc | 同步olapi历史数据分配的访问 | ||
enq: OQ - xsoqhiClose | 同步olapi历史数据结束的访问 | ||
enq: OQ - xsoqhiFlush | 同步olapi历史数据刷新的访问 | ||
enq: OQ - xsoqhistrecb | 同步olapi历史数据参数CB的访问 | ||
enq: OT - TEXT: Generic Lock | TEXT: Generic Lock | 全文检索 | |
enq: OW - initialization | 如果会话初始化数据库Wallet,则会话将等待此事件,并且另一个会话已经开始初始化。 | wallet | |
enq: OW - termination | 如果会话尝试终止数据库Wallet,则会话将等待此事件,并且另一个会话已经开始终止。 | wallet | |
enq: PD - contention | 高 | Property Lock,阻止其他会话修改相同的属性,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | 数据库健康 |
enq: PE - contention | 参数修改锁,一般出现在alter system 或者alter session语句修改参数时 | 参数修改 | |
enq: PF - contention | 参数文件锁,用于锁定参数文件访问 | 参数文件 | |
enq: PG - contention | PG锁等待 | ||
enq: PH - contention | AQ Notification Proxy,Lock used for recovery when setting Proxy for AQ HTTP notifications | AQ | |
enq: PI - contention | Remote PX Process Spawn Status,Communicates remote Parallel Execution Server Process creation status | 远程并行查询 | |
enq: PL - contention | 传输表空间锁,同步PLUGIN表空间操作 | 传输表空间 | |
enq: PM - contention | enq: PM - contention | ||
enq: PR - contention | 高 | 进程启动同步锁,当该等待较多时说明系统中的新的会话无法正常启动或者启动缓慢。系统可能存在性能下降、CPU、内存等资源不住等现象 | 数据库性能、数据库并发 |
enq: PS - contention | PX Process Reservation,用于并行执行服务进程之间的同步 | 并行查询 | |
enq: PT - contention | ASM Partnership and Status Table,Synchronizes access to ASM PST metadata | ASM | |
enq: PV - syncshut | 等待SLAVE关闭的锁,一般是Oracle后台进程之间的同步事件 | 后台进程消息 | |
enq: PV - syncstart | 等待SLAVE启动的锁,一般是ORACLE后台进程之间的同步事件 | 后台进程消息 | |
enq: PW - flush prewarm buffers | 刷新预热池的锁等待 | 缓冲池预热 | |
enq: PW - perwarm status in dbw0 | DB CACHE预热锁,dbw0加锁并进入CACHE预热阶段 | 缓冲池预热 | |
enq: QA - contention | Row cache instance lock | ROWCACHE | |
enq: QB - contention | Row cache instance lock | ROWCACHE | |
enq: QC - contention | Row cache instance lock | ROWCACHE | |
enq: QD - contention | Row cache instance lock | ROWCACHE | |
enq: QE - contention | Row cache instance lock | ROWCACHE | |
enq: QF - contention | Row cache instance lock | ROWCACHE | |
enq: QG - contention | Row cache instance lock | ROWCACHE | |
enq: QH - contention | Row cache instance lock | ROWCACHE | |
enq: QI - contention | Row cache instance lock | ROWCACHE | |
enq: QJ - contention | Row cache instance lock | ROWCACHE | |
enq: QK - contention | Row cache instance lock | ROWCACHE | |
enq: QL - contention | Row cache instance lock | ROWCACHE | |
enq: QM - contention | Row cache instance lock | ROWCACHE | |
enq: QN - contention | Row cache instance lock | ROWCACHE | |
enq: QO - contention | Row cache instance lock | ROWCACHE | |
enq: QP - contention | Row cache instance lock | ROWCACHE | |
enq: QQ - contention | Row cache instance lock | ROWCACHE | |
enq: QR - contention | Row cache instance lock | ROWCACHE | |
enq: QS - contention | Row cache instance lock | ROWCACHE | |
enq: QT - contention | Row cache instance lock | ROWCACHE | |
enq: QU - contention | Row cache instance lock | ROWCACHE | |
enq: QV - contention | Row cache instance lock | ROWCACHE | |
enq: QX - contention | Row cache instance lock | ROWCACHE | |
enq: QY - contention | Row cache instance lock | ROWCACHE | |
enq: QZ - contention | Row cache instance lock | ROWCACHE | |
enq: RB - contention | ASM Rollback Recovery,Serializes ASM rollback recovery operations | ASM RECOVER | |
enq: RC - Result Cache: Contention | 结果缓冲锁等待 | QUERYCACHE | |
enq: RD - RAC load | RAC Load ,更新RAC LOAD数据 | ||
enq: RE - block repair contention | enq: RE - block repair contention,块修复锁冲突 | BLOCK RECOVER | |
enq: RF - atomicity | Data Guard Broker,Ensures atomicity of log transport setup | DG BROKER | |
enq: RF - DG Broker Current File ID | Data Guard Broker, Identifies which configuration metadata file is current | DG BROKER | |
enq: RF - FSFO Observer Heartbeat | Data Guard Broker,Captures recent Fast-Start Failover Observer heartbeat information | DG BROKER | |
enq: RF - FSFO Primary Shutdown suspended | Data Guard Broker,Records when FSFO Primary Shutdown is suspended | DG BROKER | |
enq: RF - new AI | Data Guard Broker,Synchronizes selection of the new apply instance | DG BROKER | |
enq: RF - RF - Database Automatic Disable | Data Guard Broker,Means for detecting when database is being automatically disabled | DG BROKER | |
enq: RF - synch: DG Broker metadata | Data Guard Broker,Ensures r/w atomicity of DG configuration metadata | DG BROKER | |
enq: RF - synchronization: aifo master | Data Guard Broker,Synchronizes apply instance failure detection and failover operation | DG BROKER | |
enq: RF - synchronization: critical ai | Data Guard Broker,Synchronizes critical apply instance among primary instances | DG BROKER | |
enq: RK - set key | wallet_set_mkey | wallet | |
enq: RL - RAC wallet lock | wallet_set_mkey | wallet | |
enq: RM - contention | RM锁等待 | ||
enq: RN - contention | Redo Log Nab Computation锁 | REDO LOG | |
enq: RO - contention | 高 | REUSAGE OBJECT锁,一般TRUNCATE,DROP对象等出现 | 数据库并发、数据库IO |
enq: RO - fast object reuse | 高 | 快速对象REUSAGE锁,一般TRUNCATE,DROP对象等出现 | 数据库并发、数据库IO |
enq: RP - contention | 高 | Resilver / Repair锁,当数据块需要重新同步或者从镜像中修复的时候会加锁。如果该锁经常出现或者阻塞了一些会话,则说明数据库的文件出现了问题 | 数据库健康、数据库IO |
enq: RR - contention | Workload Capture and Replay | ||
enq: RS - file delete | 可回收空间锁,锁定以防止在空间回收期间访问文件 | 空间回收 | |
enq: RS - persist alert level | 可回收空间锁,保持锁定以使警报级别持久 | 空间回收 | |
enq: RS - prevent aging list update | 可回收空间锁,锁定以防止老化列表更新 | 空间回收 | |
enq: RS - prevent file delete | 可回收空间锁,锁定以防止删除文件以回收空间 | 空间回收 | |
enq: RS - read alert level | 可回收空间锁,用于读取ALERT LEVEL | 空间回收 | |
enq: RS - record reuse | 可回收空间锁,锁定以防止文件在重用循环记录时访问 | 空间回收 | |
enq: RS - write alert level | 可回收空间锁,用于写入ALERT LEVEL | 空间回收 | |
enq: RT - contention | Redo Thread锁,当MOUNT/OPEN数据库实例时,LGWR, DBW0, RVWR 加该锁 | REDO THREAD | |
enq: RT - thread internal enable/disable | Redo Thread锁,当thread enable/disable的时候CKPT加该锁,如果生产系统出现该锁,说明有人在操作thread,应该予以警告 | REDO THREAD | |
enq: RU - contention | Rolling Migration滚动升级时出现,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | 数据库健康 | |
enq: RU - waiting | Rolling Migration滚动升级时出现,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | 数据库健康 | |
enq: RW - MV metadata contention | Materialized View Flags锁, CREATE/ALTER/DROP物化视图时候的锁 | 物化视图 | |
enq: RX - relocate extent | ASM Extent Relocation Lock,Synchronizes relocating ASM extents | ASM EXTENT | |
enq: RX - unlock extent | ASM Extent Relocation Lock,Synchronizes unlocking ASM extents | ASM EXTENT | |
enq: SB - logical standby metadata | LogicalStandby锁,同步逻辑STANDBY元数据操作 | 逻辑STANDBY | |
enq: SB - table instantiation | LogicalStandby锁,同步表的instantiation 和 EDS操作 | 逻辑STANDBY | |
enq: SC - contention | enq: SC - contention | ||
enq: SE - contention | Session Migration,同步透明会话迁移操作 | ||
enq: SF - contention | AQ Notification Sender,Lock used for recovery when setting Sender for AQ e-mail notifications | AQ | |
enq: SH - contention | 高 | Active Session History Flushing,一般看不到该锁,因为该锁是不需要等待的(NOWAIT) | ASH |
enq: SI - contention | Streams Table Instantiation ,Prevents multiple streams tabel instantiations | STREAMS | |
enq: SJ - Slave Task Cancel | KTSJ Slave Task Cancel锁,串行化 | ||
enq: SK - contention | Shrink Segment锁,串行化shrink操作 | shrink | |
enq: SL - escalate lock | Serialize Lock request,sending lock escalate to LCK0 | LCK0 | |
enq: SL - get lock | Serialize Lock request,sending lock req to LCK0 | LCK0 | |
enq: SL - get lock for undo | Serialize Lock request,sending lock req for undo to LCK0 | LCK0 | |
enq: SM - contention | enq: SM - contention | ||
enq: SO - contention | Shared Object,Synchronizes access to Shared Object (PL/SQL Shared Object Manager) | PL/SQL | |
enq: SP - contention 1 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: SP - contention 2 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: SP - contention 3 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: SP - contention 4 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: SQ - contention | SEQUENCE$数据字典锁,当序列号申请较为频繁并且SQEUENCE的参数设置不合理的时候,容易引起该等待 | SEQUENCE | |
enq: SR - contention | Synchronized Replication,Coordinates replication / streams operations | streams、同步复制 | |
enq: SS - contention | 高 | 等待排序段(SORT SEGMENT),如果此类等待较多,可能的原因是临时表空间不足或者SMON由于处理其他事务而没有及时清理临时段 | Top SQL-物理读、Top SQL-逻辑读 |
enq: ST - contention | 高 | 同步空间管理锁,该锁出现在字典管理表空间的场景下,使用本地管理表空间不会出现该锁等待。 | 空间管理 |
enq: SU - contention | SaveUndo Segment锁,串行化SaveUndo Segment | ||
enq: SV - contention | 高 | SEQUENCE锁,当带有CACHE的SEQUENCE在多实例环境中使用时会遇到该锁。如果该锁经常出现或者经常阻塞会话,则说明有SEQUENCE的性能存在问题。可以通过加大CACHE,使用NOORDER参数等方式提升相关性能 | SEQUENCE\数据库性能 |
enq: SW - contention | 高 | alter system suspend'命令导致系统写IO挂起,如果长时间出现该锁或者大量进程等待该锁,则系统可能被人为命令挂起,如果是非正常操作,需要尽快解除 | SUSPENDIO\数据库健康 |
enq: SX - contention 5 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: SX - contention 6 | 打ONE-OFF补丁时的锁,正常系统不应该出现 | OPATH | |
enq: TA - contention | Instance Undo锁,串行化UNDO段和UNDO表空间上的操作 | UNDO SEGMENT | |
enq: TB - SQL Tuning Base Cache Load | SQL Tuning Base Existence Cache,Synchronizes writes to the SQL Tuning Base Existence Cache | SQL TUNING | |
enq: TB - SQL Tuning Base Cache Update | SQL Tuning Base Existence Cache,Synchronizes writes to the SQL Tuning Base Existence Cache | SQL TUNING | |
enq: TC - contention | TABLESPACE CHECKPOINT锁,用于串行化表空间CHECKPOINT操作,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | CHECKPOINT/TABLESPACE | |
enq: TC - contention2 | TABLESPACE CHECKPOINT锁,用于串行化表空间CHECKPOINT操作,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | CHECKPOINT/TABLESPACE | |
enq: TD - KTF dump entries | KTF map table enqueue锁,KTF dumping time/scn mappings in SMON_SCN_TIME table | SCN | |
enq: TE - KTF broadcast | KTF broadcast 锁,KTF广播 | ||
enq: TF - contention | 临时文件锁,串行化临时文件删除 | 临时文件 | |
enq: TH - metric threshold evaluation | Threshold Chain,Serializes threshold in-memory chain access | ||
enq: TK - Auto Task Serialization | Auto Task Serialization | ||
enq: TK - Auto Task Slave Lockout | Auto Task Serialization | ||
enq: TL - contention | Log Lock,Serializes threshold log table read and update | ||
enq: TM - contention | 表锁冲突等待,此类等待是由于有应用或者维护人员锁定了某张表。可以通过BLOCKING工具查找锁持有者,如果长期有会话持有该锁不释放,可以通过杀掉持有者进行强行解锁 | 表锁、数据库并发 | |
enq: TO - contention | Temp Object,同步对一个临时对象的操作,包括DML和DDL | 临时对象 | |
enq: TP - contention | Runtime Fixed Table Purge锁,系统动态调整或者清除FIXED TABLE的同步锁。FIXED TABLE是oracle的一些内存表 | X$表操作 | |
enq: TQ - DDL contention | Queue table enqueue,TM access to the queue table | AQ/STREAMS | |
enq: TQ - DDL-INI contention | Queue table enqueue,TM access to the queue table | AQ/STREAMS | |
enq: TQ - INI contention | Queue table enqueue,TM access to the queue table | AQ/STREAMS | |
enq: TQ - TM contention | Queue table enqueue,TM access to the queue table | AQ/STREAMS | |
enq: TS - contention | 临时段锁,串行化访问临时段 | 临时段,排序 | |
enq: TT - contention | 表空间锁,串行化表空间的操作 | 表空间 | |
enq: TW - contention | enq: TW - contention | ||
enq: TX - allocate ITL entry | 高 | 行锁等待,等待位置为ITL(事务槽),如果某个并发修改较大的表的initrans参数设置不足会导致该等待较多。加大表的INITRANS参数有助于改善该等待(修改参数只会影响新的数据块,老数据块需要通过段重建等才会起作用) | 事务锁、数据库并发/表参数 |
enq: TX - contention | 事务锁 | ||
enq: TX - index contention | 高 | 在索引中插入行的事务必须等待另一个事务完成索引块的拆分的结束时发生。 | 事务锁/索引碎片 |
enq: TX - row lock contention | 以下有几种情况会发生行锁等待。如果一个用户想要更新或删除另一个会话正在修改的一行或多行数据时, 持有锁的会话将在执行COMMIT或ROLLBACK时释放它。 | 并发事务 | |
enq: UL - contention | 用户定义锁等待 | ||
enq: US - contention | Undo Segment锁,在UNDO上做DDL操作的锁,如果该锁经常出现或者阻塞了大量会话说明UNDO配置或者性能存在问题,会影响数据库的健康 | 数据库健康、数据库性能、UNDO | |
enq: WA - contention | AQ Notification Watermark,Lock used for recovery when setting Watermark for memory usage in AQ notifications | AQ/STREAMS | |
enq: WF - contention | AWR Flush,刷新AWR数据到数据字典表 | AWR | |
enq: WG - delete fso | Write gather local enqueue,acquire lobid local enqueue when deleting fso | LOB操作 | |
enq: WG - lock fso | enq: WG - lock fso,和LOB,SECUREFILE有关的锁等待 | LOB操作 | |
enq: WL - contention | 高 | Being Written Redo Log锁,同步redo log 文件和归档日志文件的访问, 如果该锁经常出现,并且阻塞了大量会话,说明系统存在异常,REDO方面出现了问题 | 数据库健康、数据库性能、REDO LOG |
enq: WL - RAC-wide SGA contention | 高 | Being Written Redo Log锁,krsh_rsga类操作在RAC范围内访问SGA的锁,如果该锁经常出现或者阻塞了大量的会话,说明系统存在异常 | 数据库健康、数据库性能 |
enq: WL - RFS global state contention | Being Written Redo Log锁,串行化访问RFS全局状态的锁,如果该锁经常出现,并且阻塞了大量会话,说明系统存在异常 | 数据库健康,REDO LOG | |
enq: WL - Test access/locking | 高 | Being Written Redo Log锁,测试REDO传输所加的锁,如果该锁经常出现或者阻塞了大量的会话,说明系统存在异常,需要排查是否有人在做类似操作 | REDO LOG |
enq: WM - WLM Plan activation | enq: WM - WLM Plan activation | ||
enq: WP - contention | AWR Purge,AWR PURGE或者BASELINE的时候防止并发执行的锁 | AWR | |
enq: WR - contention | 高 | LNS archiving log锁,同步Async LNS 和 ARCH/前台进程对REDO LOG的访问,如果该锁经常出现,并且阻塞了大量会话,说明系统存在异常,REDO方面出现了问题 | 数据库健康、数据库性能、REDO LOG |
enq: WS - contention | enq: WS - contention,和DG日志传输有关的锁 | DG | |
enq: XB - contention | enq: XB - contention | ||
enq: XC - XDB Configuration | AWR CDB-Wide Table Lock,This enqueue serializes CDB-Wide Operation on AWR Table | AWR | |
enq: XH - contention | AQ Notification No-Proxy,Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications | AQ/STREAMS | |
enq: XL - fault extent map | 高 | ASM Extent Fault Lock, Keep multiple processes from faulting in the same extent chunk | ASM |
enq: XQ - purification | 高 | ASM Extent Relocation Enqueue,wait for relocation before doing block purification | ASM |
enq: XQ - recovery | 高 | ASM Extent Relocation Enqueue,prevent relocation during _recovery_asserts checking | ASM |
enq: XQ - relocation | 高 | ASM Extent Relocation Enqueue,wait for recovery before doing relocation | ASM |
enq: XR - database force logging | Quiesce / Force Logging 锁,设置数据库强制日志 | ||
enq: XR - quiesce database | Quiesce / Force Logging 锁,使数据库进入暂停状态 | ||
enq: XY - contention | 高 | oracle内部测试使用的锁,正常系统不应该出现,如果该锁长时间出现或者大量出现,说明数据库健康问题已经较为严重或者遇到某个BUG | 数据库健康 |
enq: ZA - add std audit table partition | Audit Partition,lock held to add partition to std audit table | AUDIT | |
enq: ZF - add fga audit table partition | FGA Partition, lock held to add partition to fga audit table | FGA | |
enq: ZG - contention | enq: ZG - contention,File Group锁,同步FILE GROUP操作 | ||
enq: ZH - compression analysis | Compression Analyzer,分析并将结果插入compress$字典表,防止对同一张表做相同操作 | COMPRESS | |
enq: ZX - repopulation file write | 高 | 迁移时同步文件操作,正常运行阶段如果经常出现或者大量出现则说明系统健康存在问题 | 数据库健康 |
enq: ZZ - update hash tables | enq: ZZ - update hash tables | HASH表相关 | |
enq: ZZ - update hash tables | enq: ZZ - update hash tables | HASH表相关 |