Venzi's Tech-Blog

4 December 2013

Oracle 12c background processes

Filed under: Oracle — Venzi @ 19:48

Here is yet another Oracle 12c post to add to all those out there and this one starts with a question: How many background processes exist in an Oracle DB environment to date? Just take a moment and take a guess, if you haven’t already seen the bold number coming up. The answer is: 108! That is with all the available bells and whistles turned on but it gives you an idea of how much engineering has gone into the Oracle Database over all those 30+ years. In Oracle 12c there is a total of 19 new background processes meaning that if you are running in an 11gR2 environment you will only have 92. Wait, 92? 108 – 19 = 89 and not 92. Those numbers don’t add up so what happened? It is one of those questions I get these days when talking about 12c. Although you can’t change it anyway people want to know what has changed between the versions and I guess: Fair enough. So there you have it, the reason for this post: To share my little research and perhaps answer the same question from one or two people out there before it reaches me via other channels. Or worst case, a link that I can copy/paste rather than having to type this all over again.

Right, so as I’ve mentioned you have a total of 108 background processes in Oracle 12c. You can find the entire list at the bottom of this post or you can have a look at the documentation (always recommended! There are far too many people out there that don’t even bother looking at the documentation anymore).

So, which ones are brand new in 12c:

Name Expanded Name Short Description Long Description External Properties
AQPC AQ Process Coordinator Per instance AQ global coordinator AQPC is responsible for performing administrative tasks for AQ Master Class Processes including commands like starting, stopping, and other administrative tasks. This process is automatically started on instance startup. Database instances Advanced Queueing
ARSn ASM Recovery Slave Process Recovers ASM transactional operations The ASM RBAL background process coordinates and spawns one or more of these slave processes to recover aborted ASM transactional operations. These processes run only in the Oracle ASM instance.Possible processes are ARS0-ARS9. Oracle ASM instances
BWnn Database Writer Process Writes modified blocks from the database buffer cache to the data files See the Long Description for the DBWn process in this table for more information about the BWnn process. Database instances
FENC Fence Monitor Process Processes fence requests for RDBMS instances which are using Oracle ASM instances CSS monitors RDBMS instances which are connected to the Oracle ASM instance and constantly doing I/Os. When the RDBMS instance terminates due to a failure, all the outstanding I/O’s from the RDBMS instance should be drained and any new I/O’s rejected. FENC receives and processes the fence request from CSSD. Oracle ASM instances
IPC0 IPC Service Background Process Common background server for basic messaging and RDMA primitives based on IPC (Inter-process communication) methods. IPC0 handles very high rates of incoming connect requests, as well as, completing reconfigurations to support basic messaging and RDMA primitives over several transports such as UDP, RDS, InfiniBand and RC. Oracle RAC
LDDn Global Enqueue Service Daemon Helper Slave Helps the LMDn processes with various tasks LDDn processes are slave processes spawned on demand by LMDn processes. They are spawned to help the dedicated LMDn processes with various tasks when certain workloads start creating performance bottlenecks. These slave processes are transient as they are started on demand and they can be shutdown when no longer needed. There can be up to 36 of these slave processes (LDD0-LDDz). Database instances, Oracle ASM instances, Oracle RAC
LGnn Log Writer Worker Writes redo log On multiprocessor systems, LGWR creates worker processes to improve the performance of writing to the redo log. LGWR workers are not used when there is a SYNC standby destination. Possible processes include LG00-LG99. Database instances
LREG Listener Registration Process Registers the instance with the listeners LREG notifies the listeners about instances, services, handlers, and endpoint. Database instances, Oracle ASM instances, Oracle RAC
OFSD Oracle File Server Background Process Serves file system requests submitted to an Oracle instance This background process listens for new file system requests, both management (like mount, unmount, and export) and I/O requests, and executes them using Oracle threads. Database instances, Oracle RAC
QMnn AQ Master Class Process Per instance per AQ Master Class Process Each of this type of process represents a single class of work item such as AQ notification, queue monitors, and cross process. Database instances Advanced Queueing
RM RAT Masking Slave Process Extracts and masks bind values from workloads like SQL tuning sets and DB Replay capture files This background process is used with Data Masking and Real Application Testing. Database instances
RMON Rolling Migration Monitor Process Manages the rolling migration procedure for an Oracle ASM cluster The RMON process is spawned on demand to run the protocol for transitioning an ASM cluster in and out of rolling migration mode. Oracle ASM instance, Oracle RAC
RPOP Instant Recovery Repopulation Daemon Responsible for re-creating and/or repopulating data files from snapshot files and backup files The RPOP process is responsible for re-creating and repopulating data files from snapshots files. It works with the instant recovery feature to ensure immediate data file access. The local instance has immediate access to the remote snapshot file’s data, while repopulation of the recovered primary data files happens concurrently. Any changes in the data are managed between the instance’s DBW processes and RPOP to ensure the latest copy of the data is returned to the user. Database instances
SAnn SGA Allocator Allocates SGA A small fraction of SGA is allocated during instance startup. The SAnn process allocates the rest of SGA in small chunks. The process exits upon completion of SGA allocation.The possible processes are SA00 – SAzz. Database instances
SCCn ASM Disk Scrubbing Slave Check Process Performs Oracle ASM disk scrubbing check operation SCCn acts as a slave process for SCRB and performs the checking operations. The possible processes are SCC0-SCC9. Oracle ASM instances
SCRB ASM Disk Scrubbing Master Process Coordinates Oracle ASM disk scrubbing operations SCRB runs in an Oracle ASM instance and coordinates Oracle ASM disk scrubbing operations. Oracle ASM instances
SCRn ASM Disk Scrubbing Slave Repair Process Performs Oracle ASM disk scrubbing repair operation SCRn acts as a slave process for SCRB and performs the repairing operations. The possible processes are SCR0-SCR9. Oracle ASM instances
SCVn ASM Disk Scrubbing Slave Verify Process Performs Oracle ASM disk scrubbing verify operation SCVn acts as a slave process for SCRB and performs the verifying operations. The possible processes are SCV0-SCV9. Oracle ASM instances
VUBG Volume drive Umbilicus Background Relays messages between Oracle ASM instance and Oracle ASM Proxy instance that is used by ADVM (for ACFS) Oracle ASM instances, Oracle ASM Proxy instances

But that is not all. One has actually been renamed all together and two have been enabled for multi-processing indicated by the “n” at the back of the name in the list below:

Name (12c) Previous name (11gR2) Expanded Name Short Description Long Description External Properties
LCKn LCK0 Lock Process Manages global enqueue requests and cross-instance broadcasts The process handles all requests for resources other than data blocks. For examples, LCKn manages library and row cache requests. Possible processes are LCK0 and LCK1. Database instances, Oracle ASM instances, Oracle RAC
LMDn LMD0 Global Enqueue Service Daemon Process Manages incoming remote resource requests from other instances LMDn processes enqueue resources managed under Global Enqueue Service. In particular, they process incoming enqueue request messages and control access to global enqueues. They also perform distributed deadlock detections. There can be up to 36 of these processes (LMD0-LMDz). Database instances, Oracle ASM instances, Oracle RAC
TTnn NSAn Redo Transport Slave Process Ships redo from current online and standby redo logs to remote standby destinations configured for ASYNC transport TTnn can run as multiple processes, where nn is 00 to ZZ.See Also: Oracle Data Guard Concepts and Administration Database instances, Data Guard

And last but not least, three have been made obsolete in 12c:

Name Expanded Name Short Description Long Description External Properties
DRnn ASM Disk Resynchronization Slave Process Resynchronizes the contents of an offline disk When a disk online SQL command is issued on a disk or disks that are offline, ASM spawns DRnn. Depending on the load, more than one slave may be spawned. ASM Instance
FDnn Oracle ASM Stale FD Cleanup Slave Process Cleans up Oracle ASM stale FDs on foreground processes This process cleans up Oracle ASM stale FDs on foreground processes if an Oracle ASM disk is globally closed. Database and ASM instances
Vnnn ASM Volume I/O Slave Process Initializes ASM volume contents during creation This process is responsible for initializing the ASM volume during creation. ASM instance

The full list of background processes in 12c to date:

Name Expanded Name Short Description Long Description External Properties
ABMR Auto BMR Background Process Coordinates execution of tasks such as filtering duplicate block media recovery requests and performing flood control When a process submits a block media recovery request to ABMR, it dynamically spawns slave processes (BMRn) to perform the recovery. ABMR and BMRn terminate after being idle for a long time. Database instances
ACFS ASM Cluster File System CSS Process Tracks the cluster membership in CSS and informs the file system driver of membership changes ACFS delivers CSS membership changes to the Oracle cluster file system. These membership changes are required for the file system to maintain file system consistency within the cluster. Oracle ASM instances, Oracle RAC
ACMS Atomic Control File to Memory Service Process Coordinates consistent updates to a control file resource with its SGA counterpart on all instances in an Oracle RAC environment The ACMS process works with a coordinating caller to ensure that an operation is executed on every instance in Oracle RAC despite failures. ACMS is the process in which a distributed operation is called. As a result, this process can exhibit a variety of behaviors. In general, ACMS is limited to small, nonblocking state changes for a limited set of cross-instance operations. Database instances, Oracle RAC
APnn Database Apply Process Coordinator Process Obtains transactions from the reader server and passes them to apply servers The coordinator process name is APnn, where nn can include letters and numbers.For more information about the coordinator process, see V$STREAMS_APPLY_COORDINATOR for Oracle Streams, V$XSTREAM_APPLY_COORDINATOR for XStream, and V$GG_APPLY_COORDINATOR for Oracle GoldenGate. Database instances, Logical Standby, Streams Apply, XStream Inbound servers, XStream Outbound servers, GoldenGate Integrated Replicat
AQPC AQ Process Coordinator Per instance AQ global coordinator AQPC is responsible for performing administrative tasks for AQ Master Class Processes including commands like starting, stopping, and other administrative tasks. This process is automatically started on instance startup. Database instances Advanced Queueing
ARBn ASM Rebalance Process Rebalances data extents within an ASM disk group Possible processes are ARB0-ARB9 and ARBA. Oracle ASM instances
ARCn Archiver Process Copies the redo log files to archival storage when they are full or an online redo log switch occurs ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled, in which case ARCn automatically archives online redo log files. LGWR cannot reuse and overwrite an online redo log group until it has been archived.The database starts multiple archiver processes as needed to ensure that the archiving of filled online redo logs does not fall behind. Possible processes include ARC0-ARC9 and ARCa-ARCt.The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARCn processes that the database initially invokes. Database instances
ARSn ASM Recovery Slave Process Recovers ASM transactional operations The ASM RBAL background process coordinates and spawns one or more of these slave processes to recover aborted ASM transactional operations. These processes run only in the Oracle ASM instance.Possible processes are ARS0-ARS9. Oracle ASM instances
ASMB ASM Background Process Communicates with the ASM instance, managing storage and providing statistics ASMB runs in Oracle ASM instances when the ASMCMD cp command runs or when the database instance first starts if the server parameter file is stored in Oracle ASM. ASMB also runs with Oracle Cluster Registry on Oracle ASM. Database instances, Oracle ASM instances
ASnn Database Apply Reader or Apply Server
  • Computes dependencies between logical change records (LCRs) and assembles messages into transactions (Reader Server)
  • Applies LCRs to database objects or passes LCRs and user messages to their appropriate apply handlers (Apply Server)
