Default Blame Acceptor

http://www.confio.com/logicalread/solving-oracle-enq-tm-contention-wait-events/#.U38OZXJdXko

Recently, I was assisting one of our customers of Ignite for Oracle trying to diagnose sessions waiting on the "enq: TM - contention" event. The blocked sessions were executing simple INSERT statements similar to:

INSERT INTO supplier VALUES (:1, :2, :3);

Waits on enq: TM - contention indicate there are unindexed foreign key constraints. Reviewing the SUPPLIER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed by the Top Objects feature of Ignite for Oracle because all the time was associated with the PRODUCT table. We added the index on the column referencing the PRODUCT table and the problem was solved.

Finding the root cause of the enq: TM - contention wait event

After using Ignite for Oracle's locking feature to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the SUPPLIER table several times a week. As a result, rows from the SUPPLIER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.

Reproducing a typical problem that leads to this wait

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the issue to see what happens under the covers. I first created a subset of the tables from this customer and loaded them with sample data.

CREATE TABLE supplier 
( supplier_id number(10) not null, 
supplier_name varchar2(50) not null, 
contact_name varchar2(50), 
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 
); 
INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1'); 
INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2'); 
COMMIT; 

CREATE TABLE product 
( product_id number(10) not null, 
product_name varchar2(50) not null, 
supplier_id number(10) not null, 
CONSTRAINT fk_supplier 
FOREIGN KEY (supplier_id) 
REFERENCES supplier(supplier_id) 
ON DELETE CASCADE ); 
INSERT INTO product VALUES (1, 'Product 1', 1); 
INSERT INTO product VALUES (2, 'Product 2', 1); 
INSERT INTO product VALUES (3, 'Product 3', 2); 
COMMIT;

I then executed statements similar to what we found at this customer:

User 1: DELETE supplier WHERE supplier_id = 1; 
User 2: DELETE supplier WHERE supplier_id = 2;
User 3: INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type 
FROM v$lock l, dba_objects o, v$session s 
WHERE UPPER(s.username) = UPPER('&User') 
AND l.id1 = o.object_id (+) 
AND l.sid = s.sid 
ORDER BY sid, type;

SID	BLOCK	EVENT	TYPE	MODE	REQ	OBJECT	OBJECT_TYPE
42		SQL*Net message from client	TM	3	0	PRODUCT	TABLE
42		SQL*Net message from client	TM	3	0	SUPPLIER	TABLE
42		SQL*Net message from client	TX	6	0		TABLE
54	42	enq: TM - contention	TM	3	0	SUPPLIER	TABLE
54	42	enq: TM - contention	TM	0	5	PRODUCT	TABLE
83	54	enq: TM - contention	TM	3	0	SUPPLIER	TABLE
83	54	enq: TM - contention	TM	0	2	PRODUCT	TABLE

Following along with the solution we used for our customer, we added an index for the foreign key constraint on the SUPPLIER table back to the PRODUCT table:

CREATE INDEX fk_supplier ON product (supplier_id);

When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Sample query to find unindexed foreign key constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

============================

spool /tmp/chktm.log 

set long 655360 

set pagesiz 100 

select dbms_metadata.get_ddl('TABLE','PORTFOLIOTYPE','WFPROD_PERMAL') from dual; 

select index_name, table_name, column_name, column_position from dba_ind_columns 

where table_owner='WFPROD_PERMAL' and table_name='PORTFOLIOTYPE'; 

select a.owner owner_name, a.table_name table_name, 

b.owner owner_nam, b.table_name referencing_table 

from dba_constraints a, dba_constraints b 

where a.constraint_name = b.r_constraint_name and 

b.constraint_type = 'R' and 

a.table_name='PORTFOLIOTYPE'; 

spool off; 

===========================

Performance Monitoring Data Dictionary Views

This section lists some of the data dictionary views that you can use to monitor an Oracle Database instance. These views are general in their scope. Other views, more specific to a process, are discussed in the section of this book where the process is described.

View	Description
V$LOCK	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS	Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS	Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS	Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system
V$SESSION_WAIT	Lists the resources or events for which active sessions are waiting
V$SYSSTAT	Contains session statistics
V$RESOURCE_LIMIT	Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH	Contains statistics for nonparent latches and summary statistics for parent latches

Identifying Locking Issues 
Note: text in this session was copied from toadworld.com


1 Query to show information about locked objects:
SELECT O.OWNER, O.OBJECT_ID, O.OBJECT_NAME, O.OBJECT_TYPE, L.TYPE
FROM DBA_OBJECTS O, V$LOCK L
WHERE O.OBJECT_ID = L.ID1
/



OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYP TY
---------- ---------- -------------------- ---------- --
SYSTEM          32392 FOOBAR               TABLE      TM
SYSTEM          32392 FOOBAR               TABLE      TM
...
SYSTEM          32371 MY_DEPT              TABLE      TM

2 DBA_BLOCKERS

The DBA_BLOCKERS view has one column, HOLDING_SESSION, which indicates each session that is:

• Not currently waiting on a locked object, and
• Currently holding a lock on an object for which another session is waiting.

3 DBA_WAITERS

The DBA_WAITERS view is the counterpart to DBA_BLOCKERS. It indicates which sessions are:

