Tag: Oracle Database

Over 1 and a half year ago I posted the enqueue waits for Oracle 10g. Well, a lot has changed in 11g including the enqueue waits. This is the new list of enqueue waits in 11gR2:

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
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 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 – PST split check	Synchronizes check for Storage (PST) split in disk groups
ASM Enqueue	enq: AM – background COD reservation	Reserve a background COD entry
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
Service Operations	enq: AS – service activation	Synchronizes new service activation
Alter Tablespace	enq: AT – contention	Serializes ‘alter tablespace’ operations
Audit index file	enq: AU – audit index file	lock held to operate on the audit index file
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
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 – 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
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
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 – 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
LogicalStandby	enq: SB – contention	Synchronizes Logical Standby metadata 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) 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 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

For those who made it all to the bottom here a little extra:

While I just found the enqueue waits for 10g on the internet I managed to find out how to retrieve that information. And believe it or not – it’s available just over a simple query:

SELECT 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;

In my previous post I talked about bitmap indexes, where they should be used and where they shouldn’t be used or what you have to keep in mind when using them. Today I show you a example where they are very helpful. Remember I said that bitmap indexes where designed for read intensive environments and that they are useful on columns with low cardinality. Lets assume you have a table for all your employees in your company. The company has three different locations, lets say 1=New York; 2=London; 3=Paris:

SQL> CREATE TABLE employees
(
 empId      NUMBER,
 deptId     NUMBER,
 name       VARCHAR2(50),
 gender     VARCHAR2(6)
);

Table created.

Now I insert lets say 100,000 employees distributed over the three locations. The distribution on the locations is based on a random number out of DBMS_RANDOM. Same for the gender:

SQL> BEGIN
 2    FOR n IN 1..100000 LOOP
 3      INSERT INTO employees VALUES (n, ROUND(DBMS_RANDOM.VALUE(0.5,3.5),0), 'Employee ' || n, DECODE(ROUND(DBMS_RANDOM.VALUE(1,2),0),1,'MALE','FEMALE'));
 4    END LOOP;
 5    COMMIT;
 6  END;
 7  /

PL/SQL procedure successfully completed.

A quick look at the distributions:

SQL> SELECT gender, COUNT(*) FROM employees GROUP BY gender;

GENDER   COUNT(*)
------ ----------
FEMALE      49985
MALE        50015

SQL> SELECT deptId, COUNT(*) from employees GROUP BY deptId;

 DEPTID   COUNT(*)
---------- ----------
 1      33532
 2      33456
 3      33012

So as you can see the distribution is good and the cardinality is “bad”. As the next step I create a normal index on it and look how it behaves.

SQL> CREATE INDEX EMPLOYEES_GENDER_I001 ON employees (gender);

Index created.

SQL> CREATE INDEX EMPLOYEES_DEPT_I001 ON employees (deptId);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

Now lets have a closer look at some selects and their explain plans. First I just count the number of female employees and the number of employees in the New York (deptId=1) office. As I have indexes on both columns Oracle should retrieve the data directly from them – no need to go to the table:

SQL> set autotrace traceonly explain;
SQL> SELECT COUNT(*) FROM employees WHERE gender = 'FEMALE';

Execution Plan
----------------------------------------------------------
Plan hash value: 938208788

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 |     7 |    88  (18)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                       |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| EMPLOYEES_GENDER_I001 | 50108 |   342K|    88  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("GENDER"='FEMALE')

SQL> SELECT COUNT(*) FROM employees WHERE deptId = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3728864332

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |     1 |     3 |    74   (9)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                     |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMPLOYEES_DEPT_I001 | 34186 |   100K|    74   (9)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("DEPTID"=1)

I got an INDEX FAST FULL SCAN and an INDEX RANGE SCAN – fair enough. Costs are 88 and 74. Now what if I have a more complex statement? What if I want to have all male employees who are either in the New York or the London office:

SQL> SELECT COUNT(*) FROM employees WHERE gender = 'MALE' AND deptId IN (1,2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1712853197

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     9 |   177  (16)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES | 34047 |   299K|   177  (16)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("GENDER"='MALE' AND ("DEPTID"=1 OR "DEPTID"=2))

This leads already to a TABLE ACCESS FULL with costs of 177. Now the same story with bitmap indexes on the columns:

SQL> DROP INDEX EMPLOYEES_GENDER_I001;

Index dropped.

SQL> DROP INDEX EMPLOYEES_DEPT_I001;

Index dropped.

SQL> CREATE BITMAP INDEX EMPLOYEES_GENDER_I001 ON employees (gender);

Index created.

SQL> CREATE BITMAP INDEX EMPLOYEES_DEPT_I001 ON employees (deptId);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

First lets do the counts again:

SQL> SELECT COUNT(*) FROM employees WHERE gender = 'FEMALE';

Execution Plan
----------------------------------------------------------
Plan hash value: 3004312514

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |                       |     1 |     7 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |                       | 51145 |   349K|     3   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| EMPLOYEES_GENDER_I001 |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 3 - access("GENDER"='FEMALE')

SQL> SELECT COUNT(*) FROM employees WHERE deptId = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2661849472

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |                     |     1 |     3 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |                     | 34039 |    99K|     3   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| EMPLOYEES_DEPT_I001 |       |       |            |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 3 - access("DEPTID"=1)

As you can see the optimizer chose another plan now for both tables. Instead of an INDEX FAST FULL SCAN and an INDEX RANGE SCAN I got now both times a BITMAP INDEX SINGLE VALUE and a BITMAP CONVERSION COUNT. Costs dropped from 88 and 74 to 3! Now the complexer statement:

SQL> SELECT COUNT(*) FROM employees WHERE gender = 'MALE' AND deptId IN (1,2);

Execution Plan
----------------------------------------------------------
Plan hash value: 597175463

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     1 |     9 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                       |     1 |     9 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                       | 33998 |   298K|     9   (0)| 00:00:01 |
|   3 |    BITMAP AND                 |                       |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | EMPLOYEES_GENDER_I001 |       |       |            |          |
|   5 |     BITMAP OR                 |                       |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| EMPLOYEES_DEPT_I001   |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE| EMPLOYEES_DEPT_I001   |       |       |            |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 4 - access("GENDER"='MALE')
 6 - access("DEPTID"=1)
 7 - access("DEPTID"=2)

I got again another plan and here you can see the power of bitmap indexes now. Oracle takes the department ids and does now a BINARY OR on the bitmap. Out of the result it does a BINARY AND with the gender bitmap and look at the result: The costs dropped from 177 on a FULL TABLE SCAN to 9 on some bitmap operations! So as you can see on this short demonstration bitmap indexes are quite powerful!

In the last few months I got more and more involved into the Data Warehouse side. This is actually not too bad – learning some new stuff brings some change into the daily routine. So on this post I tell something about BITMAP indexes as I just had this subject in work. Normally I don’t write about indexes as Richard Foote is way better in that but however:

Bitmap indexes got introduced into Oracle with 7.3 but are currently only available in the Enterprise and Personal Edition. So why did I mention the Data Warehouse above? Well, because BITMAP indexes are designed for them and ad hoc query environments. Usually you use them on columns with low cardinality. However they are NOT designed for OLTP environments where a lot of data manipulation happens in many concurrent sessions. Reason for that is because you will face some locking issues but more on that later. So how does a BITMAP index look like? The index stores a bitmap for a single value on all the rows. Lets take the emp table for example (I took here the example from Tom Kyte‘s book Expert Oracle Database Architecture site 448). I create a bitmap index on the job column. You can have a lot of employees but the jobs will keep within a limit so you will have a low cardinality there. Your index would look like this:

Value/Row	1	2	3	4	5	6	7	8	9	10	11	12	13	14
ANALYST	0	0	0	0	0	0	0	1	0	1	0	0	1	0
CLERK	1	0	0	0	0	0	0	0	0	0	1	1	0	1
MANAGER	0	0	0	1	0	1	1	0	0	0	0	0	0	0
PRESIDENT	0	0	0	0	0	0	0	0	1	0	0	0	0	0
SALESMAN	0	1	1	0	1	0	0	0	0	0	0	0	0	0

So instead of a normal B*Tree index you don’t get a new index entry with every value pointing to the rowid but you have a bitmap for each single value and Oracle puts either a 0 or an 1 in there for a specific row. One thing to mention here: BITMAP indexes also store NULL values as 0. So why is there an issue with locking on concurrent sessions? The reason for that is that one single bitmap entry points to many rows. In the above example thing about the CLERK entry. One company will have many clerks but you have only one index entry with the underlying bitmap. Oracle cannot lock individual bits in a bitmap index entry, it has to lock the specific entry with the entire bitmap. Any other session which also tries to update the same index entry at the same time will be locked until the first session does a commit or rollback! So lets have a look into this:

First I create a new table. The table has just one column “departementID”. Lets stay at the example with the employees. We got many employees which belong to 3 different departments. Normally you would have columns like name and so forth but for this I concentrate on the departmentId:

SQL> CREATE TABLE BITMAPTEST (departmentId NUMBER) TABLESPACE DATA1;

Table created.

Now I add a bitmap index on the departmentId. All I have to do is to put the word BITMAP between CREATE and INDEX:

SQL> CREATE BITMAP INDEX BITMAPTEST_BI001 ON BITMAPTEST (departmentId);

Index created.

So, now I have two sessions:

Session 1 has the SID 1539
Session 2 has the SID 1470

Now session 1 inserts a row with the first departmentId:

SESS1 – 1539:

SQL> INSERT INTO BITMAPTEST VALUES (1);

1 row created.

No commit so far. Now a second session inserts a new row with the same department – another employee who started at the same department:

SESS2 – 1470:

SQL> INSERT INTO BITMAPTEST VALUES (1);

The insert is locked with a “enq: TX – row lock contention until the first session commits or does a rollback:

SQL> SELECT sid FROM v$session_wait WHERE event = 'enq: TX - row lock contention';

SID
----------
1470

After I do a commit the lock is released:

SESS1 – 1539:

 SQL> COMMIT;

Commit complete.

The second session can now create the row:

SESS2 – 1470:

1 row created.

As I said before the lock is only on the bitmap of the specific index entry. So if session 1 (session 2 did not commit or rollback yet) tries now to insert another row on another department everything is fine:

SESS1 – 1539:

SQL> INSERT INTO BITMAPTEST VALUES (2);

1 row created.

What does that mean on an OLTP environment? As an OLTP environment is very write/modification intensive system concurrent sessions will lock each other out when they try do modify a bitmap index entry. Here a small test:

I take again the bitmaptest table with the departmentId. Now I insert 1,000 rows with 3 different department IDs like this:

DECLARE
departmentID  NUMBER:=1;
maxLoop       NUMBER := 1000;
BEGIN
FOR n1 IN 1..maxLoop LOOP
IF departmentID = 4 THEN
departmentID := 1;
END IF;
INSERT INTO bitmapTest VALUES (departmentID);
departmentID := departmentID+1;
END LOOP;
COMMIT;
END;
/

Note: The commit happens after the loop. So all other sessions will have to wait on this one to finish before they can start.

I run this PL/SQL now in 100 concurrent sessions over a shell script:

processes=100;
i=0;

while [ $i -le $processes ];
do
runInserts.sh &
i=`expr $i + 1`;
done;

Now lets see what happens. To monitor the locks I just do a simple SQL on v$session_wait. I should see there lots of waits with “enq: TX – row lock contention”. So I run the test and what I get is:

SQL> SELECT count(*), event, state, wait_class
FROM v$session_wait
GROUP BY event, state, wait_class
ORDER BY 1 DESC;

COUNT(*) EVENT                                                            STATE               WAIT_CLASS
---------- ---------------------------------------------------------------- ------------------- -----------------
136 SQL*Net message from client                                      WAITING             Idle
90 enq: TX - row lock contention                                    WAITING             Application
12 rdbms ipc message                                                WAITING             Idle
1 buffer busy waits                                                WAITED SHORT TIME   Concurrency
1 smon timer                                                       WAITING             Idle
1 wait for unread message on broadcast channel                     WAITING             Idle
1 Streams AQ: qmn coordinator idle wait                            WAITING             Idle
1 SQL*Net message to client                                        WAITED SHORT TIME   Network
1 pmon timer                                                       WAITING             Idle
1 Streams AQ: qmn slave idle wait                                  WAITING             Idle
1 Streams AQ: waiting for messages in the queue                    WAITING             Idle

11 rows selected.

As you can see I have 90 sessions with row lock contention. When I reexecute this statement several times the amount of sessions reduces just slowly as just one session is running after each other:

SQL> r
1  SELECT count(*), event, state, wait_class
2   FROM v$session_wait
3    GROUP BY event, state, wait_class
4*    ORDER BY 1 DESC

COUNT(*) EVENT                                                            STATE               WAIT_CLASS
---------- ---------------------------------------------------------------- ------------------- -----------------
136 SQL*Net message from client                                      WAITING             Idle
86 enq: TX - row lock contention                                    WAITING             Application
12 rdbms ipc message                                                WAITING             Idle
1 buffer busy waits                                                WAITED SHORT TIME   Concurrency
1 smon timer                                                       WAITING             Idle
1 wait for unread message on broadcast channel                     WAITING             Idle
1 Streams AQ: qmn coordinator idle wait                            WAITING             Idle
1 SQL*Net message to client                                        WAITED SHORT TIME   Network
1 pmon timer                                                       WAITING             Idle
1 Streams AQ: qmn slave idle wait                                  WAITING             Idle
1 Streams AQ: waiting for messages in the queue                    WAITING             Idle

11 rows selected.

SQL> r
1  SELECT count(*), event, state, wait_class
2   FROM v$session_wait
3    GROUP BY event, state, wait_class
4*    ORDER BY 1 DESC

COUNT(*) EVENT                                                            STATE               WAIT_CLASS
---------- ---------------------------------------------------------------- ------------------- -----------------
136 SQL*Net message from client                                      WAITING             Idle
83 enq: TX - row lock contention                                    WAITING             Application
12 rdbms ipc message                                                WAITING             Idle
1 buffer busy waits                                                WAITED SHORT TIME   Concurrency
1 smon timer                                                       WAITING             Idle
1 wait for unread message on broadcast channel                     WAITING             Idle
1 Streams AQ: qmn coordinator idle wait                            WAITING             Idle
1 SQL*Net message to client                                        WAITED SHORT TIME   Network
1 pmon timer                                                       WAITING             Idle
1 Streams AQ: qmn slave idle wait                                  WAITING             Idle
1 Streams AQ: waiting for messages in the queue                    WAITING             Idle

11 rows selected.

So when I pick out one of those sessions I can have a look on what I am waiting on just be doing a select on v$session and all_objects:

SQL> SELECT count(*), s.row_wait_obj#
FROM v$session s, v$session_wait w
WHERE s.sid=w.sid
AND w.event = 'enq: TX - row lock contention'
GROUP BY s.row_wait_obj#;
COUNT(*) ROW_WAIT_OBJ#
---------- -------------
96       7154476

96 sessions waiting on the object with the ID 7154476 which is

SQL> SELECT object_name, object_type FROM all_objects WHERE object_id = 7154476;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
BITMAPTEST_I001                INDEX

as you can see my BITMAP index! So what does this mean? As I wrote already before: BITMAP index are designed for data warehouse or read intensive environments to speed up queries where you have a low cardinality on it. As Tom Kyte already wrote in his book: Low cardinality always depends on the data. In a table with 10,000 rows a low cardinality might be 10 while in a 10 million table 10,000 might be a low cardinality! It always depends, there is no cut-and-dried answer for this. You simply have to try it out. If you want to use a BITMAP index in an OLTP environment be aware of the locking! There might be valid cases to use them inside there as for example on the “gender” column of a “employees” table to speed up reports. It always depends if the table you load is heavily modified or not and if you load the table concurrent or not!

Sources:

• Expert Oracle Database Architecture by Thomas Kyte (ISBN: 1-59059-530-0) – Chapter 11 – Site 448 Bitmap Indexes