When the reader server finishes computing dependencies between LCRs and assembling transactions, it returns the assembled transactions to the coordinator process. Query V$STREAMS_APPLY_READER, V$XSTREAM_APPLY_READER, and V$GG_APPLY_READER for information about the reader server background process.An apply server receives the transactions from the coordinator background process, and either applies database changes in LCRs or sends LCRs or messages to apply handlers. Apply servers can also enqueue a queue. If an apply server encounters an error, then it then tries to resolve the error with a user-specified conflict handler or error handler. If an apply server cannot resolve an error, then it rolls back the transaction and places the entire transaction, including all of its messages, in the error queue. When an apply server commits a completed transaction, this transaction has been applied. When an apply server places a transaction in the error queue and commits, this transaction also has been applied. Query V$STREAMS_APPLY_SERVER for information about the apply server background process. For XStream Inbound servers, query V$XSTREAM_APPLY_SERVER. For GoldenGate Integrated Replicat, query V$GG_APPLY_SERVER.The coordinator process name is ASnn, where nn can include letters and numbers. Database instances, XStream Outbound servers, XStream Inbound servers, GoldenGate Integrated Replicat
BMRn Automatic Block Media Recovery Slave Pool Process Fetches blocks from a real-time readable standby database When a process submits a block media recovery request to ABMR, it dynamically spawns slave processes (BMRn) to perform the recovery. BMRn processes fetch blocks from a real-time readable standby database. ABMR and BMRn terminate after being idle for a long time. Database instances
Bnnn ASM Blocking Slave Process for GMON Performs maintenance actions on Oracle ASM disk groups Bnnn performs actions that require waiting for resources on behalf of GMON. GMON must be highly available and cannot wait.A Bnnn slave is spawned when a disk is taken offline in an Oracle ASM disk group. Offline timer processing and drop of the disk are performed in this slave. Up to five process (B000 to B004) can exist depending on the load. Oracle ASM instances
BWnn Database Writer Process Writes modified blocks from the database buffer cache to the data files See the Long Description for the DBWn process in this table for more information about the BWnn process. Database instances
CJQ0 Job Queue Coordinator Process Selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs CJQ0 is automatically started and stopped as needed by Oracle Scheduler.The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. CJQ0 starts only as many job queue processes as required by the number of jobs to run and available resources. Database instances
CKPT Checkpoint Process Signals DBWn at checkpoints and updates all the data files and control files of the database to indicate the most recent checkpoint At specific times CKPT starts a checkpoint request by messaging DBWn to begin writing dirty buffers. On completion of individual checkpoint requests, CKPT updates data file headers and control files to record most recent checkpoint.CKPT checks every three seconds to see whether the amount of memory exceeds the value of the PGA_AGGREGATE_LIMIT initialization parameter, and if so, takes the action described in “PGA_AGGREGATE_LIMIT”. Database instances, Oracle ASM instances
CPnn Database Capture Process Captures database changes from the redo log by using the infrastructure of LogMiner The capture process name is CPnn, where nn can include letters and numbers. The underlying LogMiner process name is MSnn, where nn can include letters and numbers. The capture process includes one reader server that reads the redo log and divides it into regions, one or more preparer servers that scan the redo log, and one builder server that merges redo records from the preparer servers. Each reader server, preparer server, and builder server is a process. Query the V$STREAMS_CAPTURE, V$XSTREAM_CAPTURE, and V$GOLDENGATE_CAPTURE view for information about this background process. Database instances, XStream Outbound Servers, Oracle Streams
CSnn I/O Calibration Process Issues I/Os to storage as part of storage calibration. CSnn slave processes are started on execution of the DBMS_RESOURCE_MANAGER.CALIBRATE_IO() procedure. There is one slave process per CPU on each node of the database. Database instances, Oracle RAC
CTWR Change Tracking Writer Process Tracks changed data blocks as part of the Recovery Manager block change tracking feature CTWR tracks changed blocks as redo is generated at a primary database and as redo is applied at a standby database. The process is slightly different depending on the type of database. Database instances
CXnn Streams Propagation Sender Process Sends LCRs to a propagation receiver The propagation sender process name is CXnn, where nn can include letters and numbers. In an Oracle Streams combined capture and apply optimization, the propagation sender sends LCRs directly to the propagation receiver to improve performance. The propagation receiver passes the LCRs to an apply process. Query V$PROPAGATION_SENDER for information about a propagation sender. Database instances, XStream Outbound Server, Oracle Streams
DBRM Database Resource Manager Process Sets resource plans and performs other tasks related to the Database Resource Manager If a resource plan is not enabled, then this process is idle. Database instances
DBWn Database Writer Process Writes modified blocks from the database buffer cache to the data files The primary responsibility of the Database Writer Process is to write data blocks to disk. It also handles checkpoints, file open synchronization, and logging of Block Written records.In many cases the blocks that the Database Writer Process writes are scattered throughout the disk. Thus, the writes tend to be slower than the sequential writes performed by LGWR. The Database Writer Process performs multiblock writes when possible to improve efficiency. The number of blocks written in a multiblock write varies by operating system.The DB_WRITER_PROCESSES initialization parameter specifies the number of Database Writer Processes. There can be 1 to 100 Database Writer Processes. The names of the first 36 Database Writer Processes are DBW0-DBW9 and DBWa-DBWz. The names of the 37th through 100th Database Writer Processes are BW36-BW99. The database selects an appropriate default setting for the DB_WRITER_PROCESSES parameter or adjusts a user-specified setting based on the number of CPUs and processor groups. Database instances
DIA0 Diagnostic Process Detects and resolves hangs and deadlocks Database instances, Oracle ASM instances
DIAG Diagnostic Capture Process Performs diagnostic dumps DIAG performs diagnostic dumps requested by other processes and dumps triggered by process or instance termination. In Oracle RAC, DIAG performs global diagnostic dumps requested by remote instances. Database instances, Oracle ASM instances
DMnn Data Pump Master Process Coordinates the Data Pump job tasks performed by Data Pump worker processes and handles client interactions The Data Pump master (control) process is started during job creation and coordinates all tasks performed by the Data Pump job. It handles all client interactions and communication, establishes all job contexts, and coordinates all worker process activities on behalf of the job. Database instances, Data Pump
DMON Data Guard Broker Monitor Process Manages and monitors a database that is part of a Data Guard broker configuration When you start the Data Guard broker, a DMON process is created. DMON runs for every database instance that is managed by the broker. DMON interacts with the local database and the DMON processes of the other databases to perform the requested function. DMON also monitors the health of the broker configuration and ensures that every database has a consistent description of the configuration.DMON maintains profiles about all database objects in the broker configuration in a binary configuration file. A copy of this file is maintained by the DMON process for each of the databases that belong to the broker configuration. The process is created when the DG_BROKER_START initialization parameter is set to true. Database instances, Data Guard
Dnnn Dispatcher Process Performs network communication in the shared server architecture In the shared server architecture, clients connect to a dispatcher process, which creates a virtual circuit for each connection. When the client sends data to the server, the dispatcher receives the data into the virtual circuit and places the active circuit on the common queue to be picked up by an idle shared server. The shared server then reads the data from the virtual circuit and performs the database work necessary to complete the request. When the shared server must send data to the client, the server writes the data back into the virtual circuit and the dispatcher sends the data to the client. After the shared server completes the client request, the server releases the virtual circuit back to the dispatcher and is free to handle other clients.Several initialization parameters relate to shared servers. The principal parameters are: DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, LOCAL_LISTENER, REMOTE_LISTENER. Database instances, shared servers
DSKM Slave Diskmon Process Acts as the conduit between the database, Oracle ASM instances, and the Master Diskmon daemon to communicate information to Exadata storage This process is active only if Exadata Storage is used. DSKM performs operations related to Exadata I/O fencing and Exadata cell failure handling. Oracle ASM instances, Exadata
DWnn Data Pump Worker Process Performs Data Pump tasks as assigned by the Data Pump master process The Data Pump worker process is responsible for performing tasks that are assigned by the Data Pump master process, such as the loading and unloading of metadata and data. Database instances
EMNC EMON Coordinator Process Coordinates database event management and notifications EMNC is a master background process that coordinates event management and notification activity in the database, including Streams Event Notifications, Continuous Query Notifications, and Fast Application Notifications. Database instances
Ennn EMON Slave Process Performs database event management and notifications The database event management and notification load is distributed among the EMON slave processes. These processes work on the system notifications in parallel, offering a capability to process a larger volume of notifications, a faster response time, and a lower shared memory use for staging notifications. Database instances
FBDA Flashback Data Archiver Process Archives historical rows for tracked tables into flashback data archives and manages archive space, organization, and retention When a transaction that modifies a tracked table commits, FBDA stores the pre-image of the rows in the archive. FDBA maintains metadata on the current rows and tracks how much data has been archived.FBDA is also responsible for automatically managing the flashback data archive for space, organization (partitioning tablespaces), and retention. FBDA also keeps track of how far the archiving of tracked transactions has progressed. Database instances
FENC Fence Monitor Process Processes fence requests for RDBMS instances which are using Oracle ASM instances CSS monitors RDBMS instances which are connected to the Oracle ASM instance and constantly doing I/Os. When the RDBMS instance terminates due to a failure, all the outstanding I/O’s from the RDBMS instance should be drained and any new I/O’s rejected. FENC receives and processes the fence request from CSSD. Oracle ASM instances
FMON File Mapping Monitor Process Manages mapping information for the Oracle Database file mapping interface The DBMS_STORAGE_MAP package enables you to control the mapping operations. When instructed by the user, FMON builds mapping information and stores it in the SGA, refreshes the information when a change occurs, saves the information to the data dictionary, and restores it to the SGA at instance startup.FMON is started by the database whenever the FILE_MAPPING initialization parameter is set to true. Database instances, Oracle ASM instances
FSFP Data Guard Broker Fast Start Failover Pinger Process Maintains fast-start failover state between the primary and target standby databases FSFP is created when fast-start failover is enabled. Database instances, Data Guard
GCRn Global Conflict Resolution Slave Process Performs synchronous tasks on behalf of LMHB GCRn processes are transient slaves that are started and stopped as required by LMHB to perform synchronous or resource intensive tasks. Database instances, Oracle ASM instances, Oracle RAC
GEN0 General Task Execution Process Performs required tasks including SQL and DML Database instances, Oracle ASM instances, Oracle ASM Proxy instances
GMON ASM Disk Group Monitor Process Monitors all mounted Oracle ASM disk groups GMON monitors all the disk groups mounted in an Oracle ASM instance and is responsible for maintaining consistent disk membership and status information. Membership changes result from adding and dropping disks, whereas disk status changes result from taking disks offline or bringing them online. Oracle ASM instances
GTXn Global Transaction Process Provides transparent support for XA global transactions in an Oracle RAC environment These processes help maintain the global information about XA global transactions throughout the cluster. Also, the processes help perform two-phase commit for global transactions anywhere in the cluster so that an Oracle RAC database behaves as a single system to the externally coordinated distributed transactions.The GLOBAL_TXN_PROCESSES initialization parameter specifies the number of GTXn processes, where n is 0-9 or a-j. The database automatically tunes the number of these processes based on the workload of XA global transactions. You can disable these processes by setting the parameter to 0. If you try to run XA global transactions with these processes disabled, an error is returned. Database instances, Oracle RAC
Innn Disk and Tape I/O Slave Process Serves as an I/O slave process spawned on behalf of DBWR, LGWR, or an RMAN backup session I/O slave process can be configured on platforms where asynchronous I/O support is not available. These slaves are started by setting the corresponding slave enable parameter in the server parameter file. The I/O slaves simulate the asynchronous I/O behavior when the underlying platform does not have native support for asynchronous I/O. Database instances
INSV Data Guard Broker Instance Slave Process Performs Data Guard broker communication among instances in an Oracle RAC environment INSV is created when the DG_BROKER_START initialization parameter is set to true. Database instances, Data Guard
IPC0 IPC Service Background Process Common background server for basic messaging and RDMA primitives based on IPC (Inter-process communication) methods. IPC0 handles very high rates of incoming connect requests, as well as, completing reconfigurations to support basic messaging and RDMA primitives over several transports such as UDP, RDS, InfiniBand and RC. Oracle RAC
Jnnn Job Queue Slave Process Executes jobs assigned by the job coordinator Job slave processes are created or awakened by the job coordinator when it is time for a job to be executed.Job slaves gather all the metadata required to run the job from the data dictionary. The slave processes start a database session as the owner of the job, execute triggers, and then execute the job. After the job is complete, the slave processes commit and then execute appropriate triggers and close the session. The slave can repeat this operation in case additional jobs need to be run. Database instances
LCKn Lock Process Manages global enqueue requests and cross-instance broadcasts The process handles all requests for resources other than data blocks. For examples, LCKn manages library and row cache requests. Possible processes are LCK0 and LCK1. Database instances, Oracle ASM instances, Oracle RAC
LDDn Global Enqueue Service Daemon Helper Slave Helps the LMDn processes with various tasks LDDn processes are slave processes spawned on demand by LMDn processes. They are spawned to help the dedicated LMDn processes with various tasks when certain workloads start creating performance bottlenecks. These slave processes are transient as they are started on demand and they can be shutdown when no longer needed. There can be up to 36 of these slave processes (LDD0-LDDz). Database instances, Oracle ASM instances, Oracle RAC
LGnn Log Writer Worker Writes redo log On multiprocessor systems, LGWR creates worker processes to improve the performance of writing to the redo log. LGWR workers are not used when there is a SYNC standby destination. Possible processes include LG00-LG99. Database instances
LGWR Log Writer Process Writes redo entries to the online redo log Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files. Database instances, Oracle ASM instances
LMDn Global Enqueue Service Daemon Process Manages incoming remote resource requests from other instances LMDn processes enqueue resources managed under Global Enqueue Service. In particular, they process incoming enqueue request messages and control access to global enqueues. They also perform distributed deadlock detections. There can be up to 36 of these processes (LMD0-LMDz). Database instances, Oracle ASM instances, Oracle RAC
LMHB Global Cache/Enqueue Service Heartbeat Monitor Monitor the heartbeat of LMON, LMD, and LMSn processes LMHB monitors LMON, LMD, and LMSn processes to ensure they are running normally without blocking or spinning. Database instances, Oracle ASM instances, Oracle RAC
LMON Global Enqueue Service Monitor Process Monitors an Oracle RAC cluster to manage global resources LMON maintains instance membership within Oracle RAC. The process detects instance transitions and performs reconfiguration of GES and GCS resources. Database instances, Oracle ASM instances, Oracle RAC
LMSn Global Cache Service Process Manages resources and provides resource control among Oracle RAC instances LMS, where n is 0-9 or a-z, maintains a lock database for Global Cache Service (GCS) and buffer cache resources. This process receives, processes, and sends GCS requests, block transfers, and other GCS-related messages. Database instances, Oracle ASM instances, Oracle RAC
LREG Listener Registration Process Registers the instance with the listeners LREG notifies the listeners about instances, services, handlers, and endpoint. Database instances, Oracle ASM instances, Oracle RAC
LSP0 Logical Standby Coordinator Process Schedules transactions for Data Guard SQL Apply LSP0 is the initial process created upon startup of Data Guard SQL Apply. In addition to managing LogMiner and Apply processes, LSP0 is responsible for maintaining inter-transaction dependencies and appropriately scheduling transactions with applier processes. LSP0 is also responsible for detecting and enabling run-time parameter changes for the SQL Apply product as a whole. Database instances, Data Guard
LSP1 Logical Standby Dictionary Build Process Performs a logical standby dictionary build on a primary database The LSP1 process is spawned on a logical standby database that is intended to become the new primary database. A logical standby database becomes a primary database because of switchover or failover. The dictionary is necessary for logical standby databases to interpret the redo of the new primary database. Database instances, Data Guard
LSP2 Logical Standby Set Guard Process Determines which database objects will be protected by the database guard The LSP2 process is created as needed during startup of SQL Apply to update the list of objects that are protected by the database guard. Database instances, Data Guard
Lnnn Pooled Server Process Handles client requests in Database Resident Connection Pooling In Database Resident Connection Pooling, clients connect to a connection broker process. When a connection becomes active, the connection broker hands off the connection to a compatible pooled server process. The pooled server process performs network communication directly on the client connection and processes requests until the client releases the server. After being released, the connection is returned to the broker for monitoring, leaving the server free to handle other clients. Database instances, Database Resident Connection Pooling
MARK Mark AU for Resynchronization Coordinator Process Marks ASM allocation units as stale following a missed write to an offline disk MARK essentially tracks which extents require resynchronization for offline disks. This process runs in the database instance and is started when the database instance first begins using the Oracle ASM instance. If required, MARK can also be started on demand when disks go offline in the Oracle ASM redundancy disk group. Database instances, Oracle ASM instances
MMAN Memory Manager Process Serves as the instance memory manager This process performs the resizing of memory components on the instance. Database instances, Oracle ASM instances
MMNL Manageability Monitor Lite Process Performs tasks relating to manageability, including active session history sampling and metrics computation MMNL performs many tasks relating to manageability, including session history capture and metrics computation. Database instances, Oracle ASM instances
MMON Manageability Monitor Process Performs or schedules many manageability tasks MMON performs many tasks related to manageability, including taking Automatic Workload Repository snapshots and performing Automatic Database Diagnostic Monitor analysis. Database instances, Oracle ASM instances
Mnnn MMON Slave Process Performs manageability tasks on behalf of MMON Mnnn performs manageability tasks dispatched to them by MMON. Tasks performed include taking Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis. Database instances, Oracle ASM instances
MRP0 Managed Standby Recovery Process Coordinates the application of redo on a physical standby database MRP0 is spawned at the start of redo apply on a physical standby database. This process handles the extraction of redo and coordinates the application of that redo on a physical standby database. Database instances, Data Guard
MSnn LogMiner Worker Process Reads redo log files and translates and assembles into transactions Multiple MSnn processes can exists, where n is 0-9 or a-Z. A minimum of three MSnn processes work as a group to provide transactions to a LogMiner client, for example, a logical standby database or a database capture. There may be more than one such group, for example, multiple capture processes configured for either local or downstream capture in a database. Database instances, Logical Standby, Oracle Streams, XStream Outbound servers, Oracle GoldenGate
Nnnn Connection Broker Process Monitors idle connections and hands off active connections in Database Resident Connection Pooling In Database Resident Connection Pooling, clients connect to a connection broker process. When a connection becomes active, the connection broker hands off the connection to a compatible pooled server process. The pooled server process performs network communication directly on the client connection and processes requests until the client releases the server. After being released, the connection is returned to the broker for monitoring, leaving the server free to handle other clients. Database instances, Database Resident Connection Pooling
NSSn Network Server SYNC Process Transfers redo from current online redo logs to remote standby destinations configured for SYNC transport NSSn can run as multiple processes, where n is 1-9 or A. Database instances, Data Guard
NSVn Data Guard Broker NetSlave Process Performs broker network communications between databases in a Data Guard environment NSVn is created when a Data Guard broker configuration is enabled. There can be as many NSVn processes (where n is 0- 9 and A-U) created as there are databases in the Data Guard broker configuration. Database instances, Data Guard
OCFn ASM CF Connection Pool Process Maintains a connection to the Oracle ASM instance for metadata operations Database instances, Oracle ASM instances
OFSD Oracle File Server Background Process Serves file system requests submitted to an Oracle instance This background process listens for new file system requests, both management (like mount, unmount, and export) and I/O requests, and executes them using Oracle threads. Database instances, Oracle RAC
Onnn ASM Connection Pool Process Maintains a connection to the Oracle ASM instance for metadata operations Onnn slave processes are spawned on demand. These processes communicate with the Oracle ASM instance. Database instances, Oracle ASM instances
PING Interconnect Latency Measurement Process Assesses latencies associated with communications for each pair of cluster instances Every few seconds, the process in one instance sends messages to each instance. The message is received by PING on the target instance. The time for the round trip is measured and collected. Database instances, Oracle ASM instances, Oracle RAC
PMON Process Monitor Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally PMON periodically performs cleanup of all the following:

  • Processes that died abnormally

  • Sessions that were killed

  • Detached transactions that have exceeded their idle timeout

  • Detached network connections which have exceeded their idle timeout

In addition, PMON monitors, spawns, and stops the following as needed:

  • Dispatcher and shared server processes

  • Job queue processes

  • Pooled server processes for database resident connection pooling

  • Restartable background processes