• Currently waiting on a locked object, and
• Not currently holding a lock on an object for which another session is waiting.

The WAITING_SESSION column indicates the SID of the waiting session, and the HOLDING_SESSION column indicates the SID of the holding session. The view shows the lock type, and the modes in which the lock is being held and requested.

============================

TOM KYTe scritp to find Unindexed Foreign Key constraint columns.

tkyte@TKYTE816> select table_name, constraint_name, 2 cname1 || nvl2(cname2,','||cname2,null) || 3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || 4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || 5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) 6 columns 7 from ( select b.table_name, 8 b.constraint_name, 9 max(decode( position, 1, column_name, null )) cname1, 10 max(decode( position, 2, column_name, null )) cname2, 11 max(decode( position, 3, column_name, null )) cname3, 12 max(decode( position, 4, column_name, null )) cname4, 13 max(decode( position, 5, column_name, null )) cname5, 14 max(decode( position, 6, column_name, null )) cname6, 15 max(decode( position, 7, column_name, null )) cname7, 16 max(decode( position, 8, column_name, null )) cname8, 17 count(*) col_cnt 18 from (select substr(table_name,1,30) table_name, 19 substr(constraint_name,1,30) constraint_name, 20 substr(column_name,1,30) column_name, 21 position 22 from user_cons_columns ) a, 23 user_constraints b 24 where a.constraint_name = b.constraint_name 25 and b.constraint_type = 'R' 26 group by b.table_name, b.constraint_name 27 ) cons 28 where col_cnt > ALL 29 ( select count(*) 30 from user_ind_columns i 31 where i.table_name = cons.table_name 32 and i.column_name in (cname1, cname2, cname3, cname4, 33 cname5, cname6, cname7, cname8 ) 34 and i.column_position <= cons.col_cnt 35 group by i.index_name 36 ) 37 / ================================================== Toms unindex is giving me false negative four stars. In my opinion fk_idx is satisfying the unindex need. At least TM Enq Wait events dismished from the environment after creating the index with different order of columns than fk. Here is an alternative approach to search for unindexed foreign keys implemented in a single sql clause. It is operational at least with Oracle 10.2.0.1 and 11.1.0.7. There exists a bug involved with usage of connect_by_root in versions 10.2.0.3, 10.2.0.4 and 11.1.0.6. For those versions you need to set the _optimizer_connect_by_cost_based parameter to false. alter session set "_optimizer_connect_by_cost_based" = false; select case when i.index_name is not null then 'OK' else '****' end ok , c.table_name , c.constraint_name , c.cols,i.index_name from ( select table_name, constraint_name , max(sys_connect_by_path(column_name, ' ' )) cols from ( select a.table_name , b.constraint_name , column_name , position , row_number() over (partition by b.constraint_name order by column_name) rn from user_constraints a, user_cons_columns b where a.constraint_name = b.constraint_name and a.constraint_type = 'R' ) start with rn = 1 connect by prior rn = rn-1 and prior constraint_name = constraint_name group by table_name, constraint_name ) c left outer join ( select table_name , index_name , cr , max(sys_connect_by_path(column_name, ' ' )) cols from ( select table_name , index_name , column_position , column_name , cr , row_number() over (partition by index_name, cr order by column_name) rn from ( select table_name , index_name , column_position , column_name , connect_by_root(column_name) cr from user_ind_columns connect by prior column_position-1 = column_position and prior index_name = index_name ) ) start with rn = 1 connect by prior rn = rn-1 and prior index_name = index_name and prior cr = cr group by table_name, index_name, cr ) i on c.cols = i.cols and c.table_name = i.table_name ; ================================================== Wait events Oracle 11g – enqueues Posted on January 16, 2013 Enqueues – are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views. In case your session is slow you can always check what it’s waiting for in v$session table. This query just shows sessions waiting for enqueue resources SELECT sid, serial#, event, wait_class FROM v$session WHERE event LIKE 'enq:%'; To get description of an enqueue resource use following query. SELECT eq_name "Enqueue", 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; Enqueue name 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 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 Amdu Dump Allow only one AMDU dump when block read failure 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 disk based alloc/dealloc Synchronizes disk based allocations/deallocations 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 reserved Check id1 of call for specific purpose 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 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 Global Transaction Branch enq: BB – 2PC across RAC instances 2PC distributed transaction branch across RAC instances 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 clonedb bitmap file access enq: BM – clonedb bitmap file write synchronizes clonedb bitmap file operations 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 Calibration enq: CA – contention Synchronizes various IO calibration runs 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 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 syncrhonize database mount/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 operation Represents an active disk online operation 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 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 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 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 NID enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running 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 Instance Recovery enq: IR – contention Synchronizes instance recovery Instance Recovery enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate 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 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 Materialized View Log DDL enq: MD – contention Lock held during materialized view log DDL statements 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 – contention Lock used to coordinate media recovery with other uses of datafiles 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 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 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 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 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 – 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 – 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 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 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 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 – 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 – 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 – 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 – Test access/locking Testing redo transport access/locking Being Written Redo Log enq: WL – contention Coordinates access to redo log files and archive logs 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 Global Context Action enq: ZZ – update hash tables lock held for updating global context hash tables Have a fun  =============================