Classes of Wait Events
Every wait event
belongs to a class of wait event. The following list describes each of the wait
classes.
Administrative
Waits resulting
from DBA commands that cause users to wait (for example, an index rebuild)
Application
Waits resulting
from user application code (for example, lock waits caused by row level locking
or explicit lock commands)
Cluster
Waits related to Real
Application Clusters resources (for example, global cache resources such as 'gc
cr block busy')
Commit
This wait class
only comprises one wait event - wait for redo log write confirmation after a
commit (that is, 'log file sync')
Concurrency
Waits for internal
database resources (for example, latches)
Configuration
Waits caused by
inadequate configuration of database or instance resources (for example,
undersized log file sizes, shared pool size
Idle
Waits that signify
the session is inactive, waiting for work (for example, 'SQL*Net message from
client')
Network
Waits related to
network messaging (for example, 'SQL*Net more data to dblink')
Other
Waits which should
not typically occur on a system (for example, 'wait for EMON to spawn')
Scheduler
Resource Manager
related waits (for example, 'resmgr: become active')
System I/O
Waits for
background process I/O (for example, DBWR wait for 'db file parallel write')
User I/O
Waits for user I/O
(for example 'db file sequential read')
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.
look at
the Top 5 Timed Events section at the beginning of the Automatic Workload
Repository report. This section automatically orders the wait events (omitting
idle events), and calculates the relative percentage:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
%
Total
Event
Waits Time (s) Call Time
-------------------------------------- ------------
----------- ---------
1. CPU time 559
88.80
2. log file parallel write 2,181 28 4.42
3. SQL*Net more data from client 516,611 27 4.24
4. db file parallel write 13,383 13 2.04
5. db file sequential read 563 2 .27
In some
situations, there might be a few events with similar percentages. This can
provide extra evidence if all the events are related to the same type of
resource request (for example, all I/O related events).
Look at
the number of waits for these events, and the average wait time. For example,
for I/O related events, the average time might help identify whether the I/O
system is slow. The following example of this data is taken from the Wait Event
section of the AWR report:
Total
Wait Avg wait Waits
Event Waits Timeouts
Time (s) (ms) /txn
--------------------------- --------- ---------
---------- ------ ---------
1. log file parallel write 2,181 0 28
13 41.2
2. SQL*Net more data from clie 516,611 0 27
0 9,747.4
3. db file parallel write 13,383 0 13
1 252.5
No comments:
Post a Comment