Database instances, Oracle ASM instances, Oracle ASM Proxy instances
Pnnn Parallel Query Slave Process Perform parallel execution of a SQL statement (query, DML, or DDL) Parallel Query has two components: a foreground process that acts as query coordinator and a set of parallel slaves (Pnnn) that are background processes. These background processes are spawned or reused during the start of a parallel statement. They receive and perform units of work sent from the query coordinator.The maximum number of Pnnn processes is controlled by the initialization parameter PARALLEL_MAX_SERVERS. Slave processes are numbered from 0 to the PARALLEL_MAX_SERVERS setting. If the query is a GV$ query, then these background processes are numbered backward, starting from PZ99. Database instances, Oracle ASM instances
PRnn Parallel Recovery Process Performs tasks assigned by the coordinator process performing parallel recovery PRnn serves as a slave process for the coordinator process performing parallel media recovery and carries out tasks assigned by the coordinator. The default number of these processes is based on number of CPUs. Database instances
PSP0 Process Spawner Process Spawns Oracle background processes after initial instance startup Database instances, Oracle ASM instances
QMNC Non-sharded queue master process Monitors AQ QMNC is the non-sharded queue master process responsible for facilitating various background activities required by AQ and Oracle Streams: time management of messages, management of nonpersistent queues, cleanup of resources, and so on. QMNC dynamically spawns Qnnn processes as needed for performing these tasks.Note that if the AQ_TM_PROCESSES initialization parameter is set to 0, this process will not start. The database writes the following message to the alert log: WARNING: AQ_TM_PROCESSES is set to 0. System might be adversely affected. Database instances Advanced Queueing
QMnn AQ Master Class Process Per instance per AQ Master Class Process Each of this type of process represents a single class of work item such as AQ notification, queue monitors, and cross process. Database instances Advanced Queueing
Qnnn AQ Server Class Process Per AQ Master Class server process Each server class process acts on behalf of an AQ master class process. This relationship is maintained until the master requires services of a particular service process. Once released, the server class processes are moved to a free server pool. Database instances Advanced Queueing
RBAL ASM Rebalance Master Process Coordinates rebalance activity In an Oracle ASM instance, it coordinates rebalance activity for disk groups. In a database instances, it manages Oracle ASM disk groups. Database instances, Oracle ASM instances
RCBG Result Cache Background Process Handles result cache messages This process is used for handling invalidation and other messages generated by server processes attached to other instances in Oracle RAC. Database instances, Oracle RAC
RECO Recoverer Process Resolves distributed transactions that are pending because of a network or system failure in a distributed database RECO uses the information in the pending transaction table to finalize the status of in-doubt transactions. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. All transactions automatically resolved by RECO are removed from the pending transaction table. Database instances
RM RAT Masking Slave Process Extracts and masks bind values from workloads like SQL tuning sets and DB Replay capture files This background process is used with Data Masking and Real Application Testing. Database instances
RMON Rolling Migration Monitor Process Manages the rolling migration procedure for an Oracle ASM cluster The RMON process is spawned on demand to run the protocol for transitioning an ASM cluster in and out of rolling migration mode. Oracle ASM instance, Oracle RAC
RMSn Oracle RAC Management Process Performs manageability tasks for Oracle RAC RMSn performs a variety of tasks, including creating resources related to Oracle RAC when new instances are added to a cluster. Database instances, Oracle RAC
Rnnn ASM Block Remap Slave Process Remaps a block with a read error A database instance reading from an Oracle ASM disk group can encounter an error during a read. If possible, Oracle ASM asynchronously schedules a Rnnn slave process to remap this bad block from a mirror copy. Oracle ASM instances
RPnn Capture Processing Worker Process Processes a set of workload capture files RPnn are worker processes spawned by calling DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir,parallel_level). Each worker process is assigned a set of workload capture files to process.Worker processes execute in parallel without needing to communicate with each other. After each process is finished processing its assigned files, it exits and informs its parent process.The number of worker processes is controlled by the parallel_level parameter of DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE. By default, parallel_level is null. Then, the number of worker processes is computed as follows:

SELECT VALUE 
FROM   V$PARAMETER 
WHERE  NAME='cpu_count';

When parallel_level is 1, no worker processes are spawned.

Database instances
RPOP Instant Recovery Repopulation Daemon Responsible for re-creating and/or repopulating data files from snapshot files and backup files The RPOP process is responsible for re-creating and repopulating data files from snapshots files. It works with the instant recovery feature to ensure immediate data file access. The local instance has immediate access to the remote snapshot file’s data, while repopulation of the recovered primary data files happens concurrently. Any changes in the data are managed between the instance’s DBW processes and RPOP to ensure the latest copy of the data is returned to the user. Database instances
RSM0 Data Guard Broker Worker Process Performs monitoring management tasks related to Data Guard on behalf of DMON The process is created when a Data Guard broker configuration is enabled. Database instances, Data Guard
RSMN Remote Slave Monitor Process Manages background slave process creation and communication on remote instances in Oracle RAC This background process manages the creation of slave processes and the communication with their coordinators and peers. These background slave processes perform tasks on behalf of a coordinating process running in another cluster instance. Database instances, Oracle RAC
RVWR Recovery Writer Process Writes flashback data to the flashback logs in the fast recovery area RVWR writes flashback data from the flashback buffer in the SGA to the flashback logs. RVWR also creates flashback logs and performs some tasks for flashback log automatic management. Database instances, Flashback Database
SAnn SGA Allocator Allocates SGA A small fraction of SGA is allocated during instance startup. The SAnn process allocates the rest of SGA in small chunks. The process exits upon completion of SGA allocation.The possible processes are SA00 – SAzz. Database instances
SCCn ASM Disk Scrubbing Slave Check Process Performs Oracle ASM disk scrubbing check operation SCCn acts as a slave process for SCRB and performs the checking operations. The possible processes are SCC0-SCC9. Oracle ASM instances
SCRB ASM Disk Scrubbing Master Process Coordinates Oracle ASM disk scrubbing operations SCRB runs in an Oracle ASM instance and coordinates Oracle ASM disk scrubbing operations. Oracle ASM instances
SCRn ASM Disk Scrubbing Slave Repair Process Performs Oracle ASM disk scrubbing repair operation SCRn acts as a slave process for SCRB and performs the repairing operations. The possible processes are SCR0-SCR9. Oracle ASM instances
SCVn ASM Disk Scrubbing Slave Verify Process Performs Oracle ASM disk scrubbing verify operation SCVn acts as a slave process for SCRB and performs the verifying operations. The possible processes are SCV0-SCV9. Oracle ASM instances
SMCO Space Management Coordinator Process Coordinates the execution of various space management tasks This background process coordinates the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks. Database instances
SMON System Monitor Process Performs critical tasks such as instance recovery and dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management SMON performs many database maintenance tasks, including the following:

  • Creates and manages the temporary tablespace metadata
  • Reclaims space used by orphaned temporary segments
  • Maintains the undo tablespace by onlining, offlining, and shrinking the undo segments based on undo space usage statistics
  • Cleans up the data dictionary when it is in a transient and inconsistent state
  • Maintains the SCN to time mapping table used to support Oracle Flashback features

In an Oracle RAC database, the SMON process of one instance can perform instance recovery for other instances that have failed.

SMON is resilient to internal and external errors raised during background activities.

Database instances
Snnn Shared Server Process Handles client requests in the shared server architecture In the shared server architecture, clients connect to a dispatcher process, which creates a virtual circuit for each connection. When the client sends data to the server, the dispatcher receives the data into the virtual circuit and places the active circuit on the common queue to be picked up by an idle shared server. The shared server then reads the data from the virtual circuit and performs the database work necessary to complete the request. When the shared server must send data to the client, the server writes the data back into the virtual circuit and the dispatcher sends the data to the client. After the shared server completes the client request, the server releases the virtual circuit back to the dispatcher and is free to handle other clients.Several initialization parameters relate to shared servers. The principal parameters are: DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, LOCAL_LISTENER, REMOTE_LISTENER.
TEMn ASM disk Test Error Emulation Process Emulates I/O errors on Oracle ASM disks through named events I/O errors can be emulated on Oracle ASM disk I/O through named events. The scope can be the process, instance, or even cluster. Optionally, a set of AUs can be chosen for error emulation. Oracle ASM instances
TTnn Redo Transport Slave Process Ships redo from current online and standby redo logs to remote standby destinations configured for ASYNC transport TTnn can run as multiple processes, where nn is 00 to ZZ. Database instances, Data Guard
VBGn Volume Background Process Communicates between the Oracle ASM instance and the operating system volume driver VBGn handles messages originating from the volume driver in the operating system and sends them to the Oracle ASM instance.VBGn can run as multiple processes, where n is 0-9. Oracle ASM instances, Oracle ASM Proxy instances
VDBG Volume Driver Process Forwards Oracle ASM requests to perform various volume-related tasks VDBG handles requests to lock or unlock an extent for rebalancing, volume resize, disk offline, add or drop a disk, force and dismount disk group to the Dynamic Volume Manager driver. Oracle ASM instances, Oracle ASM Proxy instances
VKRM Virtual Scheduler for Resource Manager Process Serves as centralized scheduler for Resource Manager activity VKRM manages the CPU scheduling for all managed Oracle processes. The process schedules managed processes in accordance with an active resource plan. Database instances
VKTM Virtual Keeper of Time Process Provides a wall clock time and reference time for time interval measurements VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements. The VKTM timer service centralizes time tracking and offloads multiple timer calls from other clients. Database instances, Oracle ASM instances
VMB0 Volume Membership Process Maintains cluster membership on behalf of the Oracle ASM volume driver This process membership in the cluster as an I/O-capable client on behalf of the Oracle ASM volume driver. Oracle ASM instances, Oracle ASM Proxy instances
VUBG Volume drive Umbilicus Background Relays messages between Oracle ASM instance and Oracle ASM Proxy instance that is used by ADVM (for ACFS) Oracle ASM instances, Oracle ASM Proxy instances
Wnnn Space Management Slave Process Performs various background space management tasks, including proactive space allocation and space reclamation Wnnn processes are slave processes dynamically spawned by SMCO to perform space management tasks in the background. These tasks include preallocating space into locally managed tablespace and SecureFiles segments based on space usage growth analysis, and reclaiming space from dropped segments. At most 10 Wnnn slaves can run on one database instance. After being started, the slave acts as an autonomous agent. After it finishes task execution, it automatically picks up another task from the queue. The process terminates itself after being idle for a long time. Database instances
XDMG Exadata Automation Manager Initiates automation tasks involved in managing Exadata storage XDMG monitors all configured Exadata cells for state changes, such as a bad disk getting replaced, and performs the required tasks for such events. Its primary tasks are to watch for inaccessible disks and cells and when they become accessible again, and to initiate the ASM ONLINE operation. The ONLINE operation is handled by XDWK. Oracle ASM instances, Exadata
XDWK Exadata Automation Manager Performs automation tasks requested by XDMG XDWK gets started when asynchronous actions such as ONLINE, DROP, and ADD an Oracle ASM disk are requested by XDMG. After a 5 minute period of inactivity, this process will shut itself down. Oracle ASM instances, Exadata
Xnnn ASM Disk Expel Slave Process Performs Oracle ASM post-rebalance activities This process expels dropped disks after an Oracle ASM rebalance. Oracle ASM instances

1 November 2013

Best hidden features in Oracle

Filed under: Oracle — Venzi @ 19:38

I seem to get into a lot of conversations around Oracle Database Options lately. Of course the Database Options are great, no doubt about it. But people sometimes seem to forget what the word “option” means. There is a reason why Oracle doesn’t put everything into one big product but gives you editions and options and the reason is simple: Not everybody will probably need the full feature set of the Oracle RDBMS. There are over 380 features within the Oracle Database which make it to the best relational database out there and I haven’t seen a single occurrence yet where all of those 380+ features are exploited within one installation. Most of the time people aren’t even aware of the feature richness of the Oracle Database.
Well, as you can imagine not all of those features ended up in Database Options but a lot of them are part of the core product and available in Standard Edition One, Standard Edition and Enterprise Edition. You got them as soon as you purchased a license for any of those three editions. A lot of you will probably still not need all the features that come as part of the editions but here is a list of what I consider the “Best hidden features in Oracle”:

  1. XML DB (this is probably the best hidden gem ever! Since 11g XML DB became really powerful and is a lot more than just a XMLTYPE BLOB data type)
  2. Text (full text search functionality)
  3. SQL Developer (check out early adopter version 4)
  4. SQL Pattern Matching (new in 12c)
  5. Temporal Database (new in 12c, no more start and end timestamps in every table)
  6. Recovery Manager (aka RMAN, no more full export, shutdown and data files copy every night)
  7. Application Express (also known as APEX, build sophisticated web-based applications directly on top of the database. Another feature that got really powerful lately!)
  8. Locator (ever come across geospatial requirements like which of my customers live closest to which branch of my store?)
  9. Enterprise Manager Express (although Enterprise Manager Cloud Control is free itself, Oracle Database 12c comes with a bundled Express version for its own instance management. This is great when you have just a single installation sitting somewhere which you either can’t deploy to your EM Cloud Control installation or you don’t have an EM Cloud Control installation)
  10. Fail Safe (High availability for Windows)

19 August 2013

Oracle 12c enqueue waits

Filed under: Oracle — Venzi @ 16:28
Tags: ,

Just as with the new Oracle 12c lock types it’s time to take a quick look at the new enqueue waits in 12c. Oracle 11.2.0.3 had a total of 304 enqueue waits. 12c has a total of 418. A lot of work seems also to have happened on ASM in this release. The amount of new enqueues per group looks like following:

Group Amount
ASM Enqueue 16
Enqueue-Cross SCN 12
Backup/Restore 11
Queue Ptning global enq 8
Being Written Redo Log 8
SGA Log-Bkt Flush 6
AM Container 5
Media Recovery 5
OLS groups 4
Instance Recovery 3
AVM RTA Access 3
AM Contained File Identification 3
Privilege Capture API 2
Queue table enqueue 2
Label Security Profile 2
RFS Logminer FLC 2
Data Guard Broker 2
IOServer File 2
Queue Partitioning local enqueue 2
Column Key 1
Flush Stat 1
Pluggable Database 1
SGA Log Operation 1
AM Group Rebuild/Validate 1
Real-Time ADDM Flood Control 1
AM Contained File Resize 1
PDB DDL Operations 1
non durable sub bmap alloc 1
CBAC Master Lock 1
IOServer Client 1
PDB Instance State 1
Oracle File System Operation 1
KJCI Serialization Enqueue 1
Spillover Audit files 1
Application Continuity 1
File Set / Dictionary Check 1

Here is the list of all the new enqueues in Oracle 12c:

