Log Buffer Space? RO Enqueue? In Oracle, queries pass through hundreds of internal database processes called Oracle Wait Events. Understanding Wait Events helps DBAs get a full picture of their Oracle operation.
Commonly encountered wait events include:
Block Change Tracking Buffer—Related to the sizing / usage of the CTWR dba buffer in the Large Pool memory structure.
Buffer Busy Wait—This wait event happens when a session tries to access a block in the buffer cache but it can’t because the buffer is busy, that is another session is modifying the block and the contents of the block are in flux.
Control File Reads and Writes—The three different wait events of ‘control file sequential read’, ‘control file single write’, and ‘control file parallel write’ all contribute to the amount of time Oracle takes to keep the control file current. Through normal operation the control file is continuously hammered with reads and writes as it is being updated.
Db File Sequential Read—There are two things you can do to these waits: optimize the SQL statement or reduce the average wait time.
Direct Path Read Temp—When a session reads buffers from disk directly into the PGA, the wait is on direct path read temp. This is closely related to the direct path read wait.
Enq: TX – row lock contention—A wait for the Oracle TX enqueue in mode 6 (row lock contention) is a common enqueue wait, and occurs when a transaction tries to update or delete rows that are currently locked by another transaction.
Enq: TM – index contention—Waits on enq: TM – contention in Oracle indicate there are unindexed foreign key constraints. In this article, I examine how foreign key constraints relate to this wait event and how to tune for this event.
File Open—This wait event happens when the database needs to open a file. Wait time is recorded starting just prior to when the open request is issued and extends until the time the request is returned, having succeeded or failed, from the operating system.
Index Block Split—As applications, users or sessions request rows from a table, Oracle may determine which index access path is best for finding rows in a table. If another session is inserting or updating data during this index lookup process, which in turn causes updates to that index and requires an index block split, the first session must wait on that index block split until finished. This is the index block split wait event.
Latch Free—This wait event occurs when a session needs a latch, tries to get the latch, but fails because someone else has it. When this happens, the session sleeps with a wait on latch free, wakes up and tries again. The time it was asleep is the wait time for “latch free.”
Locking and Enqueue Waits—Oracle enqueue waits indicate that a seesion is waiting for a lock that is held by another user (or sessions). For Oracle 9i, there is a specific Enqueue wait event. For Oracle 10g and later, the Enqueue wait event has been separated into more than 200 unique wait events, which each include more specific information about the related lock type.
Log Buffer Space—This wait event occurs when server processes write data into the log buffer faster than the LGWR process can write it out.
Log File Switch Completion—As data is manipulated and altered in an Oracle database, time must be spent recording and archiving these changes through redo log generation. The subsequent wait is known as Oracle log file switch completion.The performance impact of switching too many redo logs is the culprit.
Log File Sync—This wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.
Oracle RAC Wait Events— Monitoring an Oracle RAC database often means monitoring this situation and the amount of requests going back and forth over the RAC interconnect. The most common wait events related to this are gc cr request and gc buffer busy.
PL/SQL Lock Timer—This wait event represents the amount of time a user or application has “slept” through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures. The PL/SQL lock timer event is worth watching because it can indicate issues with application response, throughput and possibly coding.
Ready by Other Session—When a session waits on the “read by other session” event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer.
RO Enqueue—This wait event is used to synchronize the work required between a foreground process and background process such as DBWR or CKPT. It is most often seen when dropping objects or truncating tables.
Row Cache Lock Wait—In order for DDL (Data Definition Language) to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows.
A good beginning on wait events. You did not provide the basic definition of what a wait event is, for the uninitiated.