Group Enqueue Type Description
Application Continuity enq: AC – acquiring partition id Synchronizes partition id
ASM Enqueue enq: AM – ASM ATB COD creation ASM ATB COD creation
ASM Enqueue enq: AM – ASM Audit file Cleanup ASM Audit file Cleanup
ASM Enqueue enq: AM – ASM Audit file Delete ASM Audit file Delete
ASM Enqueue enq: AM – ASM DD update SrRloc Serializes DD update for SrRloc
ASM Enqueue enq: AM – ASM client check Synchronizes client check, kill and register
ASM Enqueue enq: AM – ASM client operation Synchronizes client operations and shutdown
ASM Enqueue enq: AM – ASM file access req Synchronizes file access per client
ASM Enqueue enq: AM – ASM file chown Prevents new owners of open files from being dropped
ASM Enqueue enq: AM – ASM metadata replication Serializes metadata replication
ASM Enqueue enq: AM – Create default DG key in OCR ASM audit create default DG key in OCR
ASM Enqueue enq: AM – Credential creation Lock held while creating credentials
ASM Enqueue enq: AM – Credential deletion Lock held while deleting credentials
ASM Enqueue enq: AM – Disable Remote ASM Lock held while disabling remote asm
ASM Enqueue enq: AM – Enable Remote ASM Lock held while enabling remote asm
ASM Enqueue enq: AM – Register with IOServer Serializes the registration of the database with IOServer
ASM Enqueue enq: AM – SR slice Clear/Mark Serialize clear/assignment for SR slices
Queue Partitioning local enqueue enq: AQ – QPT fg map dqpt kwsptGetOrMapDqPtn
Queue Partitioning local enqueue enq: AQ – QPT fg map qpt kwsptGetOrMapQPtn
non durable sub bmap alloc enq: BA – SUBBMAP contention subscriber access to bitmap
AM Container enq: BC – create container Container lock held while creating a container
AM Container enq: BC – drop container Container lock held while dropping a container
AM Container enq: BC – drop container group Group lock held while dropping a container group
AM Container enq: BC – group – create container Group lock held while creating a container
AM Container enq: BC – group – create file Group lock held while creating a contained file
AM Contained File Identification enq: BI – create file Enqueue held while a contained file is created
AM Contained File Identification enq: BI – delete file Enqueue held while a contained file is cleaned up or deleted
AM Contained File Identification enq: BI – identify file Enqueue held while a contained file is identified
Backup/Restore enq: BR – RS check quiescence Lock to verify that task execution is not being quiesced
Backup/Restore enq: BR – RS invalidate plans Lock to allow invalidation of recovery server plans
Backup/Restore enq: BR – RS lock API Lock to single stream API functions on recovery server
Backup/Restore enq: BR – RS lock db Perform DB storage allocations on recovery server
Backup/Restore enq: BR – RS lock scheduler Lock to perform job scheduling functions on recovery server
Backup/Restore enq: BR – RS lock storage loc Lock to use reserved storage to perform in line purging
Backup/Restore enq: BR – RS lock timer queue Lock to perform timer functions on recovery server
Backup/Restore enq: BR – RS protect plans Lock to protect a recovery server plan from becoming invalid
Backup/Restore enq: BR – RS purge storage loc Lock to restrict a single purging thread per storage location
Backup/Restore enq: BR – RS quiesce storage Lock to prevent new tasks from executing
Backup/Restore enq: BR – RS run scheduler Hint that recovery server schedulers may have new work
AM Group Rebuild/Validate enq: BV – rebuild/validate group Enqueue held while a container group is rebuilding
AM Contained File Resize enq: BZ – resize file Enqueue held while a contained file is resized
CBAC Master Lock enq: CB – role operation Synchronizes accesses to the CBAC roles cached in KGL
Column Key enq: CC – decryption decrypting and caching column key
Pluggable Database enq: CP – Pluggable database resetlogs Synchronization
Flush Stat enq: FH – contention Serializes flush of ILM stats to disk
Oracle File System Operation enq: FO – file system operation contention Synchronizes various Oracle File system operations
File Set / Dictionary Check enq: FS – online log operation Enqueue used to synchronize LGWR cached open online logs and online log clear/drop
IOServer Client enq: IC – IOServer clientID Gets a unique client ID
IOServer File enq: IF – file close File Close
IOServer File enq: IF – file open File Open
PDB Instance State enq: IP – open/close PDB Enqueue used to synchronize instance state changes for PDBs
Instance Recovery enq: IR – arbitrate instance recovery arbitrate parallel instance recovery
Instance Recovery enq: IR – global serialization serialize instance recovery globally
Instance Recovery enq: IR – local serialization serialize instance recovery with local dismount
KJCI Serialization Enqueue enq: KI – contention Synchronizes Cross-Instance Calls
SGA Log Operation enq: MC – Securefile log Serializes log creation/destruction with log flushes
SGA Log-Bkt Flush enq: MF – creating swap in instance Serializes multiple processes in creating the swap space
SGA Log-Bkt Flush enq: MF – flush bkgnd periodic Serializes flushes for a SGA log in bkgnd
SGA Log-Bkt Flush enq: MF – flush client Serializes flushes for a single SGA log – client
SGA Log-Bkt Flush enq: MF – flush destroy Serializes flushes for a single SGA log – destroy
SGA Log-Bkt Flush enq: MF – flush prior error Serializes flushes for a single SGA log – error earlier
SGA Log-Bkt Flush enq: MF – flush space Serializes flushes for a single SGA log – space lack
Media Recovery enq: MR – PDB open Lock used to coordinate media recovery with other uses of PDB datafiles
Media Recovery enq: MR – adg instance recovery Synchronizes multi instance recovery cleanup with adg instance recovery
Media Recovery enq: MR – any role transition Lock used to disallow any concurrent role transition attempt
Media Recovery enq: MR – datafile online Enqueue used to coordinate datafile offline in RAC
Media Recovery enq: MR – multi instance redo apply Lock used to disallow concurrent recovery attempt when MIRA active
Label Security Profile enq: OP – OLS Cleanup unused profiles Synchronizing access to ols$profile when deleting unused profiles
Label Security Profile enq: OP – OLS Store user entries Synchronizing access to ols$user when inserting user entries
Privilege Capture API enq: PA – modify a privilege capture lock held for during modify a privilege capture
Privilege Capture API enq: PA – read a privilege capture lock held for during reading privilege captur status
PDB DDL Operations enq: PB – PDB Lock Enqueue used to synchronize PDB DDL operations
Queue Ptning global enq enq: PQ – QPT LB Trunc sync kwslbFreShadowShrd:LB syncronization with Truncate
Queue Ptning global enq enq: PQ – QPT Trunc kwsptChkTrncLst:Truncate
Queue Ptning global enq enq: PQ – QPT add dqpt kwsptLoadDqCache: Add DQ Partitions.
Queue Ptning global enq enq: PQ – QPT add dqpt fg kwsptMapDqPtn:Drop DQ Partitions in foreground
Queue Ptning global enq enq: PQ – QPT add qpt kwsptLoadQCache: Add Q Partitions.
Queue Ptning global enq enq: PQ – QPT add qpt fg kwsptMapQPtn: Add Q Partitions in foreground
Queue Ptning global enq enq: PQ – QPT drop dqpt kwsptLoadDqCache:Drop DQ Partitions.
Queue Ptning global enq enq: PQ – QPT drop qpt kwsptLoadQCache:Drop Q Partitions.
AVM RTA Access enq: PY – AVM RTA access database Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access database 2 Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access instance Instance RTA info access on AVM
Real-Time ADDM Flood Control enq: RA – RT ADDM flood control Flood control in RAC. Acquired in no-wait.
Data Guard Broker enq: RF – Broker State Lock Captures database-wide broker state
Data Guard Broker enq: RF – FSFO string buffer Makes a FSFO-related string available across RAC instances
Enqueue-Cross SCN enq: RQ – AQ indexed cached commit AQ indexed cached commit
Enqueue-Cross SCN enq: RQ – AQ uncached commit WM update AQ uncached commit WM update
Enqueue-Cross SCN enq: RQ – AQ uncached dequeue AQ uncached dequeue
Enqueue-Cross SCN enq: RQ – Cross export subshard Cross(export) – truncate subshard
Enqueue-Cross SCN enq: RQ – Cross import subshard Cross(import) – free shadow shard
Enqueue-Cross SCN enq: RQ – Cross update scn Cross process updating disk
Enqueue-Cross SCN enq: RQ – Dequeue update scn Dequeue updating scn
Enqueue-Cross SCN enq: RQ – Enqueue commit cached Enqueue commit rac cached
Enqueue-Cross SCN enq: RQ – Enqueue commit uncached Enqueue commit uncached
Enqueue-Cross SCN enq: RQ – Free shadow shard Free shadow – Cross(import) shard
Enqueue-Cross SCN enq: RQ – Parallel cross update scn Parallel cross(update scn) – truncate subshard
Enqueue-Cross SCN enq: RQ – Trucate subshard Truncate – Cross(export) subshard
RFS Logminer FLC enq: RZ – add element Synchronizes access to the foreign log cache while a structure is being inserted
RFS Logminer FLC enq: RZ – remove element Synchronizes access to the foreign log cache while a structure is being removed
OLS groups enq: SG – OLS Add Group Synchronize access to ols$groups when zlllabGroupTreeAddGroup does a read
OLS groups enq: SG – OLS Alter Group Parent Synchronizing access to ols$groups when alter group parent
OLS groups enq: SG – OLS Create Group Synchronize access to ols$groups when creating a group
OLS groups enq: SG – OLS Drop Group Synchronizing access to ols$groups when dropping a group
Queue table enqueue enq: TQ -  sq TM contention SQ TM access to the queue table
Queue table enqueue enq: TQ – INI sq contention INIT access to the queue tables
Being Written Redo Log enq: WL – Far Sync Fail Over Exclude multiple simultaneous failovers
Being Written Redo Log enq: WL – Switchover To Primary One and only one process executing switchover to primary
Being Written Redo Log enq: WL – redo_db table query Synchronize query to redo_db table
Being Written Redo Log enq: WL – redo_db table update Serialize update to redo_db table
Being Written Redo Log enq: WL – redo_log table query Synchronize query to redo_log table
Being Written Redo Log enq: WL – redo_log table update Serialize update to redo_log table
Being Written Redo Log enq: WL – redo_rta table query Synchronize query to redo_rta table
Being Written Redo Log enq: WL – redo_rta table update Serialize update to redo_rta table
Spillover Audit files enq: ZS – excl access to spill audit file lock held while writing to/renaming/deleting spillover audit file

However, one enqueue wait disappeared in 12c which was the “enq: DO – disk online operation”:

Group Enqueue Type Description
ASM Disk Online Lock enq: DO – disk online operation Represents an active disk online operation

It doesn’t seem that it got replaced by any of the new enqueues, at least when looking at the names and descriptions. So it is likely that it is simply not required anymore. A lot of work went into the new release making sure that operations can now be done online which would have required locks/enqueues in the past or simply would have made parts of the schema unusable (especially when it comes to partitioning and index maintenance).

Below is the complete list of enqueue waits in Oracle 12c:

Group Enqueue Type Description
Auto BMR enq: AB – ABMR process initialized Lock held to ensure that ABMR process is initialized
Auto BMR enq: AB – ABMR process start/stop Lock held to ensure that only one ABMR is started in the cluster
Application Continuity enq: AC – acquiring partition id Synchronizes partition id
ASM Disk AU Lock enq: AD – allocate AU Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock enq: AD – deallocate AU Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock enq: AD – relocate AU Synchronizes accesses to a specific ASM disk AU
Edition Lock enq: AE – lock Prevent Dropping an edition in use
Advisor Framework enq: AF – task serialization This enqueue is used to serialize access to an advisor task
Analytic Workspace Generation enq: AG – contention Synchronizes generation use of a particular workspace
ASM Enqueue enq: AM – ASM ACD Relocation Block ASM cache freeze
ASM Enqueue enq: AM – ASM ATB COD creation ASM ATB COD creation
ASM Enqueue enq: AM – ASM Amdu Dump Allow only one AMDU dump when block read failure
ASM Enqueue enq: AM – ASM Audit file Cleanup ASM Audit file Cleanup
ASM Enqueue enq: AM – ASM Audit file Delete ASM Audit file Delete
ASM Enqueue enq: AM – ASM DD update SrRloc Serializes DD update for SrRloc
ASM Enqueue enq: AM – ASM File Destroy Prevent same file deletion race
ASM Enqueue enq: AM – ASM Grow ACD Serializes growing ASM ACD
ASM Enqueue enq: AM – ASM Password File Update Allow one ASM password file update per cluster at a time
ASM Enqueue enq: AM – ASM User Prevents a user from being dropped if it owns any open files
ASM Enqueue enq: AM – ASM cache freeze Start ASM cache freeze
ASM Enqueue enq: AM – ASM client check Synchronizes client check, kill and register
ASM Enqueue enq: AM – ASM client operation Synchronizes client operations and shutdown
ASM Enqueue enq: AM – ASM disk based alloc/dealloc Synchronizes disk based allocations/deallocations
ASM Enqueue enq: AM – ASM file access req Synchronizes file access per client
ASM Enqueue enq: AM – ASM file chown Prevents new owners of open files from being dropped
ASM Enqueue enq: AM – ASM file descriptor Serializes access to ASM file descriptors
ASM Enqueue enq: AM – ASM file relocation Serializes file relocation shrink and drop
ASM Enqueue enq: AM – ASM metadata replication Serializes metadata replication
ASM Enqueue enq: AM – ASM reserved Check id1 of call for specific purpose
ASM Enqueue enq: AM – Create default DG key in OCR ASM audit create default DG key in OCR
ASM Enqueue enq: AM – Credential creation Lock held while creating credentials
ASM Enqueue enq: AM – Credential deletion Lock held while deleting credentials
ASM Enqueue enq: AM – Disable Remote ASM Lock held while disabling remote asm
ASM Enqueue enq: AM – Enable Remote ASM Lock held while enabling remote asm
ASM Enqueue enq: AM – Register with IOServer Serializes the registration of the database with IOServer
ASM Enqueue enq: AM – SR slice Clear/Mark Serialize clear/assignment for SR slices
ASM Enqueue enq: AM – background COD reservation Reserve a background COD entry
ASM Enqueue enq: AM – block repair Serializes block repairs
ASM Enqueue enq: AM – client registration Registers DB instance to ASM client state object hash
ASM Enqueue enq: AM – disk offline Synchronizes disk offlines
ASM Enqueue enq: AM – group block ASM group block
ASM Enqueue enq: AM – group use Client group use
ASM Enqueue enq: AM – rollback COD reservation Reserve a rollback COD entry
ASM Enqueue enq: AM – shutdown Prevent DB instance registration during ASM instance shutdown
MultiWriter Object Access enq: AO – contention Synchornizes access to objects and scalar variables
SPM Autopurge enq: AP – contention This enqueue is used to serialize the purging of SQL plan baselines
Queue Partitioning local enqueue enq: AQ – QPT fg map dqpt kwsptGetOrMapDqPtn
Queue Partitioning local enqueue enq: AQ – QPT fg map qpt kwsptGetOrMapQPtn
Service Operations enq: AS – service activation Synchronizes new service activation
Alter Tablespace enq: AT – contention Serializes ‘alter tablespace’ operations
ASM volume locks enq: AV – AVD client registration Serialize inst reg and first DG use
ASM volume locks enq: AV – add/enable first volume in DG Serialize taking the AVD DG enqueue
ASM volume locks enq: AV – persistent DG number prevent DG number collisions
ASM volume locks enq: AV – volume relocate Serialize relocating volume extents
Analytic Workspace enq: AW – AW generation lock In-use generation state for a particular workspace
Analytic Workspace enq: AW – AW state lock Row lock synchronization for the AW$ table
Analytic Workspace enq: AW – AW$ table lock Global access synchronization to the AW$ table
Analytic Workspace enq: AW – user access for AW Synchronizes user accesses to a particular workspace
KSXA Test Affinity Dictionary enq: AY – contention Affinity Dictionary test affinity synchronization
non durable sub bmap alloc enq: BA – SUBBMAP contention subscriber access to bitmap
Global Transaction Branch enq: BB – 2PC across RAC instances 2PC distributed transaction branch across RAC instances
AM Container enq: BC – create container Container lock held while creating a container
AM Container enq: BC – drop container Container lock held while dropping a container
AM Container enq: BC – drop container group Group lock held while dropping a container group
AM Container enq: BC – group – create container Group lock held while creating a container
AM Container enq: BC – group – create file Group lock held while creating a contained file
BLOOM FILTER enq: BF – PMON Join Filter cleanup PMON bloom filter recovery
BLOOM FILTER enq: BF – allocation contention Allocate a bloom filter in a parallel statement
AM Contained File Identification enq: BI – create file Enqueue held while a contained file is created
AM Contained File Identification enq: BI – delete file Enqueue held while a contained file is cleaned up or deleted
AM Contained File Identification enq: BI – identify file Enqueue held while a contained file is identified
clonedb bitmap file access enq: BM – clonedb bitmap file write synchronizes clonedb bitmap file operations
Backup/Restore enq: BR – RS check quiescence Lock to verify that task execution is not being quiesced
Backup/Restore enq: BR – RS invalidate plans Lock to allow invalidation of recovery server plans
Backup/Restore enq: BR – RS lock API Lock to single stream API functions on recovery server
Backup/Restore enq: BR – RS lock db Perform DB storage allocations on recovery server
Backup/Restore enq: BR – RS lock scheduler Lock to perform job scheduling functions on recovery server
Backup/Restore enq: BR – RS lock storage loc Lock to use reserved storage to perform in line purging
Backup/Restore enq: BR – RS lock timer queue Lock to perform timer functions on recovery server
Backup/Restore enq: BR – RS protect plans Lock to protect a recovery server plan from becoming invalid
Backup/Restore enq: BR – RS purge storage loc Lock to restrict a single purging thread per storage location
Backup/Restore enq: BR – RS quiesce storage Lock to prevent new tasks from executing
Backup/Restore enq: BR – RS run scheduler Hint that recovery server schedulers may have new work
Backup/Restore enq: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
Backup/Restore enq: BR – multi-section restore header Lock held to serialize file header access during multi-section restore
Backup/Restore enq: BR – multi-section restore section Lock held to serialize section access during multi-section restore
Backup/Restore enq: BR – perform autobackup Lock held to perform a new controlfile autobackup
Backup/Restore enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
Backup/Restore enq: BR – request autobackup Lock held to request controlfile autobackups
Backup/Restore enq: BR – space info datafile hdr update Lock held to prevent multiple process to update the headers at the same time
AM Group Rebuild/Validate enq: BV – rebuild/validate group Enqueue held while a container group is rebuilding
AM Contained File Resize enq: BZ – resize file Enqueue held while a contained file is resized
Calibration enq: CA – contention Synchronizes various IO calibration runs
CBAC Master Lock enq: CB – role operation Synchronizes accesses to the CBAC roles cached in KGL
Column Key enq: CC – decryption decrypting and caching column key
Controlfile Transaction enq: CF – contention Synchronizes accesses to the controlfile
Cross-Instance Call Invocation enq: CI – contention Coordinates cross-instance function invocations
Label Security cache enq: CL – compare labels Synchronizes accesses to label cache for label comparison
Label Security cache enq: CL – drop label Synchronizes accesses to label cache when dropping a label
ASM Instance Enqueue enq: CM – diskgroup dismount serialize asm diskgroup dismount
ASM Instance Enqueue enq: CM – gate serialize access to instance enqueue
ASM Instance Enqueue enq: CM – instance indicate ASM diskgroup is mounted
KTCN REG enq enq: CN – race with init during descriptor initialization
KTCN REG enq enq: CN – race with reg during transaction commit to see concurrent registrations
KTCN REG enq enq: CN – race with txn during registration
KTUCLO Master Slave enq enq: CO – master slave det enqueue held be Master in Cleanout Optim
Pluggable Database enq: CP – Pluggable database resetlogs Synchronization
Cleanup querycache registrations enq: CQ – contention Serializes access to cleanup client query cache registrations
Reuse Block Range enq: CR – block range reuse ckpt Coordinates fast block range reuse ckpt
Block Change Tracking enq: CT – CTWR process start/stop Lock held to ensure that only one CTWR process is started in a single instance
Block Change Tracking enq: CT – change stream ownership Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
Block Change Tracking enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
Block Change Tracking enq: CT – local space management Lock held during change tracking space management operations that affect just the data for one thread
Block Change Tracking enq: CT – reading Lock held to ensure that change tracking data remains in existence until a reader is done with it
Block Change Tracking 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
Block Change Tracking enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
Block Change Tracking enq: CT – state change gate 2 Lock held while enabling or disabling change tracking in RAC
Cursor enq: CU – contention Recovers cursors in case of death while compiling
TEXT: Index Specific Lock enq: CX – TEXT: Index Specific Lock Index Specific Lock on CTX index
DbsDriver enq: DB – contention Synchronizes modification of database wide supplementallogging attributes
ASM Local Disk Group enq: DD – contention Synchronizes local accesses to ASM disk groups
Datafile Online in RAC enq: DF – contention Enqueue held by foreground or DBWR when a datafile is brought online in RAC
ASM Disk Group Modification enq: DG – contention Synchronizes accesses to ASM disk groups
Direct Loader Index Creation enq: DL – contention Lock to prevent index DDL during direct load
Database Mount/Open enq: DM – contention Enqueue held by foreground or DBWR to synchronize database mount/open or pluggable database open with other operations
Diskgroup number generator enq: DN – contention Serializes group number generations
ASM Disk Online Lock enq: DO – Staleness Registry create Synchronizes Staleness Registry creation
ASM Disk Online Lock enq: DO – disk online Synchronizes disk onlines and their recovery
ASM Disk Online Lock enq: DO – disk online recovery Synchronizes disk onlines and their recovery
ASM Disk Online Lock enq: DO – startup of MARK process Synchronizes startup of MARK process
LDAP Parameter enq: DP – contention Synchronizes access to LDAP parameters
Distributed Recovery enq: DR – contention Serializes the active distributed recovery operation
Database Suspend enq: DS – contention Prevents a database suspend during LMON reconfiguration
Default Temporary Tablespace enq: DT – contention Serializes changing the default temporary table spaceand user creation
Diana Versioning enq: DV – contention Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
In memory Dispenser enq: DW – contention Serialize in memory dispenser operations
Distributed Transaction enq: DX – contention Serializes tightly coupled distributed transaction branches
ASM File Access Lock enq: FA – access file Synchronizes accesses to open ASM files
Format Block enq: FB – contention Ensures that only one process can format data blcoks in auto segment space managed tablespaces
Disk Group Chunk Mount enq: FC – open an ACD thread LGWR opens an ACD thread
Disk Group Chunk Mount enq: FC – recover an ACD thread SMON recovers an ACD thread
Flashback Database enq: FD – Flashback coordinator Synchronization
Flashback Database enq: FD – Flashback logical operations Synchronization
Flashback Database enq: FD – Flashback on/off Synchronization
Flashback Database enq: FD – Marker generation Synchronization
Flashback Database enq: FD – Restore point create/drop Synchronization
Flashback Database enq: FD – Tablespace flashback on/off Synchronization
KTFA Recovery enq: FE – contention Serializes flashback archive recovery
ACD Relocation Gate Enqueue enq: FG – FG redo generation enq race resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue enq: FG – LGWR redo generation enq race resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue enq: FG – serialize ACD relocate only 1 process in the cluster may do ACD relocation in a disk group
Flush Stat enq: FH – contention Serializes flush of ILM stats to disk
Flashback database log enq: FL – Flashback database log Synchronization
Flashback database log enq: FL – Flashback db command Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
File Mapping enq: FM – contention Synchronizes access to global file mapping state
Oracle File System Operation enq: FO – file system operation contention Synchronizes various Oracle File system operations
File Object enq: FP – global fob contention Synchronizes various File Object(FOB) operations
Disk Group Recovery enq: FR – contention begin recovery of disk group
Disk Group Recovery enq: FR – recover the thread wait for lock domain detach
Disk Group Recovery enq: FR – use the thread indicate this ACD thread is alive
File Set / Dictionary Check enq: FS – contention Enqueue used to synchronize recovery and file operations or synchronize dictionary check
File Set / Dictionary Check enq: FS – online log operation Enqueue used to synchronize LGWR cached open online logs and online log clear/drop
Disk Group Redo Generation enq: FT – allow LGWR writes allow LGWR to generate redo in this thread
Disk Group Redo Generation enq: FT – disable LGWR writes prevent LGWR from generating redo in this thread
DBFUS enq: FU – contention This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
ACD Xtnt Info CIC enq: FX – issue ACD Xtnt Relocation CIC ARB relocates ACD extent
ASM Disk Header enq: HD – contention Serializes accesses to ASM SGA data structures
Queue Page enq: HP – contention Synchronizes accesses to queue pages
Hash Queue enq: HQ – contention Synchronizes the creation of new queue IDs
Direct Loader High Water Mark enq: HV – contention Lock used to broker the high water mark during parallel inserts
Segment High Water Mark enq: HW – contention Lock used to broker the high water mark during parallel inserts
Internal enq: IA – contention
IOServer Client enq: IC – IOServer clientID Gets a unique client ID
NID enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running
IOServer File enq: IF – file close File Close
IOServer File enq: IF – file open File Open
Label Security enq: IL – contention Synchronizes accesses to internal label data structures
Kti blr lock enq: IM – contention for blr Serializes block recovery for IMU txn
PDB Instance State enq: IP – open/close PDB Enqueue used to synchronize instance state changes for PDBs
Instance Recovery enq: IR – arbitrate instance recovery arbitrate parallel instance recovery
Instance Recovery enq: IR – contention Synchronizes instance recovery
Instance Recovery enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
Instance Recovery enq: IR – global serialization serialize instance recovery globally
Instance Recovery enq: IR – local serialization serialize instance recovery with local dismount
Instance State enq: IS – contention Enqueue used to synchronize instance state changes
In-Mem Temp Table Meta Creation enq: IT – contention Synchronizes accesses to a temp object’s metadata
Job Queue Date enq: JD – contention Synchronizes dates between job queue coordinator and slave processes
Materialized View enq: JI – contention Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Job Queue enq: JQ – contention Lock to prevent multiple instances from running a single job
Job Scheduler enq: JS – aq sync Scheduler evt code and AQ sync
Job Scheduler enq: JS – contention Synchronizes accesses to the job cache
Job Scheduler enq: JS – evt notify Lock got during event notification
Job Scheduler enq: JS – evtsub add Lock got when adding subscriber to event q
Job Scheduler enq: JS – evtsub drop Lock got when dropping subscriber to event q
Job Scheduler enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
Job Scheduler enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
Job Scheduler enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
Job Scheduler enq: JS – queue lock Lock on internal scheduler queue
Job Scheduler enq: JS – sch locl enqs Scheduler non-global enqueues
Job Scheduler enq: JS – wdw op Lock got when doing window open/close
SQL STATEMENT QUEUE enq: JX – SQL statement queue  statement
SQL STATEMENT QUEUE enq: JX – cleanup of  queue release SQL statement resources
Scheduler Master DBRM enq: KD – determine DBRM master Determine DBRM master
KJCI Serialization Enqueue enq: KI – contention Synchronizes Cross-Instance Calls
Scheduler enq: KM – contention Synchronizes various Resource Manager operations
Multiple Object Checkpoint enq: KO – fast object checkpoint Coordinates fast object checkpoint
Kupp Process Startup enq: KP – contention Synchronizes kupp process startup
ASM Attributes Enque enq: KQ – access ASM attribute Synchronization of ASM cached attributes
Scheduler Plan enq: KT – contention Synchronizes accesses to the current Resource Manager plan
SGA Log Operation enq: MC – Securefile log Serializes log creation/destruction with log flushes
Materialized View Log DDL enq: MD – contention Lock held during materialized view log DDL statements
SGA Log-Bkt Flush enq: MF – creating swap in instance Serializes multiple processes in creating the swap space
SGA Log-Bkt Flush enq: MF – flush bkgnd periodic Serializes flushes for a SGA log in bkgnd
SGA Log-Bkt Flush enq: MF – flush client Serializes flushes for a single SGA log – client
SGA Log-Bkt Flush enq: MF – flush destroy Serializes flushes for a single SGA log – destroy
SGA Log-Bkt Flush enq: MF – flush prior error Serializes flushes for a single SGA log – error earlier
SGA Log-Bkt Flush enq: MF – flush space Serializes flushes for a single SGA log – space lack
AQ Notification Mail Host enq: MH – contention Lock used for recovery when setting Mail Host for AQ e-mail notifications
Master Key enq: MK – contention changing values in enc$
AQ Notification Mail Port enq: ML – contention Lock used for recovery when setting Mail Port for AQ e-mail notifications
LogMiner enq: MN – contention Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MMON restricted session enq: MO – contention Serialize MMON operations for restricted sessions
Media Recovery enq: MR – PDB open Lock used to coordinate media recovery with other uses of PDB datafiles
Media Recovery enq: MR – adg instance recovery Synchronizes multi instance recovery cleanup with adg instance recovery
Media Recovery enq: MR – any role transition Lock used to disallow any concurrent role transition attempt
Media Recovery enq: MR – contention Lock used to coordinate media recovery with other uses of datafiles
Media Recovery enq: MR – datafile online Enqueue used to coordinate datafile offline in RAC
Media Recovery enq: MR – multi instance redo apply Lock used to disallow concurrent recovery attempt when MIRA active
Media Recovery enq: MR – standby role transition Lock used to disallow concurrent standby role transition attempt
Materialized View Refresh Log enq: MS – contention Lock held during materialized view refresh to setup MV log
Online Datafile Move enq: MV – datafile move Held during online datafile move operation or cleanup
MWIN Schedule enq: MW – contention This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
ksz synch enq: MX – sync storage server info Lock held to generate a response to the storage server information request when an instance is starting up
Outline Cache enq: OC – contention Synchronizes write accesses to the outline cache
Online DDLs enq: OD – Serializing DDLs Lock to prevent concurrent online DDLs
Outline Name enq: OL – contention Synchronizes accesses to a particular outline name
Label Security Profile enq: OP – OLS Cleanup unused profiles Synchronizing access to ols$profile when deleting unused profiles
Label Security Profile enq: OP – OLS Store user entries Synchronizing access to ols$user when inserting user entries
OLAPI Histories enq: OQ – xsoq*histrecb Synchronizes access to olapi history parameter CB
OLAPI Histories enq: OQ – xsoqhiAlloc Synchronizes access to olapi history allocation
OLAPI Histories enq: OQ – xsoqhiClose Synchronizes access to olapi history closing
OLAPI Histories enq: OQ – xsoqhiFlush Synchronizes access to olapi history flushing
OLAPI Histories enq: OQ – xsoqhistrecb Synchronizes access to olapi history globals
TEXT: Generic Lock enq: OT – TEXT: Generic Lock CTX Generic Locks
Encryption Wallet enq: OW – initialization initializing the wallet context
Encryption Wallet enq: OW – termination terminate the wallet context
Privilege Capture API enq: PA – modify a privilege capture lock held for during modify a privilege capture
Privilege Capture API enq: PA – read a privilege capture lock held for during reading privilege captur status
PDB DDL Operations enq: PB – PDB Lock Enqueue used to synchronize PDB DDL operations
Property Lock enq: PD – contention Prevents others from updating the same property
Parameter enq: PE – contention Synchronizes system parameter updates
Password File enq: PF – contention Synchronizes accesses to the password file
Global Parameter enq: PG – contention Synchronizes global system parameter updates
AQ Notification Proxy enq: PH – contention Lock used for recovery when setting Proxy for AQ HTTP notifications
Remote PX Process Spawn Status enq: PI – contention Communicates remote Parallel Execution Server Process creation status
Transportable Tablespace enq: PL – contention Coordinates plug-in operation of transportable tablespaces
Queue Ptning global enq enq: PQ – QPT LB Trunc sync kwslbFreShadowShrd:LB syncronization with Truncate
Queue Ptning global enq enq: PQ – QPT Trunc kwsptChkTrncLst:Truncate
Queue Ptning global enq enq: PQ – QPT add dqpt kwsptLoadDqCache: Add DQ Partitions.
Queue Ptning global enq enq: PQ – QPT add dqpt fg kwsptMapDqPtn:Drop DQ Partitions in foreground
Queue Ptning global enq enq: PQ – QPT add qpt kwsptLoadQCache: Add Q Partitions.
Queue Ptning global enq enq: PQ – QPT add qpt fg kwsptMapQPtn: Add Q Partitions in foreground
Queue Ptning global enq enq: PQ – QPT drop dqpt kwsptLoadDqCache:Drop DQ Partitions.
Queue Ptning global enq enq: PQ – QPT drop qpt kwsptLoadQCache:Drop Q Partitions.
Process Startup enq: PR – contention Synchronizes process startup
PX Process Reservation enq: PS – contention Parallel Execution Server Process reservation and synchronization
ASM Partnership and Status Table enq: PT – contention Synchronizes access to ASM PST metadata
KSV slave startup enq: PV – syncshut Synchronizes instance shutdown_slvstart
KSV slave startup enq: PV – syncstart Synchronizes slave start_shutdown
Buffer Cache PreWarm enq: PW – flush prewarm buffers Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
Buffer Cache PreWarm enq: PW – perwarm status in dbw0 DBWR 0 holds enqueue indicating prewarmed buffers present in cache
AVM RTA Access enq: PY – AVM RTA access database Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access database 2 Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access instance Instance RTA info access on AVM
Real-Time ADDM Flood Control enq: RA – RT ADDM flood control Flood control in RAC. Acquired in no-wait.
ASM Rollback Recovery enq: RB – contention Serializes ASM rollback recovery operations
Result Cache: Enqueue enq: RC – Result Cache: Contention Coordinates access to a result-set
RAC Load enq: RD – RAC load  update RAC load info
Block Repair/Resilvering enq: RE – block repair contention Synchronize block repair/resilvering operations
Data Guard Broker enq: RF – Broker State Lock Captures database-wide broker state
Data Guard Broker enq: RF – DG Broker Current File ID Identifies which configuration metadata file is current
Data Guard Broker enq: RF – FSFO Observer Heartbeat Captures recent Fast-Start Failover Observer heartbeat information
Data Guard Broker enq: RF – FSFO Primary Shutdown suspended Records when FSFO Primary Shutdown is suspended
Data Guard Broker enq: RF – FSFO string buffer Makes a FSFO-related string available across RAC instances
Data Guard Broker enq: RF – RF – Database Automatic Disable Means for detecting when database is being automatically disabled
Data Guard Broker enq: RF – atomicity Ensures atomicity of log transport setup
Data Guard Broker enq: RF – new AI Synchronizes selection of the new apply instance
Data Guard Broker enq: RF – synch: DG Broker metadata Ensures r/w atomicity of DG configuration metadata
Data Guard Broker enq: RF – synchronization: aifo master Synchronizes apply instance failure detection and failover operation
Data Guard Broker enq: RF – synchronization: critical ai Synchronizes critical apply instance among primary instances
wallet_set_mkey enq: RK – set key wallet master key rekey
RAC Encryption Wallet Lock enq: RL – RAC wallet lock RAC wallet lock
Redo Log Nab Computation enq: RN – contention Coordinates nab computations of online logs during recovery
Multiple Object Reuse enq: RO – contention Coordinates flushing of multiple objects
Multiple Object Reuse enq: RO – fast object reuse Coordinates fast object reuse
Resilver / Repair enq: RP – contention Enqueue held when resilvering is needed or when datablock is repaired from mirror
Enqueue-Cross SCN enq: RQ – AQ indexed cached commit AQ indexed cached commit
Enqueue-Cross SCN enq: RQ – AQ uncached commit WM update AQ uncached commit WM update
Enqueue-Cross SCN enq: RQ – AQ uncached dequeue AQ uncached dequeue
Enqueue-Cross SCN enq: RQ – Cross export subshard Cross(export) – truncate subshard
Enqueue-Cross SCN enq: RQ – Cross import subshard Cross(import) – free shadow shard
Enqueue-Cross SCN enq: RQ – Cross update scn Cross process updating disk
Enqueue-Cross SCN enq: RQ – Dequeue update scn Dequeue updating scn
Enqueue-Cross SCN enq: RQ – Enqueue commit cached Enqueue commit rac cached
Enqueue-Cross SCN enq: RQ – Enqueue commit uncached Enqueue commit uncached
Enqueue-Cross SCN enq: RQ – Free shadow shard Free shadow – Cross(import) shard
Enqueue-Cross SCN enq: RQ – Parallel cross update scn Parallel cross(update scn) – truncate subshard
Enqueue-Cross SCN enq: RQ – Trucate subshard Truncate – Cross(export) subshard
Workload Capture and Replay enq: RR – contention Concurrent invocation of DBMS_WORKLOAD_* package API
Reclaimable Space enq: RS – file delete Lock held to prevent file from accessing during space reclaimation
Reclaimable Space enq: RS – persist alert level Lock held to make alert level persistent
Reclaimable Space enq: RS – prevent aging list update Lock held to prevent aging list update
Reclaimable Space enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
Reclaimable Space enq: RS – read alert level Lock held to read alert level
Reclaimable Space enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
Reclaimable Space enq: RS – write alert level Lock held to write alert level
Redo Thread enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
Redo Thread enq: RT – thread internal enable/disable Thread locks held by CKPT to synchronize thread enable and disable
Rolling Migration enq: RU – contention Serializes rolling migration operations
Rolling Migration enq: RU – waiting Results of rolling migration CIC
Materialized View Flags enq: RW – MV metadata contention Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
ASM Extent Relocation Lock enq: RX – relocate extent Synchronizes relocating ASM extents
ASM Extent Relocation Lock enq: RX – unlock extent Synchronizes unlocking ASM extents
RFS Logminer FLC enq: RZ – add element Synchronizes access to the foreign log cache while a structure is being inserted
RFS Logminer FLC enq: RZ – remove element Synchronizes access to the foreign log cache while a structure is being removed
LogicalStandby enq: SB – logical standby metadata Synchronizes Logical Standby metadata operations
LogicalStandby enq: SB – table instantiation Synchronizes table instantiation and EDS operations
Session Migration enq: SE – contention Synchronizes transparent session migration operations
AQ Notification Sender enq: SF – contention Lock used for recovery when setting Sender for AQ e-mail notifications
OLS groups enq: SG – OLS Add Group Synchronize access to ols$groups when zlllabGroupTreeAddGroup does a read
OLS groups enq: SG – OLS Alter Group Parent Synchronizing access to ols$groups when alter group parent
OLS groups enq: SG – OLS Create Group Synchronize access to ols$groups when creating a group
OLS groups enq: SG – OLS Drop Group Synchronizing access to ols$groups when dropping a group
Active Session History Flushing enq: SH – contention Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
Streams Table Instantiation enq: SI – contention Prevents multiple streams tabel instantiations
KTSJ Slave Task Cancel enq: SJ – Slave Task Cancel Serializes cancelling task executed by slave process
Shrink Segment enq: SK – contention Serialize shrink of a segment
Serialize Lock request enq: SL – escalate lock sending lock escalate to LCK0
Serialize Lock request enq: SL – get lock sending lock req to LCK0
Serialize Lock request enq: SL – get lock for undo sending lock req for undo to LCK0
Shared Object enq: SO – contention Synchronizes access to Shared Object (PL/SQL Shared Object Manager)
Spare Enqueue enq: SP – contention 1 (1) due to one-off patch
Spare Enqueue enq: SP – contention 2 (2) due to one-off patch
Spare Enqueue enq: SP – contention 3 (3) due to one-off patch
Spare Enqueue enq: SP – contention 4 (4) due to one-off patch
Sequence Cache enq: SQ – contention Lock to ensure that only one process can replenish the sequence cache
Synchronized Replication enq: SR – contention Coordinates replication / streams operations
Sort Segment enq: SS – contention Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
Space Transaction enq: ST – contention Synchronizes space management activities in dictionary-managed tablespaces
SaveUndo Segment enq: SU – contention Serializes access to SaveUndo Segment
Suspend Writes enq: SW – contention Coordinates the ‘alter system suspend’ operation
Instance Undo enq: TA – contention Serializes operations on undo segments and undo tablespaces
SQL Tuning Base Existence Cache enq: TB – SQL Tuning Base Cache Load Synchronizes writes to the SQL Tuning Base Existence Cache
SQL Tuning Base Existence Cache enq: TB – SQL Tuning Base Cache Update Synchronizes writes to the SQL Tuning Base Existence Cache
Tablespace Checkpoint enq: TC – contention Lock held to guarantee uniqueness of a tablespace checkpoint
Tablespace Checkpoint enq: TC – contention2 Lock of setup of a unqiue tablespace checkpoint in null mode
KTF map table enqueue enq: TD – KTF dump entries KTF dumping time/scn mappings in SMON_SCN_TIME table
KTF broadcast enq: TE – KTF broadcast KTF broadcasting
Temporary File enq: TF – contention Serializes dropping of a temporary file
Threshold Chain enq: TH – metric threshold evaluation Serializes threshold in-memory chain access
Auto Task Serialization enq: TK – Auto Task Serialization Lock held by MMON to prevent other MMON spawning of Autotask Slave
Auto Task Serialization enq: TK – Auto Task Slave Lockout Serializes spawned Autotask Slaves
Log Lock enq: TL – contention Serializes threshold log table read and update
DML enq: TM – contention Synchronizes accesses to an object
Temp Object enq: TO – contention Synchronizes DDL and DML operations on a temp object
Runtime Fixed Table Purge enq: TP – contention Lock held during purge and dynamic reconfiguration of fixed tables.
Queue table enqueue enq: TQ -  sq TM contention SQ TM access to the queue table
Queue table enqueue enq: TQ – DDL contention TM access to the queue table
Queue table enqueue enq: TQ – DDL-INI contention Streams DDL on queue table
Queue table enqueue enq: TQ – INI contention TM access to the queue table
Queue table enqueue enq: TQ – INI sq contention INIT access to the queue tables
Queue table enqueue enq: TQ – TM contention TM access to the queue table
Temporary Segment enq: TS – contention Serializes accesses to temp segments
Tablespace enq: TT – contention Serializes DDL operations on tablespaces
Cross-Instance Transaction enq: TW – contention Lock held by one instance to wait for transactions on all instances to finish
Transaction enq: TX – allocate ITL entry Allocating an ITL entry in order to begin a transaction
Transaction enq: TX – contention Lock held by a transaction to allow other transactions to wait for it
Transaction enq: TX – index contention Lock held on an index during a split to prevent other operations on it
Transaction enq: TX – row lock contention Lock held on a particular row by a transaction to prevent other transactions from modifying it
User-defined enq: UL – contention Lock used by user applications
Undo Segment enq: US – contention Lock held to perform DDL on the undo segment
AQ Notification Watermark enq: WA – contention Lock used for recovery when setting Watermark for memory usage in AQ notifications
AWR Flush enq: WF – contention This enqueue is used to serialize the flushing of snapshots
Write gather local enqueue enq: WG – delete fso acquire lobid local enqueue when deleting fso
Write gather local enqueue enq: WG – lock fso acquire lobid local enqueue when locking fso
Being Written Redo Log enq: WL – Far Sync Fail Over Exclude multiple simultaneous failovers
Being Written Redo Log enq: WL – RAC-wide SGA contention Serialize access to RAC-wide SGA
Being Written Redo Log enq: WL – RFS global state contention Serialize access to RFS global state
Being Written Redo Log enq: WL – Switchover To Primary One and only one process executing switchover to primary
Being Written Redo Log enq: WL – Test access/locking Testing redo transport access/locking
Being Written Redo Log enq: WL – contention Coordinates access to redo log files and archive logs
Being Written Redo Log enq: WL – redo_db table query Synchronize query to redo_db table
Being Written Redo Log enq: WL – redo_db table update Serialize update to redo_db table
Being Written Redo Log enq: WL – redo_log table query Synchronize query to redo_log table
Being Written Redo Log enq: WL – redo_log table update Serialize update to redo_log table
Being Written Redo Log enq: WL – redo_rta table query Synchronize query to redo_rta table
Being Written Redo Log enq: WL – redo_rta table update Serialize update to redo_rta table
WLM Plan Operations enq: WM – WLM Plan activation Synchronizes new WLM Plan activation
AWR Purge enq: WP – contention This enqueue handles concurrency between purging and baselines
LNS archiving log enq: WR – contention Coordinates access to logs by Async LNS and ARCH/FG
XDB Configuration enq: XC – XDB Configuration Lock obtained when incrementing XDB configuration version number
Auto Online Exadata disks enq: XD – ASM disk OFFLINE Serialize OFFLINE Exadata disk operations
Auto Online Exadata disks enq: XD – ASM disk ONLINE Serialize ONLINE Exadata disk operations
Auto Online Exadata disks enq: XD – ASM disk drop/add Serialize Auto Drop/Add Exadata disk operations
AQ Notification No-Proxy enq: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
ASM Extent Fault Lock enq: XL – fault extent map Keep multiple processes from faulting in the same extent chunk
ASM Extent Relocation Enqueue enq: XQ – purification wait for relocation before doing block purification
ASM Extent Relocation Enqueue enq: XQ – recovery prevent relocation during _recovery_asserts checking
ASM Extent Relocation Enqueue enq: XQ – relocation wait for recovery before doing relocation
Quiesce / Force Logging enq: XR – database force logging Lock held during database force logging mode
Quiesce / Force Logging enq: XR – quiesce database Lock held during database quiesce
Internal Test enq: XY – contention Lock used for internal testing
Audit Partition enq: ZA – add std audit table partition lock held to add partition to std audit table
FGA Partition enq: ZF – add fga audit table partition lock held to add partition to fga audit table
File Group enq: ZG – contention Coordinates file group operations
Compression Analyzer enq: ZH – compression analysis Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
Spillover Audit files enq: ZS – excl access to spill audit file lock held while writing to/renaming/deleting spillover audit file
Global Context Action enq: ZZ – update hash tables lock held for updating global context hash tables

The SQL to retrieve the enqueue waits hasn’t changed of course:

SELECT eq.eq_name "Group", ev.name "Enqueue Type", eq.req_description "Description"
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event#=ev.event#
ORDER BY ev.name;

12 August 2013

Oracle 12c lock types

Filed under: Oracle — Venzi @ 11:37
Tags: ,

As stated in my previous post, it’s time for me to revisit some of my posts and check the relevance for 12c. Let’s start with the one on locks. As posted in late 2010 there are 202 different lock types in Oracle 11g. It’s actually 205 in 11.2.0.3 which was not out back then. Anyway, in 12c we have now 240 but still only 4 which are caused by user interaction, aka. SQL statements.

Non of the existing 11g locks disappeared in 12c. But following got introduced in 12c:

TYPE NAME IS_USER LIVES_IN DESCRIPTION
AC Application Continuity NO (Non-) Container/CDB Synchronizes new service activation
AQ Queue Partitioning local enqueue NO (Non-) Container/CDB Synchronizes partition operations on queue table within instance
BA non durable sub bmap alloc NO (Non-) Container/CDB Synchronizes access to globl Bitmap Alloc
BC AM Container NO (Non-) Container/CDB Availability Machine Container
BI AM Contained File Identification NO (Non-) Container/CDB Availability Machine Contained File Identification
BV AM Group Rebuild/Validate NO (Non-) Container/CDB Availability Machine Container Rebuild/Validate
BZ AM Contained File Resize NO (Non-) Container/CDB Availability Machine Contained File Resize
CB CBAC Master Lock NO (Non-) Container/CDB Synchronizes accesses to the CBAC roles cached in KGL
CC Column Key NO (Non-) Container/CDB Serializes Column Encryption Key Cache
CP Pluggable Database NO (Non-) Container/CDB Coordinate pluggable database operations
FH Flush Stat NO (Non-) Container/CDB Serializes flush of ILM stats to disk
FO Oracle File System Operation NO (Non-) Container/CDB Synchronize Oracle File System Operations
HM Hang Manager Test Resource NO (Non-) Container/CDB Resource to Generate Hangs for Hang Manager Testing
IC IOServer Client NO (Non-) Container/CDB Gets a unique client ID
IF IOServer File NO (Non-) Container/CDB Synchronizes file operations
IP PDB Instance State NO (Non-) Container/CDB Enqueue used to synchronize instance state changes for PDBs
KI KJCI Serialization Enqueue NO (Non-) Container/CDB Enqueue to Serialize KJCI Cross-Instance Calls
KZ ASM:Purge Audit NO (Non-) Container/CDB Coordinate GEN0 on multiple instances
MC SGA Log Operation NO (Non-) Container/CDB Lock held during dml/ddl for a securefile log
MF SGA Log-Bkt Flush NO (Non-) Container/CDB Lock held during flushing a log bucket
MP SGA Log Partition NO (Non-) Container/CDB Lock held during partitioning an instance log
OE DLM Enqueue Owner Serialization NO (Non-) Container/CDB Enqueue to serialize access to an Omni Enqueue Owner
OP Label Security Profile NO (Non-) Container/CDB Synchronizes access to the ols$profile table
PA Privilege Capture API NO (Non-) Container/CDB Lock held for modifying or reading a privilege capture
PB PDB DDL Operations NO (Non-) Container/CDB Enqueue used to synchronize PDB DDL operations
PP GES Plug and Play Enqueue NO (Non-) Container/CDB Coordinates Adding Plug and Play Instances
PQ Queue Ptning global enq NO (Non-) Container/CDB Synchronizes partitioning operations on queue table accross instances
PY AVM RTA Access NO (Non-) Container/CDB Coordinate RTA info access on AVM
RA Real-Time ADDM Flood Control NO (Non-) Container/CDB Coordinate Flood Control in RAC
RQ Enqueue-Cross SCN NO (Non-) Container/CDB Synchronize enqueue-dequeue-cross on SCN generation
RZ RFS Logminer FLC NO (Non-) Container/CDB Synchronizes updates to logminer foreign log cache
SG OLS groups NO (Non-) Container/CDB Synchronizes access to ols$groups table
XK GES Test-Lock for KSIRESTXDV NO (Non-) Container/CDB Lock used for internal testing
XM ASM RM Master Lock NO (Non-) Container/CDB RMON which process this lock is the Master
ZS Spillover Audit files NO (Non-) Container/CDB Lock held for exclusive access on a Spillover audit file

As you can see there is a new column LIVES_IN (named cdb_id in the v$ view) which defines whether the lock resides within a CDB (or Non-CDB if you haven’t installed the container database version) or a pluggable database. As it seems all the locks currently reside within the CDB which makes sense as locks are held within the SGA structure and this structure is held at the CDB level.

Here is the new list of all locks in 12c:

TYPE NAME IS_USER LIVES_IN DESCRIPTION
TM DML YES (Non-) Container/CDB Synchronizes accesses to an object
TX Transaction YES (Non-) Container/CDB Lock held by a transaction to allow other transactions to wait for it
UL User-defined YES (Non-) Container/CDB Lock used by user applications
ZH Compression Analyzer YES (Non-) Container/CDB Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
AB Auto BMR NO (Non-) Container/CDB A general class of locks used by auto BMR for various purposes
AC Application Continuity NO (Non-) Container/CDB Synchronizes new service activation
AD ASM Disk AU Lock NO (Non-) Container/CDB Synchronizes accesses to a specific ASM disk AU
AE Edition Lock NO (Non-) Container/CDB Prevent Dropping an edition in use
AF Advisor Framework NO (Non-) Container/CDB This enqueue is used to serialize access to an advisor task
AG Analytic Workspace Generation NO (Non-) Container/CDB Synchronizes generation use of a particular workspace
AK GES Deadlock Test NO (Non-) Container/CDB Lock used for internal testing
AM ASM Enqueue NO (Non-) Container/CDB ASM instance general-purpose enqueue
AO MultiWriter Object Access NO (Non-) Container/CDB Synchronizes access to objects and scalar variables
AP SPM Autopurge NO (Non-) Container/CDB This enqueue is used to serialize the purging of SQL plan baselines
AQ Queue Partitioning local enqueue NO (Non-) Container/CDB Synchronizes partition operations on queue table within instance
AR ASM Relocation Lock NO (Non-) Container/CDB Protects locked extent pointers during ASM file relocation
AS Service Operations NO (Non-) Container/CDB Synchronizes new service activation
AT Alter Tablespace NO (Non-) Container/CDB Serializes ‘alter tablespace’ operations
AV ASM volume locks NO (Non-) Container/CDB id1 is for persistent DG number locking. id2 is for other volume serialization
AW Analytic Workspace NO (Non-) Container/CDB Synchronizes access to Analytic Workspace resources
AY KSXA Test Affinity Dictionary NO (Non-) Container/CDB Affinity Dictionary test affinity synchronization
BA non durable sub bmap alloc NO (Non-) Container/CDB Synchronizes access to globl Bitmap Alloc
BB Global Transaction Branch NO (Non-) Container/CDB 2PC distributed transaction branch across RAC instances
BC AM Container NO (Non-) Container/CDB Availability Machine Container
BF BLOOM FILTER NO (Non-) Container/CDB Synchronize access to a bloom filter in a parallel statement
BI AM Contained File Identification NO (Non-) Container/CDB Availability Machine Contained File Identification
BM clonedb bitmap file access NO (Non-) Container/CDB synchronizes clonedb bitmap file operations
BR Backup/Restore NO (Non-) Container/CDB Lock held by a backup/restore operation to allow other operations to wait for it
BV AM Group Rebuild/Validate NO (Non-) Container/CDB Availability Machine Container Rebuild/Validate
BZ AM Contained File Resize NO (Non-) Container/CDB Availability Machine Contained File Resize
CA Calibration NO (Non-) Container/CDB Lock used by IO Calibration
CB CBAC Master Lock NO (Non-) Container/CDB Synchronizes accesses to the CBAC roles cached in KGL
CC Column Key NO (Non-) Container/CDB Serializes Column Encryption Key Cache
CF Controlfile Transaction NO (Non-) Container/CDB Synchronizes accesses to the controlfile
CI Cross-Instance Call Invocation NO (Non-) Container/CDB Coordinates cross-instance function invocations
CL Label Security cache NO (Non-) Container/CDB Synchronizes accesses to label cache and label tables
CM ASM Instance Enqueue NO (Non-) Container/CDB ASM instance and gate enqueue
CN KTCN REG enq NO (Non-) Container/CDB Enqueue held for registrations for change notifications
CO KTUCLO Master Slave enq NO (Non-) Container/CDB Enqueue held for determining Master Slaves
CP Pluggable Database NO (Non-) Container/CDB Coordinate pluggable database operations
CQ Cleanup querycache registrations NO (Non-) Container/CDB Serializes access to cleanup client query cache registrations
CR Reuse Block Range NO (Non-) Container/CDB Coordinates checkpointing of block range reuse
CT Block Change Tracking NO (Non-) Container/CDB A general class of locks used by change tracking for various purposes
CU Cursor NO (Non-) Container/CDB Recovers cursors in case of death while compiling
CX TEXT: Index Specific Lock NO (Non-) Container/CDB Index Specific Lock on CTX index
DB DbsDriver NO (Non-) Container/CDB Synchronizes modification of database wide supplementallogging attributes
DD ASM Local Disk Group NO (Non-) Container/CDB Synchronizes local accesses to ASM disk groups
DF Datafile Online in RAC NO (Non-) Container/CDB Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG ASM Disk Group Modification NO (Non-) Container/CDB Synchronizes accesses to ASM disk groups
DI GES Internal NO (Non-) Container/CDB Coordinates Global Enqueue Service deadlock detection
DL Direct Loader Index Creation NO (Non-) Container/CDB Lock to prevent index DDL during direct load
DM Database Mount/Open NO (Non-) Container/CDB Enqueue held by foreground or DBWR to synchronize database mount/open or pluggable database open with other operations
DN Diskgroup number generator NO (Non-) Container/CDB Serializes Group number generations
DO ASM Disk Online Lock NO (Non-) Container/CDB Synchronizes disk onlines and their recovery
DP LDAP Parameter NO (Non-) Container/CDB Synchronizes access to LDAP parameters
DQ ASM RBAL doorbell NO (Non-) Container/CDB Inter-RBAL process metadata invalidation notification
DR Distributed Recovery NO (Non-) Container/CDB Serializes the active distributed recovery operation
DS Database Suspend NO (Non-) Container/CDB Prevents a database suspend during LMON reconfiguration
DT Default Temporary Tablespace NO (Non-) Container/CDB Serializes changing the default temporary table space and user creation
DV Diana Versioning NO (Non-) Container/CDB Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW In memory Dispenser NO (Non-) Container/CDB Serialize in memory dispenser operations
DX Distributed Transaction NO (Non-) Container/CDB Serializes tightly coupled distributed transaction branches
E Library Cache Lock 2 NO (Non-) Container/CDB Synchronizes accesses to library cache objects
FA ASM File Access Lock NO (Non-) Container/CDB Synchronizes accesses to open ASM files
FB Format Block NO (Non-) Container/CDB Ensures that only one process can format data blocks in auto segment space managed tablespaces
FC Disk Group Chunk Mount NO (Non-) Container/CDB controls access to an ACD chunk
FD Flashback Database NO (Non-) Container/CDB Coordinate flashback database
FE KTFA Recovery NO (Non-) Container/CDB Flashback archive Enqueue to serialize recovery
FG ACD Relocation Gate Enqueue NO (Non-) Container/CDB ACD relocation serialization
FH Flush Stat NO (Non-) Container/CDB Serializes flush of ILM stats to disk
FL Flashback database log NO (Non-) Container/CDB Synchronize access to flashback database log
FM File Mapping NO (Non-) Container/CDB Synchronizes access to global file mapping state
FO Oracle File System Operation NO (Non-) Container/CDB Synchronize Oracle File System Operations
FP File Object NO (Non-) Container/CDB Synchronizes various File Object(FOB) operations
FR Disk Group Recovery NO (Non-) Container/CDB enqueue to control ASM recovery
FS File Set / Dictionary Check NO (Non-) Container/CDB Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT Disk Group Redo Generation NO (Non-) Container/CDB controls the privilege to generate redo in a thread
FU DBFUS NO (Non-) Container/CDB This enqueue is used to serialize the capture of the DB Feature. Usage and High Water Mark Statistics
FW Flashback Writer NO (Non-) Container/CDB Coordinate RVWR on multiple instances
FX ACD Xtnt Info CIC NO (Non-) Container/CDB serialize ACD relocation CIC
FZ ASM Freezing Cache Lock NO (Non-) Container/CDB Freezes ASM Cache for a diskgroup
G Library Cache Pin 2 NO (Non-) Container/CDB Synchronizes accesses to the contents of library cache objects
HD ASM Disk Header NO (Non-) Container/CDB Serializes accesses to ASM SGA data structures
HM Hang Manager Test Resource NO (Non-) Container/CDB Resource to Generate Hangs for Hang Manager Testing
HP Queue Page NO (Non-) Container/CDB Synchronizes accesses to queue pages
HQ Hash Queue NO (Non-) Container/CDB Synchronizes the creation of new queue IDs
HV Direct Loader High Water Mark NO (Non-) Container/CDB Lock used to broker the high water mark during parallel inserts
HW Segment High Water Mark NO (Non-) Container/CDB Lock used to broker the high water mark during parallel inserts
IA Internal NO (Non-) Container/CDB
IC IOServer Client NO (Non-) Container/CDB Gets a unique client ID
ID NID NO (Non-) Container/CDB Lock held by a NID operation to allow other operations to wait for it
IF IOServer File NO (Non-) Container/CDB Synchronizes file operations
IL Label Security NO (Non-) Container/CDB Synchronizes accesses to internal label data structures
IM Kti blr lock NO (Non-) Container/CDB Serializes block recovery for an IMU txn
IP PDB Instance State NO (Non-) Container/CDB Enqueue used to synchronize instance state changes for PDBs
IR Instance Recovery NO (Non-) Container/CDB Synchronizes instance recovery
IS Instance State NO (Non-) Container/CDB Enqueue used to synchronize instance state changes
IT In-Mem Temp Table Meta Creation NO (Non-) Container/CDB Synchronizes accesses to a temp object’s metadata
IV Library Cache Invalidation NO (Non-) Container/CDB Synchronizes library cache object invalidations across instances
IZ INSTANCE LOCK NO (Non-) Container/CDB Proctects the lock name space used by the instance
JD Job Queue Date NO (Non-) Container/CDB Synchronizes dates between job queue coordinator and slave processes
JI Materialized View NO (Non-) Container/CDB Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Job Queue NO (Non-) Container/CDB Lock to prevent multiple instances from running a single job
JS Job Scheduler NO (Non-) Container/CDB Synchronizes accesses to the job cache
JX SQL STATEMENT QUEUE NO (Non-) Container/CDB synchronize SQL statement queue operations
KD Scheduler Master DBRM NO (Non-) Container/CDB Determine DBRM master
KE ASM Cached Attributes NO (Non-) Container/CDB Synchronization of ASM cached attributes
KI KJCI Serialization Enqueue NO (Non-) Container/CDB Enqueue to Serialize KJCI Cross-Instance Calls
KK Kick Instance to Switch Logs NO (Non-) Container/CDB Lock held by open redo thread, used by other instances to force a log switch
KL LOB KSI LOCK NO (Non-) Container/CDB KSI lock for buffer cache and wgc concurrency
KM Scheduler NO (Non-) Container/CDB Synchronizes various Resource Manager operations
KO Multiple Object Checkpoint NO (Non-) Container/CDB Coordinates checkpointing of multiple objects
KP Kupp Process Startup NO (Non-) Container/CDB Synchronizes kupp process startup
KQ ASM Attributes Enque NO (Non-) Container/CDB Single Inst Sync of ASM attributes
KT Scheduler Plan NO (Non-) Container/CDB Synchronizes accesses to the current Resource Manager plan
KZ ASM:Purge Audit NO (Non-) Container/CDB Coordinate GEN0 on multiple instances
L Library Cache Lock 1 NO (Non-) Container/CDB Synchronizes accesses to library cache objects
MC SGA Log Operation NO (Non-) Container/CDB Lock held during dml/ddl for a securefile log
MD Materialized View Log DDL NO (Non-) Container/CDB Lock held during materialized view log DDL statements
MF SGA Log-Bkt Flush NO (Non-) Container/CDB Lock held during flushing a log bucket
MH AQ Notification Mail Host NO (Non-) Container/CDB Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK Master Key NO (Non-) Container/CDB Serializes enc$ access
ML AQ Notification Mail Port NO (Non-) Container/CDB Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN LogMiner NO (Non-) Container/CDB Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO MMON restricted session NO (Non-) Container/CDB Serialize MMON operations for restricted sessions
MP SGA Log Partition NO (Non-) Container/CDB Lock held during partitioning an instance log
MR Media Recovery NO (Non-) Container/CDB Lock used to coordinate media recovery with other uses of datafiles
MS Materialized View Refresh Log NO (Non-) Container/CDB Lock held during materialized view refresh to setup MV log
MV Online Datafile Move NO (Non-) Container/CDB Synchronizes online datafile move operation or cleanup
MW MWIN Schedule NO (Non-) Container/CDB This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
MX ksz synch NO (Non-) Container/CDB Used to synchronize storage server info across all nodes
N Library Cache Pin 1 NO (Non-) Container/CDB Synchronizes accesses to the contents of library cache objects
OC Outline Cache NO (Non-) Container/CDB Synchronizes write accesses to the outline cache
OD Online DDLs NO (Non-) Container/CDB Lock to prevent concurrent online DDLs
OE DLM Enqueue Owner Serialization NO (Non-) Container/CDB Enqueue to serialize access to an Omni Enqueue Owner
OL Outline Name NO (Non-) Container/CDB Synchronizes accesses to a particular outline name
OP Label Security Profile NO (Non-) Container/CDB Synchronizes access to the ols$profile table
OQ OLAPI Histories NO (Non-) Container/CDB Synchronizes access to olapi history resources
OT TEXT: Generic Lock NO (Non-) Container/CDB Generic Lock on CTX index
OW Encryption Wallet NO (Non-) Container/CDB Serializes wallet initialization and access
PA Privilege Capture API NO (Non-) Container/CDB Lock held for modifying or reading a privilege capture
PB PDB DDL Operations NO (Non-) Container/CDB Enqueue used to synchronize PDB DDL operations
PD Property Lock NO (Non-) Container/CDB Serializes property update
PE Parameter NO (Non-) Container/CDB Synchronizes system parameter updates
PF Password File NO (Non-) Container/CDB Synchronizes accesses to the password file
PG Global Parameter NO (Non-) Container/CDB Synchronizes global system parameter updates
PH AQ Notification Proxy NO (Non-) Container/CDB Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Remote PX Process Spawn Status NO (Non-) Container/CDB Communicates remote Parallel Execution Server Process creation status
PL Transportable Tablespace NO (Non-) Container/CDB Coordinates plug-in operation of transportable tablespaces
PM ASM PST Signalling NO (Non-) Container/CDB Signals inter-instance access to ASM PST metadata
PP GES Plug and Play Enqueue NO (Non-) Container/CDB Coordinates Adding Plug and Play Instances
PQ Queue Ptning global enq NO (Non-) Container/CDB Synchronizes partitioning operations on queue table accross instances
PR Process Startup NO (Non-) Container/CDB Synchronizes process startup
PS PX Process Reservation NO (Non-) Container/CDB Parallel Execution Server Process reservation and synchronization
PT ASM Partnership and Status Table NO (Non-) Container/CDB Gates inter-node synchronization of ASM PST metadata
PV KSV slave startup NO (Non-) Container/CDB Synchronizes slave start_shut
PW Buffer Cache PreWarm NO (Non-) Container/CDB Coordinates Direct Loads with Prewarmed cache buffers
PY AVM RTA Access NO (Non-) Container/CDB Coordinate RTA info access on AVM
Q Row Cache NO (Non-) Container/CDB Coordinates updates and accesses to row cache objects
RA Real-Time ADDM Flood Control NO (Non-) Container/CDB Coordinate Flood Control in RAC
RB ASM Rollback Recovery NO (Non-) Container/CDB Serializes ASM rollback recovery operations
RC Result Cache: Enqueue NO (Non-) Container/CDB Accessing a result in the result-set cache
RD RAC Load NO (Non-) Container/CDB synchronize update of RAC load info
RE Block Repair/Resilvering NO (Non-) Container/CDB Synchronizes block repair/mirror resilvering operations
RF Data Guard Broker NO (Non-) Container/CDB Synchronizes broker lock operation involving lock value
RK wallet_set_mkey NO (Non-) Container/CDB Serializes wallet master key rekey
RL RAC Encryption Wallet Lock NO (Non-) Container/CDB RAC encryption wallet lock
RM GES Resource Remastering NO (Non-) Container/CDB Coordinates Global Enqueue Service resource remastering
RN Redo Log Nab Computation NO (Non-) Container/CDB Coordinates nab computations of online logs during recovery
RO Multiple Object Reuse NO (Non-) Container/CDB Coordinates flushing of multiple objects
RP Resilver / Repair NO (Non-) Container/CDB Enqueue held when resilvering is needed or when datablock is repaired from mirror
RQ Enqueue-Cross SCN NO (Non-) Container/CDB Synchronize enqueue-dequeue-cross on SCN generation
RR Workload Capture and Replay NO (Non-) Container/CDB Prevents concurrent invokers of DBMS_WORKLOAD_*
RS Reclaimable Space NO (Non-) Container/CDB Lock held by a space reclaimable operation to allow other operations to wait for it
RT Redo Thread NO (Non-) Container/CDB Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Rolling Migration NO (Non-) Container/CDB Serilized rolling migration operations
RW Materialized View Flags NO (Non-) Container/CDB Lock used when creating or readingmaterialized view flags in detail tables
RX ASM Extent Relocation Lock NO (Non-) Container/CDB Synchronizes relocating ASM extents
RZ RFS Logminer FLC NO (Non-) Container/CDB Synchronizes updates to logminer foreign log cache
SB LogicalStandby NO (Non-) Container/CDB Synchronizes Logical Standby metadata operations
SC System Change Number NO (Non-) Container/CDB Coordinates system change number generation on multiple instances
SE Session Migration NO (Non-) Container/CDB Lock used by transparent session migration
SF AQ Notification Sender NO (Non-) Container/CDB Lock used for recovery when setting Sender for AQ e-mail notifications
SG OLS groups NO (Non-) Container/CDB Synchronizes access to ols$groups table
SH Active Session History Flushing NO (Non-) Container/CDB To prevent multiple concurrent flushers
SI Streams Table Instantiation NO (Non-) Container/CDB Prevents muiltiple streams table instantiations
SJ KTSJ Slave Task Cancel NO (Non-) Container/CDB Serializes cancelling task executed by slave process
SK Shrink Segment NO (Non-) Container/CDB Serialize shrink of a segment
SL Serialize Lock request NO (Non-) Container/CDB Request serialization to LCK0
SM SMON Serialization NO (Non-) Container/CDB Lock to check SMON global work in RAC
SO Shared Object NO (Non-) Container/CDB Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
SP Spare Enqueue NO (Non-) Container/CDB Spare enqueue for one-off backports
SQ Sequence Cache NO (Non-) Container/CDB Lock to ensure that only one process can replenish the sequence cache
SR Synchronized Replication NO (Non-) Container/CDB Coordinates replication / streams operations
SS Sort Segment NO (Non-) Container/CDB Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST Space Transaction NO (Non-) Container/CDB Synchronizes space management activities in dictionary-managed tablespaces
SU SaveUndo Segment NO (Non-) Container/CDB Serializes access to SaveUndo Segment
SV Sequence Ordering NO (Non-) Container/CDB Lock to ensure ordered sequence allocation in RAC mode
SW Suspend Writes NO (Non-) Container/CDB Coordinates the ‘alter system suspend’ operation
TA Instance Undo NO (Non-) Container/CDB Serializes operations on undo segments and undo tablespaces
TB SQL Tuning Base Existence Cache NO (Non-) Container/CDB Synchronizes writes to the SQL Tuning Base Existence Cache
TC Tablespace Checkpoint NO (Non-) Container/CDB Lock held to guarantee uniqueness of a tablespace checkpoint
TD KTF map table enqueue NO (Non-) Container/CDB Serializes updates and inserts to the SMON_SCN_TIME mapping table
TE KTF broadcast NO (Non-) Container/CDB Serializes broadcasts for flushes to SMON_SCN_TIME
TF Temporary File NO (Non-) Container/CDB Serializes dropping of a temporary file
TH Threshold Chain NO (Non-) Container/CDB Serializes threshold in-memory chain access
TK Auto Task Serialization NO (Non-) Container/CDB Prevents more than one AutoTask Background Slave from being spawned
TL Log Lock NO (Non-) Container/CDB Serializes threshold log table read and update
TO Temp Object NO (Non-) Container/CDB Synchronizes DDL and DML operations on a temp object
TP Runtime Fixed Table Purge NO (Non-) Container/CDB Lock used for fixed runtime table purge and definition
TQ Queue table enqueue NO (Non-) Container/CDB Synchronizes access to queue table
TS Temporary Segment NO (Non-) Container/CDB Serializes accesses to temp segments
TT Tablespace NO (Non-) Container/CDB Serializes DDL operations on tablespaces
TW Cross-Instance Transaction NO (Non-) Container/CDB Lock held by one instance to wait for transactions on all instances to finish
US Undo Segment NO (Non-) Container/CDB Lock held to perform DDL on the undo segment
V Library Cache Lock 3 NO (Non-) Container/CDB Synchronizes accesses to library cache objects
WA AQ Notification Watermark NO (Non-) Container/CDB Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF AWR Flush NO (Non-) Container/CDB This enqueue is used to serialize the flushing of snapshots
WG Write gather local enqueue NO (Non-) Container/CDB Long term lock on wgc file state
WL Being Written Redo Log NO (Non-) Container/CDB Coordinates access to redo log files and archive logs
WM WLM Plan Operations NO (Non-) Container/CDB Synchronizes new WLM Plan activation
WP AWR Purge NO (Non-) Container/CDB This enqueue handles concurrency between purging and baselines
WR LNS archiving log NO (Non-) Container/CDB Coordinates access to logs by Async LNS and ARCH/FG
WS LogWriter Standby NO (Non-) Container/CDB Used by LogWriter to coordinate communication to standby databases
XB ASM Group Block lock NO (Non-) Container/CDB Prevents client diskgroup use during storage reconfiguration
XC XDB Configuration NO (Non-) Container/CDB Synchronization access to XDB configuration
XD Auto Online Exadata disks NO (Non-) Container/CDB Serialize Auto Management of Exadata disks
XH AQ Notification No-Proxy NO (Non-) Container/CDB Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XK GES Test-Lock for KSIRESTXDV NO (Non-) Container/CDB Lock used for internal testing
XL ASM Extent Fault Lock NO (Non-) Container/CDB Keep multiple processes from faulting in the same extent chunk
XM ASM RM Master Lock NO (Non-) Container/CDB RMON which process this lock is the Master
XQ ASM Extent Relocation Enqueue NO (Non-) Container/CDB ASM extent relocation
XR Quiesce / Force Logging NO (Non-) Container/CDB Lock held during database quiesce or for database force logging
XY Internal Test NO (Non-) Container/CDB Lock used for internal testing
Y Library Cache Pin 3 NO (Non-) Container/CDB Synchronizes accesses to the contents of library cache objects
ZA Audit Partition NO (Non-) Container/CDB Lock held for adding partition to Aud table
ZF FGA Partition NO (Non-) Container/CDB Lock held for adding partition to Fga table
ZG File Group NO (Non-) Container/CDB Coordinates file group operations
ZS Spillover Audit files NO (Non-) Container/CDB Lock held for exclusive access on a Spillover audit file
ZZ Global Context Action NO (Non-) Container/CDB Lock held for updating Global context hash tables

You can retrieve the list yourself via following query:

SELECT type, name, is_user, decode(con_id, '0', '(Non-) Container/DB', 1, 'Root DB', 'Pluggable DB') lives_in, description
FROM V$LOCK_TYPE ORDER BY is_user DESC, type;

6 August 2013

Oracle 12c and the world part II

Filed under: Oracle — Venzi @ 10:09
Tags: ,

Here is a link to a great list from The Oracle Alchemist around Oracle 12c articles from the community: http://www.oraclealchemist.com/news/install-oracle-12c-12-1/

5 August 2013

Oracle 12c and the world

Filed under: Oracle — Venzi @ 11:13
Tags: ,

Oracle Database 12c is out and the blogs out there are filled with the new features and improvements in the new version. For me it’s time to revisit some of my previous blog posts and compare them with 12c. Stay tuned….

8 July 2013

Oracle 12c database is out!

Filed under: Oracle — Venzi @ 12:09
Tags: ,

After more than 5 years of development Oracle has released version 12c of its flagship product the database. With a New Features Guide of 128 pages containing 500+ new features this is one of the biggest releases ever. You can download it for Linux and Solaris on Oracle Technology Network.

15 January 2013

Amazon Web Services (AWS) Plug-in for Oracle Enterprise Manager

Filed under: Oracle — Venzi @ 09:54

Oracle has released an Enterprise Manager Plug-In for AWS. This Plug-In enables you to monitor and troubleshoot your cloud-based databases alongside with your on-premise databases with one single tool. Especially in cloud-based environments it can be tricky and time consuming to identify issues with your database. However,  by deploying this Plug-In within your OEM Cloud Control environment, you gain following management features:

  • Monitor EBS, EC2 and RDS instances on Amazon Web Services
  • Gather performance metrics and configuration details for AWS instances
  • Raise alerts and violations based on thresholds set on monitoring
  • Generate reports based on the gathered data

31 December 2012

DTrace is now also available for Oracle Linux

Filed under: Oracle,Performance — Venzi @ 09:42

Oracle has ported DTrace for Oracle Linux. DTrace is a very powerful performance analysis and troubleshooting tool that allows you to instrument all software. It’s name is short for Dynamic Tracing. I’m not a DTrace expert but some say it is that powerful that it allows you to reverse engineer any software…

3 November 2012

Loading data fast – DML error logging performance

Filed under: Code,Java,Oracle,Performance,Programming — Venzi @ 14:49

In the last post of my “Loading data fast” series I showed how DML error logging can be used to prevent batch loads from failing when one or multiple rows can’t be inserted. Now the question is how much performance impact errors have on the mechanism. What I try to prove is that a developer should not just blindly use DML error logging instead of thinking whether the data he intents to insert is primary valid. So let’s have a look:

First I create the same simple table with a primary key on it that I used before:

CREATE TABLE TESTLOADTABLE (id NUMBER, text VARCHAR2(255));
table TESTLOADTABLE created.

CREATE UNIQUE INDEX TESTLOADTABLE_PK ON TESTLOADTABLE(id);
unique index TESTLOADTABLE_PK created.

ALTER TABLE TESTLOADTABLE ADD PRIMARY KEY (id) USING INDEX TESTLOADTABLE_PK;
table TESTLOADTABLE altered.

Then I create the error logging table:

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TESTLOADTABLE','ERR_TESTLOADTABLE');
END;
anonymous block completed

So far so good, now let’s execute following tests:

1) Load 10k rows with no errors
2) Load 10k rows with 100 (1%) rows failing
3) Load 10k rows with 1000 (10%) rows failing
4) Load 10k rows with 2500 (25%) rows failing
5) Load 10k rows with 5000 (50%) rows failing
6) Load 10k rows with 7500 (75%) rows failing
And just out of curiosity let’s see how long a regular insert with no error logging takes
7) Load 10k rows with no errors and no error logging

Test 1 – no errors:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 861
Executing overall took: 1020
Preparation took: 159

So overall it took 1020 milli seconds while the job spent 861 milli seconds in the execution of the insert.

Test 2 – 100 errors (1%):

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i<10000;i++)
{
  if (i%100 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1017
Executing overall took: 1069
Preparation took: 52

This time it took quite a bit long to execute the batch, 1017 milli seconds. The reason for this is obvious. Oracle now has not only to insert 10k rows but also to reject rows and insert them into another table.

Test 3 – 1000 (10%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i<10000;i++)
{
  if (i%10 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1420
Executing overall took: 1470
Preparation took: 50

And as you can see the more rows that are failing, the longer the insert takes. So let’s have a look at the rest of the tests and see how bad it gets:

Test 4 – 2500 (25%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i<10000;i++)
{
  if (i%4 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1877
Executing overall took: 1961
Preparation took: 84

Test 5 – 5000 (50%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i<10000;i++)
{
  if (i%2 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 2680
Executing overall took: 2765
Preparation took: 85

Test 6 – 7500 (75%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i<10000;i++)
{
  if (i<=7500)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 3349
Executing overall took: 3412
Preparation took: 63

So as you can see, the more errors you get, the longer your batch will need to execute. This is only logical of course as the more errors you get, the more exceptions are thrown which then lead to more rows inserted into the error table. The main takeaway is that your insert is not failing and you can smoothly query the error logging table and act appropriately. Just in comparison:

  • Insert no errors: 861ms
  • Insert 1% errors: 1017ms
  • Insert 10% errors: 1420ms
  • Insert 25% errors: 1877ms
  • Insert 50% errors: 2680ms
  • Insert 75% errors: 3349ms

Now let’s do one more test and see how fast a regular insert without DML error logging is. You would think it’s the same but during my experiments if found an interesting fact:

Test 7 – no errors, no error logging:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* regular bulk insert */ INTO testloadtable (id, text) VALUES (?,?)");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 212
Executing overall took: 372
Preparation took: 160

So a regular insert takes only 212ms while a DML error logging insert takes 861ms. That’s 4 times longer!
The reason for this is because of the unpublished bug 11865420 (My Oracle Support Doc Id: 11865420.8). Once you download the patch and update the system accordingly the insert with DML error logging is just as fast as without:

Regular batch insert:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* regular bulk insert */ INTO testloadtable (id, text) VALUES (?,?)");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 248
Executing overall took: 399
Preparation took: 151

DML error logging insert:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i<10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 227
Executing overall took: 384
Preparation took: 157

Conclusion: This is the last article of the series Loading data fast! In this series I’ve not only shown how bad it is to commit after each row (remember Autocommit in JDBC!) but also how much more speed you can get out of your program by doing batch inserts. I’ve also shown how to deal with potential errors during batch inserts and how much performance impact errors produce. The latter I have done to make it clear that developers should not just throw DML error logging inserts at every problem as they will lose the performance benefit again that batch inserts provide. So now, go over your code and batch up! ;)

Next Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.