by Terry Sutton and Roger Schrag
Database Specialists, Inc.

About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.

Introduction

The wait event interface has continued to be an invaluable tool for DBAs as it offers both breadth and depth in the information it provides to aid in troubleshooting and boosting system performance. Throughout this paper we will assume the reader is familiar with wait event concepts and the wait event interface in Oracle. In particular, this paper is designed for DBAs who have experience using the wait event facility in Oracle 9i or earlier and want to learn what enhancements have been made in Oracle 10g. (Those new to this area of Oracle technology might want to first read our paper entitled, “Interpreting Wait Events to Boost System Performance” available for free download at http://www.dbspecialists.com/presentations.html#wait_events.)

There are still significant gaps in the documentation as of Oracle 10g release 10.1.0.3, making it that much harder to learn what has changed in Oracle 10g with respect to wait events and the wait event interface. For example, the Oracle 10g Database Reference manual (part number B10755-01) still provides the wait events list from Oracle 9i. For this reason, you might find the Appendix listed at the end of this paper to be useful. It lists all of the wait event names for Oracle 10g release 10.1.0.3, along with the parameter names for each.

Although introduced in Oracle 7, not much changed in the wait event interface through and including Oracle 9i. Oracle 7.3 had just 106 wait events, while that number has increased to over 400 in Oracle 9i. Also, Oracle 9i TKPROF reports include wait event information and Oracle 9i v$ views show wait times in microseconds. But these are minor enhancements, really. Oracle 10g, on the other hand, brings more significant change to the wait event interface than we have seen in years. Now there are over 800 wait events and names are more descriptive, wait events are categorized into classes, several v$ views have been added, helpful columns have been added to existing v$ views, built-in statistics collection by Active Session History and the Automatic Workload Repository has been introduced, a new time model concept for looking at how sessions spend their time has appeared, and improvements have been made to the session tracing facility.

Wait Event Enhancements in Oracle 10g

In this section, we will introduce what we see as the top dozen areas in which wait events and the wait event interface have been enhanced in Oracle 10g. We’ve listed these enhancements in no particular order.

More Descriptive Wait Event Names

Prior to Oracle 10g, some wait event names were quite vague and not very useful without looking at the parameter values for a specific occurrence of the event. For example, an enqueue wait could indicate various situations ranging from contention for a row in a table to waiting on a user-defined lock. Wait event names in Oracle 10g are more descriptive in the areas of latches, enqueues, and buffer busy waits.

There is still a latch free wait event in Oracle 10g, but there are also 26 more specific latch-related events. These cover the most common latches that experience contention. In the past, if we saw a session waiting on the latch free event, we would have output like the following:

SQL> SELECT event, state, p1, p2, p3
  2  FROM   v$session_wait
  3  WHERE  sid = 162;

EVENT         STATE            P1      P2    P3
------------- ------- -----------  ------ -----
latch free    WAITING 15113593728      97     5

We would then have to query v$event_name to determine the meaning of the parameters p1, p2, and p3:

SQL> SELECT * FROM v$event_name WHERE name = 'latch free';

EVENT# NAME       PARAMETER1      PARAMETER2      PARAMETER3
------ ---------- --------------- --------------- ---------------
     3 latch free address         number          tries

And, seeing that p2 is the latch number, we would need to query v$latch to find out which latch was being waited upon:

SQL> SELECT name
  2  FROM   v$latch
  3  WHERE  latch# = 97;

NAME
--------------------
cache buffers chains

In Oracle 10g we simply see:

SQL> SELECT event, state
  2  FROM   v$session_wait
  3  WHERE  sid = 162;

EVENT                          STATE  
------------------------------ -------
latch: cache buffers chains    WAITING

The descriptive event name saves us two steps in determining which latch is causing the wait. The more detailed description enables one to more quickly drill down to the root cause of the wait.

The names of enqueue-related wait events have also been made more descriptive in Oracle 10g. There is no longer an enqueue wait event—it has been replaced by 184 events with more detailed names. In the past, if sessions were waiting on enqueues, we would have to decode the type of lock from the p1 parameter:

SQL> SELECT event, state, seconds_in_wait siw
  2  FROM   v$session_wait
  3  WHERE  sid = 96;

EVENT                               STATE                      SIW
----------------------------------- ------------------- ----------
enqueue                             WAITING                     24

SQL> SELECT sid,
  2         CHR (BITAND (p1,-16777216) / 16777215) ||
  3         CHR (BITAND (p1, 16711680) / 65535) enq,
  4         DECODE (CHR (BITAND (p1,-16777216) / 16777215) ||
  5                 CHR (BITAND (p1, 16711680) / 65535),
  6                   'TX', 'Transaction (RBS)',
  7                   'TM', 'DML Transaction',
  8                   'TS', 'Tablespace and Temp Seg',
  9                   'TT', 'Temporary Table',
 10                   'ST', 'Space Mgt (e.g., uet$, fet$)',
 11                   'UL', 'User Defined',
 12                   CHR (BITAND (p1,-16777216) / 16777215) ||
 13                   CHR (BITAND (p1, 16711680) / 65535)) enqueue_name,
 14         DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
 15                   3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
 16                   6, 'Exclusive', 'Other') lock_mode
 17  FROM   v$session_wait
 18  WHERE  sid = 96;

  SID ENQ  ENQUEUE_NAME                   LOCK_MODE
----- ---- ------------------------------ ----------
   96 TX   Transaction (RBS)              Exclusive

In Oracle 10g we get more information directly from the enqueue name:

SQL> SELECT event, state, seconds_in_wait siw
  2  FROM   v$session_wait
  3  WHERE  sid = 143;

EVENT                               STATE                      SIW
----------------------------------- ------------------- ----------
enq: TX - row lock contention       WAITING                    495

Additionally, more information is available in some cases from the p1, p2, and p3 parameters, whose meanings vary with the different enqueue-related wait events. (See the Appendix for a listing of all wait events and their associated parameter meanings.)

In addition to latch and enqueue waits, there are a few other wait events that have more descriptive names in Oracle 10g. However, the state of the Oracle 10g documentation as of this writing makes it hard to enumerate all of them. One example of another descriptive name change has to do with buffer busy waits. The situation where one session is waiting for another session to read in a desired data block from disk (reason code 130) has been given the more descriptive wait event name “read by other session.”

Wait Event Classes

In Oracle 10g wait events are classified into categories which can help the DBA to more easily determine the likely root cause of the wait. The categories are:

Administrative	Idle
Application	Network
Cluster	Scheduler
Commit	System I/O
Concurrency	User I/O
Configuration	Other

While nearly 70% of the wait events are in the “Other” category (557 out of 811 in release 10.1.0.3), the most frequently encountered ones are in wait classes with helpful names. Let’s look at the wait class designations of some enqueue events as an example:

SQL> SELECT   wait_class, name
  2  FROM     v$event_name
  3  WHERE    name LIKE 'enq%'
  4  AND      wait_class <> 'Other'
  5  ORDER BY wait_class;

WAIT_CLASS                     NAME
------------------------------ ----------------------------------------
Administrative                 enq: TW - contention
Administrative                 enq: DB - contention
Application                    enq: PW - flush prewarm buffers
Application                    enq: RO - contention
Application                    enq: RO - fast object reuse
Application                    enq: TM - contention
Application                    enq: TX - row lock contention
Application                    enq: UL - contention
Concurrency                    enq: TX - index contention
Configuration                  enq: ST - contention
Configuration                  enq: TX - allocate ITL entry
Configuration                  enq: SQ - contention
Configuration                  enq: HW - contention

We see that TX enqueues (row locks) and TM enqueues (table locks) are in the Application class, which makes sense since these wait events generally occur because of application behavior. Meanwhile, ST (space management), HW (high-water mark extension), and SQ (sequence number) enqueues are in the Configuration class, as these can usually be alleviated by changes in object and database settings.

The User I/O class includes the db file scattered read, db file sequential read, direct path read, and direct path write events as one might expect, while the System I/O class includes many waits related to reading and writing of redo logs and archive logs. The Commit class has one member, log file sync, as that wait is caused by commits. And the Idle class is made up of various wait events which have traditionally been considered “idle events,” such as SQL*Net message from client. It should be noted that sometimes such “idle events” can actually be symptoms of the root cause of poor performance, so they should not be disregarded without consideration.

In general, the addition of wait classes helps direct the DBA more quickly toward the root cause of performance problems.

v$ View Enhancements

In Oracle 10g there are quite a few new v$ views that pertain to wait events, and helpful new columns have been added to existing v$ views. We will discuss several of the enhancements in this section, although some new v$ views are part of major new Oracle 10g functionality and will be discussed in separate sections later on.

v$event_name

Three columns have been added to the v$event_name view in Oracle 10g: wait_class_id, wait_class#, and wait_class. These columns show which wait class the wait event is part of. We saw in the previous section how this new information might be used. The columns in v$event_name now are:

SQL> DESCRIBE v$event_name
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EVENT#                                             NUMBER
 EVENT_ID                                           NUMBER
 NAME                                               VARCHAR2(64)
 PARAMETER1                                         VARCHAR2(64)
 PARAMETER2                                         VARCHAR2(64)
 PARAMETER3                                         VARCHAR2(64)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)

v$sql and v$sqlarea

The v$sql and v$sqlarea views have six new columns in Oracle 10g that relate to wait events:

application_wait_time

concurrency_wait_time

cluster_wait_time

user_io_wait_time

plsql_exec_time

java_exec_time

These columns are designed to identify the amount of time a SQL statement spends in PL/SQL or Java code execution, or waiting in four specific wait classes. The Oracle release 10.1.0.3 documentation provides almost no information concerning these new columns, though they could be extremely important in diagnosing performance problems. In the absence of documentation, we will use an example to demonstrate the behavior and value of these new columns.

Suppose we create a table called testtab with about a million rows. We then run the following statement from one session without committing, and then run the same statement from another session:

SQL> UPDATE testtab SET numcol = numcol + 1 WHERE ROWNUM < 1000;

Obviously the second session will wait on an enqueue wait event until the first session either commits or rolls back. After a while we roll back the first session and then the second session. Next, in a third session, we run the following statement:

SQL> UPDATE testtab SET numcol = numcol + 1;

The instance we’re using has a small buffer cache, so quite a bit of physical I/O is caused by the statement. After the UPDATE completes we look at v$sqlarea:

SQL> SELECT sql_id, application_wait_time appl, concurrency_wait_time concurr,
  2         user_io_wait_time user_io   
  3  FROM   v$sqlarea
  4  WHERE  sql_text LIKE 'UPDATE testtab SET numcol%';

SQL_ID             APPL   CONCURR     USER_IO    
------------- --------- --------- -----------
038m56cp4am0c 178500000         0       20000
fd5mxhdbf09ny         0     10000   105040000

SQL> SELECT sql_id, sql_text
  2  FROM   v$sqlarea
  3  WHERE  sql_id IN ('fd5mxhdbf09ny','038m56cp4am0c');

SQL_ID        SQL_TEXT
------------- -------------------------------------------------------------
038m56cp4am0c UPDATE testtab SET numcol = numcol + 1 WHERE ROWNUM < 1000
fd5mxhdbf09ny UPDATE testtab SET numcol = numcol + 1

So we see that the first statement (locking rows) spent 178.5 seconds (178,500,000 microseconds) waiting on events in the Application wait class and 0.02 seconds waiting on events in the User I/O wait class. If we recall from the discussion of wait classes, the TX enqueue for row contention is in the Application wait class, and data file reads are in the User I/O wait class. The second statement, which required more disk reads, shows 105 seconds of User I/O waits and a very small amount of concurrency waits.

While the currently available documentation from Oracle does not provide much information about these new columns in v$sql and v$sqlarea, they appear to hold much promise for diagnosing query performance problems.

v$session_wait_history

Up through Oracle 9i, the v$ views show us only the most recent wait event for each session. Even though wait times can accumulate to greatly slow down a process, many waits are very short (from a human perspective) individually. So, it’s often difficult to grab information on a wait event as it is happening. The v$session_wait_history view, new in Oracle 10g, helps by showing the last ten wait events each session has experienced As an example, the following query shows the ten most recent wait events for session 154:

SQL> SELECT   sid, seq#, event, wait_time, p1, p2, p3 
  2  FROM     v$session_wait_history
  3  WHERE    sid = 154 
  4  ORDER BY seq#;

SID SEQ# EVENT                     WAIT_TIME     P1     P2     P3
--- ---- ------------------------ ---------- ------ ------ ------
154    1 db file sequential read          28      4   3547      1
154    2 log buffer space                 18      0      0      0
154    3 log buffer space                 36      0      0      0
154    4 db file sequential read           0      4   3559      1
154    5 db file sequential read           0      4   1272      1
154    6 db file sequential read           0      4   3555      1
154    7 log buffer space                  9      0      0      0
154    8 db file sequential read           0      4   3551      1
154    9 db file sequential read           6      4   1268      1
154   10 log buffer space                  8      0      0      0

The seq# column is supposed to show the chronological sequence of the wait events, with 1 being the most recent wait event in the session. On our release 10.1.0.3 databases on Solaris, the seq# column behaves differently—making it difficult to tell which wait event is the most recent. In any case, note that this seq# value differs from the seq# column in v$session, which is incremented for each wait experienced by the session throughout the life of the session.

In the above query, we see that the session’s most recent waits alternated between single-block disk reads and log buffer space. This makes sense, since the SQL that the session was performing looked like:

INSERT INTO table1 (column1, column2) 
SELECT column1, column2
FROM   table2
WHERE  ...

From this list of recent waits, we can also drill down to get more detail. The p1 and p2 values for db file sequential read indicate the file and block numbers being read, so we can quickly determine what segment was being read.

The columns in v$session_wait_history are:

SQL> DESCRIBE v$session_wait_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SEQ#                                               NUMBER
 EVENT#                                             NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 WAIT_TIME                                          NUMBER
 WAIT_COUNT                                         NUMBER

v$session

The v$session view has been enhanced in Oracle 10g with several new columns which are quite useful. The wait event columns from v$session_wait have been added to v$session. In previous releases of Oracle, to get more detailed information about a session experiencing waits (such as what SQL the waiting session is executing), we had to join v$session_wait with v$session, as in:

SQL> SELECT s.sid, w.state, w.event, w.seconds_in_wait siw,
  2         s.sql_address, s.sql_hash_value hash_value, w.p1, w.p2, w.p3
  3  FROM   v$session s, v$session_wait w
  4  WHERE  s.sid = w.sid
  5  AND    s.sid = 154;

In Oracle 10g we can get all of this information from v$session:

SQL> SELECT sid, state, event, seconds_in_wait siw,
  2         sql_address, sql_hash_value hash_value, p1, p2, p3
  3  FROM   v$session
  4  WHERE  sid = 154;

SID STATE   EVENT                   SIW SQL_ADDRESS      HASH_VALUE  P1   P2  P3
--- ------- ----------------------- --- ---------------- ---------- --- ---- ---
154 WAITING db file sequential read   1 000000038551E820 3625097388   4 9813   1

Two additional columns have been added to v$session that are helpful for wait event analysis: blocking_session and blocking_session_status. The blocking_session column contains the session id (SID) of the holder of the resource that the waiting session is waiting for. The blocking_session_status column indicates the validity of the contents of the blocking_session column. If blocking_session_status is VALID, a valid SID is present in the blocking_session column. In the past, if we saw that a session was waiting for a resource, we would have to do additional queries to determine who was holding the resource. If the resource was an enqueue lock, we would have to query v$lock (sometimes a very expensive query) to determine who was holding the lock. Now we can find out who is holding the lock much more quickly:

SQL> SELECT sid, blocking_session, username,
  2         blocking_session_status status
  3  FROM   v$session
  4  WHERE  blocking_session_status = 'VALID';

SID BLOCKING_SESSION USERNAME STATUS
--- ---------------- -------- -----------
154              157 TSUTTON  VALID

If we combine this with the wait event information now available in v$session, we see:

SQL> SELECT sid, blocking_session, username,
  2         event, seconds_in_wait siw
  3  FROM   v$session
  4  WHERE  blocking_session_status = 'VALID';

SID BLOCKING_SESSION USERNAME EVENT                          SIW
--- ---------------- -------- ------------------------------ ---
154              157 TSUTTON  enq: TX - row lock contention  318

v$event_histogram

The v$system_event view shows the number of waits, total time waited, and average wait time for a given wait event name (system-wide since instance startup). However, this aggregation can cloud the picture, because a small number of long waits can skew the data. For example, consider the following query from v$system_event:

SQL> SELECT event, total_waits, time_waited, average_wait
  2  FROM   v$system_event 
  3  WHERE  event = 'enq: TX - row lock contention';

EVENT                         TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
----------------------------- ----------- ----------- ------------
enq: TX - row lock contention       17218     2101966          122

We see that there have been 17,218 waits and that the average wait time was 1.22 seconds, but we have no idea how the wait times are distributed. Were all of these waits roughly the same length? Were most of them under one second long and a few really long waits threw off the average? We can’t tell from v$system_event. However, in Oracle 10g we can look at v$event_histogram for a more complete picture:

SQL> SELECT event, wait_time_milli, wait_count
  2  FROM   v$event_histogram 
  3  WHERE  event = 'enq: TX - row lock contention';

EVENT                         WAIT_TIME_MILLI WAIT_COUNT
----------------------------- --------------- ----------
enq: TX - row lock contention               1        833
enq: TX - row lock contention               2        635
enq: TX - row lock contention               4        372
enq: TX - row lock contention               8        395
enq: TX - row lock contention              16        781
enq: TX - row lock contention              32       3729
enq: TX - row lock contention              64       3050
enq: TX - row lock contention             128        410
enq: TX - row lock contention             256         47
enq: TX - row lock contention             512         46
enq: TX - row lock contention            1024         37
enq: TX - row lock contention            2048          3
enq: TX - row lock contention            4096       6880

We see that very few of the waits were anywhere near 1.22 seconds. Nearly 60% of the waits were less than 0.128 seconds (with most of those between 16 milliseconds and 64 milliseconds), and most of the remaining waits were between 2.048 seconds and 4.096 seconds (at which point some timed out and started new waits). In this way, the v$event_histogram view gives us a more accurate picture of wait times summarized by event name.

The columns of v$event_histogram are:

SQL> DESCRIBE v$event_histogram
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EVENT#                                             NUMBER
 EVENT                                              VARCHAR2(64)
 WAIT_TIME_MILLI                                    NUMBER
 WAIT_COUNT                                         NUMBER

v$system_wait_class and v$session_wait_class

The two new views v$system_wait_class and v$session_wait_class enable us to get system and session wait information summarized by wait classes. This gives us a higher level view of what is happening in the system or session, rather than focusing on individual events. The views are roughly equivalent to the views v$system_event and v$session_event, except that they roll up the events by wait class. The wait times are expressed in centiseconds since instance startup for v$system_wait_class and centiseconds since session connection for v$session_wait_class.

The following queries show how much time (in centiseconds) has been spent on waits in each class across the system since instance start and for one specific session since that session began:

SQL> SELECT   wait_class, time_waited
  2  FROM     v$system_wait_class
  3  ORDER BY time_waited DESC;

WAIT_CLASS    TIME_WAITED
------------- -----------
Idle            777450022
System I/O        1261584
User I/O           116667
Configuration      116481
Application         72301
Other               12432
Commit               3496
Concurrency           319
Network                 1

SQL> SELECT   wait_class, time_waited
  2  FROM     v$session_wait_class
  3  WHERE    sid = 154
  4  ORDER BY time_waited DESC;

WAIT_CLASS    TIME_WAITED
------------- -----------
Idle               612453
User I/O             1500
Configuration          28
Commit                 11
Other                   0
Application             0
Network                 0

Since the wait times shown in these views are aggregations since system or session startup, these views are best used by taking samples and comparing the results to determine waits over a period of time. For instance, you could get data for the entire instance at time T1:

DROP TABLE swc_snap;
CREATE TABLE swc_snap
AS
SELECT wait_class, total_waits, time_waited
FROM   v$system_wait_class;

And then, at time T2 a while later, get a summary of waits between T1 and T2:

SELECT   a.wait_class, (a.time_waited - b.time_waited) tm_waited
FROM     v$system_wait_class a, swc_snap b 
WHERE    a.wait_class = b.wait_class
AND      a.total_waits > NVL (b.total_waits, 0)   
ORDER BY tm_waited DESC;

WAIT_CLASS       TM_WAITED
--------------- ----------
Idle                255767
Application            171
System I/O             156
User I/O                44
Other                   21
Commit                  13
Network                  1

Active Session History

In previous releases of Oracle, the detailed information displayed in v$session_wait could prove extremely helpful in diagnosing performance problems—if you queried the view at the right time. The v$session_wait_history view in Oracle 10g makes it a little easier to catch detailed information by preserving the last ten waits for each session. But what if you want detailed information about a session’s waits for a period further back in time? This is where the Active Session History feature of Oracle 10g—ASH for short—comes in handy. ASH makes detailed information about a sampling of waits encountered by all sessions available to us for a very long time.

In Oracle 10g, a new background daemon process called MMNL queries v$session once each second and stores information about all active sessions in a circular buffer in memory accessible by a new view called v$active_session_history. How far back you can look at sessions in this view depends on session activity and how much memory Oracle allocated for ASH. Oracle’s goal is to keep at least a few hours of session data available in this view. The v$active_session_history view includes much of the detailed wait event information shown in v$session:

SQL> DESCRIBE v$active_session_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAMPLE_ID                                          NUMBER
 SAMPLE_TIME                                        TIMESTAMP(3)
 SESSION_ID                                         NUMBER
 SESSION_SERIAL#                                    NUMBER
 USER_ID                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 SQL_PLAN_HASH_VALUE                                NUMBER
 SQL_OPCODE                                         NUMBER
 SERVICE_HASH                                       NUMBER
 SESSION_TYPE                                       VARCHAR2(10)
 SESSION_STATE                                      VARCHAR2(7)
 QC_SESSION_ID                                      NUMBER
 QC_INSTANCE_ID                                     NUMBER
 EVENT                                              VARCHAR2(64)
 EVENT_ID                                           NUMBER
 EVENT#                                             NUMBER
 SEQ#                                               NUMBER
 P1                                                 NUMBER
 P2                                                 NUMBER
 P3                                                 NUMBER
 WAIT_TIME                                          NUMBER
 TIME_WAITED                                        NUMBER
 CURRENT_OBJ#                                       NUMBER
 CURRENT_FILE#                                      NUMBER
 CURRENT_BLOCK#                                     NUMBER
 PROGRAM                                            VARCHAR2(48)
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 CLIENT_ID                                          VARCHAR2(64)

As you can see, v$active_session_history captures the essential wait-related data from v$session. It also captures useful information about the SQL statement currently being executed, as well as current object number, file, and block being accessed. When a wait that was sampled by ASH completes, Oracle fills in the time_waited column for the row in v$active_session_history with the actual duration of the wait.

The Automatic Workload Repository, which we will discuss in the next section, writes data from v$active_session_history to disk at regular intervals, preserving one sample every ten seconds from each active session. So, active session information remains accessible—although with less detail—even after the data has aged out of v$active_session_history.

Because ASH is always “on,” you always have access to detailed information about waits encountered in sessions within the last few hours. This means that if a user complains about a performance problem, you may be able to query v$active_session_history and gain insight into the problem without having to initiate an extended SQL trace or start a close watch of v$session while they reproduce the problem.

An important thing to keep in mind about v$active_session_history, however, is that it is populated by sampling v$session once each second. A session may encounter many different waits during a one second period, but only the one wait that was in progress when ASH collected its sample will be recorded in v$active_session_history. For this reason, ASH is valuable for general aggregate queries but not for precise counting of individual events or determining minimum or maximum wait times. Statistically speaking, the data collected by ASH is probably more accurate over a larger time interval and/or number of sessions.

For example, you might query v$active_session_history to see what percentage of time over the last two hours a particular group of sessions spent waiting on disk reads. However, using this view to determine how many disk read waits a session encountered in the last minute probably will not yield very accurate results.

Even though ASH data is only a sampling of active sessions, the information can prove to be quite useful. For example, the following query shows that sessions running the ARXENV application over the last two hours encountered a great deal of row-level lock contention:

SQL> SELECT   DECODE (session_state, 'WAITING', event, NULL) event,
  2           session_state, COUNT(*), SUM (time_waited) time_waited
  3  FROM     v$active_session_history
  4  WHERE    module = 'ARXENV'
  5  AND      sample_time > SYSDATE - (2/24)
  6  GROUP BY DECODE (session_state, 'WAITING', event, NULL),
  7           session_state;

EVENT                          SESSION_STATE COUNT(*) TIME_WAITED
------------------------------ ------------- -------- -----------
                               ON CPU             124           0
log file sync                  WAITING              2       52686
db file scattered read         WAITING              2       28254
db file sequential read        WAITING              1        6059
control file sequential read   WAITING              1        9206
SQL*Net break/reset to client  WAITING              1        9140
enq: TX - row lock contention  WAITING            922   930864016

In addition to running queries against the v$active_session_history view, you can use Enterprise Manager to run reports that will display ASH data.

Although ASH runs on all Oracle 10g databases by default, you are not allowed to query the v$active_session_history view (or run the corresponding reports in Enterprise Manager) unless you have purchased the Diagnostic Pack.

Automatic Workload Repository

Oracle 10g includes another significant facility relevant to the wait event interface. It’s known as the Automatic Workload Repository—or AWR for short. AWR is basically a next-generation Statspack. By default, AWR collects an hourly snapshot of database performance information, storing the data in tables in the SYS schema. AWR is configured automatically when you create an Oracle 10g database. You can call the dbms_workload_repository package to collect a snapshot on demand, purge a snapshot or range of snapshots, or change the snapshot interval or retention period. (By default snapshots are collected at the top of each hour and are purged after seven days.)

AWR collects the same type of data that Statspack does—including system-level statistics, resource-intensive SQL, and of course instance-wide wait event information. AWR also collects data that is new for Oracle 10g, such as time model statistics (which we will discuss in the next section). As an aside, the Oracle 10g version of Statspack also collects a lot of this new information, including time model statistics.

You can generate an AWR report of database activity between two snapshots by running the awrrpt.sql script in the rdbms/admin directory under $ORACLE_HOME. This script offers reports formatted as plain text or HTML. The reports will look familiar if you have used Statspack before. You can use Enterprise Manager to generate AWR reports as well.

AWR offers many benefits over Statspack. For one, it is more tightly integrated into the Oracle kernel, reducing resource requirements and overhead when collecting snapshots. AWR snapshots also include ASH data from v$active_session_history, providing session-level information to complement the system-level data collection familiar to Statspack users.

Data collected by AWR is made easily accessible via views with names that start DBA_HIST. This enables you to write your own reports that extract just the data you need to address a specific situation, if for some reason you don’t find what you need in the standard AWR report. For example, the following query displays the two most recent snapshot IDs:

SQL> SELECT snap_id, begin_interval_time, end_interval_time
  2  FROM   (
  3         SELECT   snap_id, begin_interval_time, end_interval_time
  4         FROM     dba_hist_snapshot
  5         ORDER BY end_interval_time DESC
  6         )
  7  WHERE  ROWNUM <= 2;

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
       362 10-MAR-05 04.00.02.018 PM 10-MAR-05 05.00.36.581 PM
       361 10-MAR-05 03.00.25.885 PM 10-MAR-05 04.00.02.018 PM

Just like ASH, AWR runs on all Oracle 10g databases by default. Also like ASH, you are not allowed to query the AWR views (or run AWR reports) unless you have licensed the Diagnostic Pack. Because AWR consumes system resources when collecting snapshots and uses up storage in the SYSAUX tablespace, you may want to disable the collection of AWR snapshots if you are not licensed to use AWR. This may be done by using the dbms_workload_repository package. If AWR is not available to you, Statspack is still a good way to go in Oracle 10g.

Time Model Statistics

Oracle 10g introduces a new concept called Time Model Statistics. This information provides yet another way to see how time is spent, and with greater detail than was available previously. The v$sys_time_model view shows time model statistics for the entire system since instance startup, while the v$sess_time_model view shows time model statistics for each session since session start. The columns in these two views are as follows:

SQL> DESCRIBE v$sys_time_model
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 STAT_ID                                           NUMBER
 STAT_NAME                                         VARCHAR2(64)
 VALUE                                             NUMBER

SQL> DESCRIBE v$sess_time_model
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 SID                                               NUMBER
 STAT_ID                                           NUMBER
 STAT_NAME                                         VARCHAR2(64)
 VALUE                                             NUMBER

A sample query from v$sys_time_model shows the following:

SQL> SELECT   stat_name, value / 1000000 seconds
  2  FROM     v$sys_time_model
  3  ORDER BY seconds DESC;

STAT_NAME                                           SECONDS
------------------------------------------------ ----------
DB time                                           80970.190
sql execute elapsed time                          75057.271
DB CPU                                            44448.628
background elapsed time                           29333.160
PL/SQL execution elapsed time                      8824.538
background cpu time                                5170.311
parse time elapsed                                 1270.147
hard parse elapsed time                             838.068
PL/SQL compilation elapsed time                     176.731
sequence load elapsed time                          112.334
connection management call elapsed time              44.644
failed parse elapsed time                            11.946
hard parse (sharing criteria) elapsed time            5.579
hard parse (bind mismatch) elapsed time               4.610
failed parse (out of shared memory) elapsed time      0.000
Java execution elapsed time                           0.000
inbound PL/SQL rpc elapsed time                       0.000

This query shows us a lot more information about how Oracle sessions have spent their time (categorically) than v$sysstat and v$sesstat do. Of course, we have to know how to interpret this information before we can put it to work for us. Values in these views are shown in microseconds, and they do not include background processes unless “background” appears in the statistic name. The “DB time” statistic shows elapsed time spent on database calls (user processes only). This amounts to time spent on the CPU or waiting on non-idle wait events. For a description of the other time model statistics, see the v$sess_time_model view listing in the Oracle 10g Database Reference manual.

From this query, among many other useful facts, we can see that no time has been spent executing Java, very little time has been spent hard parsing or compiling PL/SQL, background processes have used about 10% of the CPU time, and about 11% of the elapsed time for user sessions was spent on PL/SQL execution.

Tracing Facility Improvements

The extended SQL trace facility, also known as event 10046, allows us to capture in a trace file detailed information about every wait event encountered by a database session. This feature has been available in Oracle for a long time. However, Oracle 10g offers some helpful improvements in this area.

Enabling extended SQL trace has always been a bit of a nuisance. In earlier releases of Oracle, you had to use a clumsy ALTER SESSION SET EVENTS statement or—worse yet—call the undocumented dbms_system.set_ev procedure to set the 10046 event in another user’s session. In Oracle 8i the dbms_support package was introduced to make this step easier, but the package was missing from many releases of Oracle and usually was not installed by default.

Oracle 10g introduces the new dbms_monitor package. This package, among many other things, makes it very easy to turn extended SQL trace on and off in any Oracle session. With one easy to remember call, you can turn extended SQL trace on or off, with wait events and/or bind variables captured in the trace file:

SQL> DESCRIBE dbms_monitor
...
PROCEDURE SESSION_TRACE_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     BINARY_INTEGER          IN     DEFAULT
 SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT
PROCEDURE SESSION_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     BINARY_INTEGER          IN     DEFAULT
 SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT

If the session_id parameter is not specified or set to NULL, your own session will be traced. Thus, the following two statements should be equivalent:

• ALTER SESSION SET events '10046 trace name context forever, level 12';

• EXECUTE dbms_monitor.session_trace_enable (waits=>TRUE, binds=>TRUE);

In Oracle 9i and earlier, extended SQL trace was easy to use if your application connected to the Oracle database via a dedicated server connection. If the shared server architecture was used, each shared server process that serviced a request from the session being traced would write its data to a separate trace file. Furthermore, tracing sessions in a connection pool environment became difficult because one Oracle session could actually process requests for many different end-user sessions.

The dbms_monitor package in Oracle 10g addresses this problem. Instead of enabling extended SQL trace for a specific Oracle session, you can enable it for a specific client identifier or combination of service, module, and action. Any time any Oracle session has the specified client identifier or combination of service, module, and action, the session will be traced. Sessions can set or clear their client identifier at will by calling the dbms_session package, and they can set their module and action by calling the dbms_application_info package.

Suppose a web-based application uses a pool of 30 database connections to serve user requests and maintains a current_sessions table to keep track of the state of each end-user session. When a user clicks a button in their browser window, the application server receives the HTTP request and hands it off to an application server process. The application server process grabs a free database connection from the pool and accesses the database as necessary to service the request. It is likely that subsequent requests from the same user will be processed using different database connections.

In Oracle 9i and earlier it would have been very difficult to trace the one user’s database activity in this environment. The user’s database accesses are spread among multiple Oracle server processes in the connection pool. Moreover, each of those Oracle processes is handling requests from many different end users.

With dbms_monitor in Oracle 10g, the application could be modified in a way to make extended SQL trace a whole lot easier. We mentioned that the application uses the current_sessions table to maintain state for each end user session. Each time the application server grabs a database connection from the pool, it could set the client identifier for the Oracle session to the session_id from the current_sessions table before doing any database access for that end user session. Then the application could clear the client identifier before returning the database connection to the pool. The Oracle calls could look like this:

EXECUTE dbms_session.set_identifier ('session_id174837492748');
...do the work for this end user session...
EXECUTE dbms_session.clear_identifier

To trace this end user session, we could now call the dbms_monitor package like this:

SQL> EXECUTE dbms_monitor.client_id_trace_enable -
>            ('session_id174837492748', waits=>TRUE, binds=>TRUE);

This call to dbms_monitor will cause each Oracle process to write extended SQL trace data to a trace file for all calls that occur while the client identifier for the session is set to the specified value. However, each Oracle process will write to its own trace file. This will cause the trace data to be split over multiple files. To address this problem, Oracle 10g provides a new command-line utility called trcsess. The trcsess utility reads multiple trace files and consolidates entries from the various files that meet the specified criteria into one trace file that can be processed by TKPROF. To consolidate the trace data for our current example, we could use the following commands:

$ cd $ORACLE_BASE/admin/$ORACLE_SID/udump
$ trcsess output=/home/rschrag/case1403/case1403-trial1.trc \
          clientid=session_id174837492748

In this way the dbms_monitor package and trcsess utility in Oracle 10g make it a lot easier to collect extended SQL trace data from an end user’s session when connection pooling or other session aggregation techniques are used by the application server tier.

Conclusion

Oracle 10g includes many enhancements to the wait event interface that should make performance management using wait event methodologies easier than ever. Some enhancements, such as descriptive wait event names, wait classes, and the session_trace_enable procedure in the dbms_monitor package, are simple conveniences that make our jobs easier. These enhancements don’t give us any information or power that we didn’t have before. Other enhancements, however, such as time model statistics, v$event_histogram, and new columns in v$sql and v$sqlarea, provide us with helpful timing and wait information that was previously unavailable.

Although as of this writing documentation on these new features is incomplete and discussion on Metalink is surprisingly sparse, many of these enhancements will likely prove quite valuable to the Oracle DBA responsible for performance management of a complex Oracle system.

About the Authors

Terry Sutton, OCP, has been an Oracle DBA for eleven years, and has worked in the information technology area for 18 years. Since 2000, Terry has been a Senior Staff Consultant at Database Specialists, performing assignments ranging from production database administration to emergency troubleshooting with a particular focus on Oracle database performance tuning. You may contact Terry by email at tsutton@dbspecialists.com.

Roger Schrag, OCP, has been an Oracle DBA and application architect for over 15 years. He began his career at Oracle Corporation on the Oracle Financials development team. In 1995, he founded Database Specialists, Inc., a boutique consulting firm specializing in Oracle database technology, remote administration, and performance tuning. Since that time, Roger has focused his expertise in the area of performance optimization. Roger is a frequent speaker at Oracle OpenWorld and the International Oracle Users Group (IOUG) Live conferences, where he has frequently been voted in the Top 10% of speakers. Roger has been an Oracle Masters Class instructor for the IOUG and is Director of Conference Programming for the Northern California Oracle Users Group (NoCOUG). He can be reached at rschrag@dbspecialists.com.

Still Looking for Help on this Subject?

Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
Complimentary Newsletter
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.

Appendix: Oracle 10g Wait Event Names

Name	Wait Class	P1	P2	P3
alter rbs offline	Administrative
alter system set dispatcher	Administrative	waited
AQ Deallocate Wait	Configuration
AQ Proxy Cleanup Wait	Idle
ARCH random i/o	System I/O
ARCH sequential i/o	System I/O
ARCH wait for archivelog lock	Other
ARCH wait for flow-control	Network
ARCH wait for net re-connect	Network
ARCH wait for netserver detach	Network
ARCH wait for netserver init 1	Network
ARCH wait for netserver init 2	Network
ARCH wait for netserver start	Network
ARCH wait for pending I/Os	System I/O
ARCH wait for process death 1	Other
ARCH wait for process death 2	Other
ARCH wait for process death 3	Other
ARCH wait for process death 4	Other
ARCH wait for process death 5	Other
ARCH wait for process start 1	Other
ARCH wait for process start 2	Other
ARCH wait for process start 3	Other
ARCH wait for process start 4	Other
ARCH wait on ATTACH	Network
ARCH wait on c/f tx acquire 1	Other
ARCH wait on c/f tx acquire 2	Other
ARCH wait on DETACH	Network
ARCH wait on SENDREQ	Network
ASM background running	Other
ASM background starting	Other
ASM background timer	Idle
ASM db client exists	Other
ASM mount : wait for heartbeat	Administrative
ASM PST query : wait for [PM][grp][0] grant	Cluster
Backup: sbtbackup	Administrative
Backup: sbtclose	Administrative
Backup: sbtclose2	Administrative
Backup: sbtcommand	Administrative
Backup: sbtend	Administrative
Backup: sbterror	Administrative
Backup: sbtinfo	Administrative
Backup: sbtinfo2	Administrative
Backup: sbtinit	Administrative
Backup: sbtinit2	Administrative
Backup: sbtopen	Administrative
Backup: sbtpcbackup	Administrative
Backup: sbtpccancel	Administrative
Backup: sbtpccommit	Administrative
Backup: sbtpcend	Administrative
Backup: sbtpcquerybackup	Administrative
Backup: sbtpcqueryrestore	Administrative
Backup: sbtpcrestore	Administrative
Backup: sbtpcstart	Administrative
Backup: sbtpcstatus	Administrative
Backup: sbtpcvalidate	Administrative
Backup: sbtread	Administrative
Backup: sbtread2	Administrative
Backup: sbtremove	Administrative
Backup: sbtremove2	Administrative
Backup: sbtrestore	Administrative
Backup: sbtwrite	Administrative
Backup: sbtwrite2	Administrative
BFILE check if exists	Other
BFILE check if open	Other
BFILE closure	Other
BFILE get length	Other
BFILE get name object	Other
BFILE get path object	Other
BFILE internal seek	Other
BFILE open	Other
BFILE read	User I/O
block change tracking buffer space	Other
buffer busy	Other	group#	obj#	block#
buffer busy waits	Concurrency	file#	block#	class#
buffer deadlock	Other	dba	class*10+mode	flag
buffer dirty disabled	Other	group#
buffer exterminate	Other	file#	block#	buf_ptr
buffer freelistbusy	Other	group#	obj#	block#
buffer invalidation wait	Other	group#	obj#	block#
buffer latch	Other	latch addr	chain#
buffer pool resize	Administrative	buffer pool id	current size	new size
buffer read retry	User I/O	file#	block#
buffer rememberlist busy	Other	group#	obj#	block#
buffer resize	Other
buffer write wait	Other	group#	obj#	block#
buffer writeList full	Other	group#	obj#	block#
CGS skgxn join retry	Other	retry count
CGS wait for IPC msg	Other
change tracking file parallel write	Other	blocks	requests
change tracking file synchronous read	Other	block#	blocks
change tracking file synchronous write	Other	block#	blocks
check CPU wait times	Other
checkpoint advanced	Other	group#
checkpoint completed	Configuration
class slave wait	Other	slave id
Cluster stablization wait	Other
Cluster Suspension wait	Other
contacting SCN server or SCN lock master	Cluster
control file heartbeat	Other
control file parallel write	System I/O	files	block#	requests
control file sequential read	System I/O	file#	block#	blocks
control file single write	System I/O	file#	block#	blocks
cr request retry	Other	file#	block#
Data Guard broker: wait upon ORA-12850 error	Other	waiting for retrying the query to mask ORA-12850 error
db file parallel read	User I/O	files	blocks	requests
db file parallel write	System I/O	requests	interrupt	timeout
db file scattered read	User I/O	file#	block#	blocks
db file sequential read	User I/O	file#	block#	blocks
db file single write	User I/O	file#	block#	blocks
DBFG waiting for reply	Other
DBMS_LDAP: LDAP operation	Other
debugger command	Other
dedicated server timer	Network	wait event
DFS db file lock	Other	file#
DFS lock handle	Other	type|mode	id1	id2
DIAG dummy wait	Other
direct path read	User I/O	file number	first dba	block cnt
direct path read temp	User I/O	file number	first dba	block cnt
direct path write	User I/O	file number	first dba	block cnt
direct path write temp	User I/O	file number	first dba	block cnt
dispatcher listen timer	Network	sleep time
dispatcher shutdown	Other	waited
dispatcher timer	Idle	sleep time
DLM lock cancel	Other	le
DLM lock cvt S	Other	group	obj#	block#
DLM lock cvt X	Other	group	obj#	block#
DLM lock esc	Other	group	obj#	block#
DLM lock esc X	Other	group	obj#	block#
DLM lock open	Other	group	obj#	block#
DLM lock open S	Other	group	obj#	block#
DLM lock open X	Other	group	obj#	block#
DLM recovery lock convert	Other	group	obj#	block#
DLM recovery lock open	Other	group	obj#	block#
dma prepare busy	Other	group	obj#	block#
dupl. cluster key	Other	dba
enq: AD - allocate AU	Other	name|mode	group and disk number	AU number
enq: AD - deallocate AU	Other	name|mode	group and disk number	AU number
enq: AF - task serialization	Other	name|mode	task id	0
enq: AG - contention	Other	name|mode	workspace #	generation
enq: AO - contention	Other	name|mode	workspace #	object #
enq: AS - contention	Other	name|mode	0	0
enq: AT - contention	Other	name|mode	0	0
enq: AW - AW generation lock	Other	name|mode	operation	workspace #
enq: AW - AW state lock	Other	name|mode	operation	workspace #
enq: AW - AW$ table lock	Other	name|mode	operation	workspace #
enq: AW - user access for AW	Other	name|mode	operation	workspace #
enq: BR - file shrink	Other	name|mode	operation	file #
enq: BR - proxy-copy	Other	name|mode	operation	file #
enq: CF - contention	Other	name|mode	0	operation
enq: CI - contention	Other	name|mode	opcode	type
enq: CL - compare labels	Other	name|mode	object #	0
enq: CL - drop label	Other	name|mode	object #	0
enq: CM - gate	Other	name|mode	disk group #	type
enq: CM - instance	Other	name|mode	disk group #	type
enq: CT - change stream ownership	Other	name|mode	operation	operation parm
enq: CT - CTWR process start/stop	Other	name|mode	operation	operation parm
enq: CT - global space management	Other	name|mode	operation	operation parm
enq: CT - local space management	Other	name|mode	operation	operation parm
enq: CT - reading	Other	name|mode	operation	operation parm
enq: CT - state	Other	name|mode	operation	operation parm
enq: CT - state change gate 1	Other	name|mode	operation	operation parm
enq: CT - state change gate 2	Other	name|mode	operation	operation parm
enq: CU - contention	Other	name|mode	handle	handle
enq: DB - contention	Administrative	name|mode	EnqMode	0
enq: DD - contention	Other	name|mode	disk group	type
enq: DF - contention	Other	name|mode	0	file #
enq: DG - contention	Other	name|mode	disk group	type
enq: DL - contention	Other	name|mode	object #	0
enq: DM - contention	Other	name|mode	type	type
enq: DN - contention	Other	name|mode	0	0
enq: DP - contention	Other	name|mode	0	0
enq: DR - contention	Other	name|mode	0	0
enq: DS - contention	Other	name|mode	0	0
enq: DT - contention	Other	name|mode	0	0
enq: DV - contention	Other	name|mode	object #	0
enq: DX - contention	Other	name|mode	transaction entry #	0
enq: FA - access file	Other	name|mode	disk group number	file number
enq: FB - contention	Other	name|mode	tablespace #	dba
enq: FC - open an ACD thread	Other	name|mode	disk group	thread
enq: FC - recover an ACD thread	Other	name|mode	disk group	thread
enq: FD - Flashback coordinator	Other	name|mode	Internal	Internal
enq: FD - Flashback on/off	Other	name|mode	Internal	Internal
enq: FD - Marker generation	Other	name|mode	Internal	Internal
enq: FD - Tablespace flashback on/off	Other	name|mode	Internal	Internal
enq: FG - FG redo generation enq race	Other	name|mode	disk group	type
enq: FG - LGWR redo generation enq race	Other	name|mode	disk group	type
enq: FG - serialize ACD relocate	Other	name|mode	disk group	type
enq: FL - Flashback database log	Other	name|mode	Log #	zero
enq: FL - Flashback db command	Other	name|mode	Log #	zero
enq: FM - contention	Other	name|mode	0	0
enq: FR - contention	Other	name|mode	disk group	unused
enq: FS - contention	Other	name|mode	0	type
enq: FT - allow LGWR writes	Other	name|mode	disk group	thread
enq: FT - disable LGWR writes	Other	name|mode	disk group	thread
enq: FU - contention	Other	name|mode	0	0
enq: HD - contention	Other	name|mode	disk group	0
enq: HP - contention	Other	name|mode	tablespace #	dba
enq: HQ - contention	Other	name|mode	object #	hash value
enq: HV - contention	Other	name|mode	object #	0
enq: HW - contention	Configuration	name|mode	table space #	block
enq: IA - contention	Other	name|mode	0	0
enq: ID - contention	Other	name|mode	0	0
enq: IL - contention	Other	name|mode	object #	0
enq: IM - contention for blr	Other	name|mode	pool #	0
enq: IR - contention	Other	name|mode	0	0/1
enq: IR - contention2	Other	name|mode	0	0/1
enq: IS - contention	Other	name|mode	0	type
enq: IT - contention	Other	name|mode	object #	0
enq: JD - contention	Other	name|mode	0	0
enq: JI - contention	Other	name|mode	view object #	0
enq: JQ - contention	Other	name|mode	0	0
enq: JS - contention	Other	name|mode	service ID	queue type
enq: JS - coord post lock	Other	name|mode	service ID	queue type
enq: JS - coord rcv lock	Other	name|mode	service ID	queue type
enq: JS - global wdw lock	Other	name|mode	service ID	queue type
enq: JS - job chain evaluate lock	Other	name|mode	service ID	queue type
enq: JS - job recov lock	Other	name|mode	service ID	queue type
enq: JS - job run lock - synchronize	Other	name|mode	service ID	queue type
enq: JS - q mem clnup lck	Other	name|mode	service ID	queue type
enq: JS - queue lock	Other	name|mode	service ID	queue type
enq: JS - running job cnt lock	Other	name|mode	service ID	queue type
enq: JS - running job cnt lock2	Other	name|mode	service ID	queue type
enq: JS - running job cnt lock3	Other	name|mode	service ID	queue type
enq: JS - slave enq get lock1	Other	name|mode	service ID	queue type
enq: JS - slave enq get lock2	Other	name|mode	service ID	queue type
enq: KK - context	Other	name|mode	0	redo thread
enq: KM - contention	Other	name|mode	type	type
enq: KP - contention	Other	name|mode	0	0
enq: KT - contention	Other	name|mode	plan #	0
enq: MD - contention	Other	name|mode	master object #	0
enq: MH - contention	Other	name|mode	0	0
enq: ML - contention	Other	name|mode	0	0
enq: MN - contention	Other	name|mode	session ID	0
enq: MR - contention	Other	name|mode	0 or file #	type
enq: MS - contention	Other	name|mode	master object #	0
enq: MW - contention	Other	name|mode	Schedule Id	0
enq: OC - contention	Other	name|mode	1	2
enq: OL - contention	Other	name|mode	hash value	0
enq: OQ - xsoq*histrecb	Other	name|mode	resource id	0
enq: OQ - xsoqhiAlloc	Other	name|mode	resource id	0
enq: OQ - xsoqhiClose	Other	name|mode	resource id	0
enq: OQ - xsoqhiFlush	Other	name|mode	resource id	0
enq: OQ - xsoqhistrecb	Other	name|mode	resource id	0
enq: PD - contention	Other	name|mode	property name	key hash
enq: PE - contention	Other	name|mode	parno	0
enq: PF - contention	Other	name|mode	0	0
enq: PG - contention	Other	name|mode	0	0
enq: PH - contention	Other	name|mode	0	0
enq: PI - contention	Other	name|mode	operation	serial #
enq: PL - contention	Other	name|mode	0	0
enq: PR - contention	Other	name|mode	0	0
enq: PS - contention	Other	name|mode	instance	slave ID
enq: PT - contention	Other	name|mode	disk group #	type
enq: PV - syncshut	Other	name|mode	0	0
enq: PV - syncstart	Other	name|mode	0	0
enq: PW - flush prewarm buffers	Application	name|mode	0	0
enq: PW - perwarm status in dbw0	Other	name|mode	0	0
enq: RB - contention	Other	name|mode	disk group	0
enq: RF - atomicity	Other	name|mode	lock operation	lock value
enq: RF - new AI	Other	name|mode	lock operation	lock value
enq: RF - synch: per-SGA Broker metadata	Other	name|mode	lock operation	lock value
enq: RF - synchronization: aifo master	Other	name|mode	lock operation	lock value
enq: RF - synchronization: chief	Other	name|mode	lock operation	lock value
enq: RF - synchronization: critical ai	Other	name|mode	lock operation	lock value
enq: RF - synchronization: HC master	Other	name|mode	lock operation	lock value
enq: RN - contention	Other	name|mode	thread number	log number
enq: RO - contention	Application	name|mode	1	0
enq: RO - fast object reuse	Application	name|mode	1	0
enq: RP - contention	Other	name|mode	file #	1 or block
enq: RS - file delete	Other	name|mode	record type	record id
enq: RS - persist alert level	Other	name|mode	record type	record id
enq: RS - prevent aging list update	Other	name|mode	record type	record id
enq: RS - prevent file delete	Other	name|mode	record type	record id
enq: RS - read alert level	Other	name|mode	record type	record id
enq: RS - record reuse	Other	name|mode	record type	record id
enq: RS - write alert level	Other	name|mode	record type	record id
enq: RT - contention	Other	name|mode	redo thread	type
enq: SB - contention	Other	name|mode	0	0
enq: SF - contention	Other	name|mode	0	0
enq: SH - contention	Other	name|mode	0	0
enq: SI - contention	Other	name|mode	object #	0
enq: SK - contention	Other	name|mode	tablespace #	dba
enq: SQ - contention	Configuration	name|mode	object #	0
enq: SR - contention	Other	name|mode	operation	sequence # / apply #
enq: SS - contention	Other	name|mode	tablespace #	dba
enq: ST - contention	Configuration	name|mode	0	0
enq: SU - contention	Other	name|mode	table space #	0
enq: SW - contention	Other	name|mode	0	0
enq: TA - contention	Other	name|mode	operation	undo segment # / other
enq: TB - SQL Tuning Base Cache Load	Other	name|mode	1	2
enq: TB - SQL Tuning Base Cache Update	Other	name|mode	1	2
enq: TC - contention	Other	name|mode	checkpoint ID	0
enq: TC - contention2	Other	name|mode	checkpoint ID	0
enq: TD - KTF dump entries	Other	name|mode	0	0
enq: TE - KTF broadcast	Other	name|mode	0	0
enq: TF - contention	Other	name|mode	tablespace #	relative file #
enq: TL - contention	Other	name|mode	0	0
enq: TM - contention	Application	name|mode	object #	table/partition
enq: TO - contention	Other	name|mode	object #	1
enq: TQ - DDL contention	Other	name|mode	QT_OBJ#	0
enq: TQ - INI contention	Other	name|mode	QT_OBJ#	0
enq: TQ - TM contention	Other	name|mode	QT_OBJ#	0
enq: TS - contention	Other	name|mode	tablespace ID	dba
enq: TT - contention	Other	name|mode	tablespace ID	operation
enq: TW - contention	Administrative	name|mode	0	operation
enq: TX - allocate ITL entry	Configuration	name|mode	usn<<16 | slot	sequence
enq: TX - contention	Other	name|mode	usn<<16 | slot	sequence
enq: TX - index contention	Concurrency	name|mode	usn<<16 | slot	sequence
enq: TX - row lock contention	Application	name|mode	usn<<16 | slot	sequence
enq: UL - contention	Application	name|mode	id	0
enq: US - contention	Other	name|mode	undo segment #	0
enq: WA - contention	Other	name|mode	0	0
enq: WF - contention	Other	name|mode	0	0
enq: WL - contention	Other	name|mode	log # / thread id #	sequence #
enq: WP - contention	Other	name|mode	0	0
enq: XH - contention	Other	name|mode	0	0
enq: XR - database force logging	Other	name|mode	operation	0
enq: XR - quiesce database	Other	name|mode	operation	0
enq: XY - contention	Other	name|mode	id1	id2
extent map load/unlock	Other	group	file	extent
flashback buf free by RVWR	Other
flashback free VI log	Other
flashback log switch	Other
Flow Control Event	Other
foreground creation: start	Other
foreground creation: wait	Other
free buffer waits	Configuration	file#	block#	set-id#
free global transaction table entry	Other	tries
free process state object	Other
gc assume	Cluster	le
gc block recovery request	Cluster	file#	block#	class#
gc buffer busy	Cluster	file#	block#	id#
gc claim	Cluster
gc cr block 2-way	Cluster
gc cr block 3-way	Cluster
gc cr block busy	Cluster
gc cr block congested	Cluster
gc cr block unknown	Cluster
gc cr cancel	Cluster	le
gc cr disk read	Cluster
gc cr disk request	Cluster	file#	block#	class#
gc cr failure	Cluster
gc cr grant 2-way	Cluster
gc cr grant busy	Cluster
gc cr grant congested	Cluster
gc cr grant unknown	Cluster
gc cr multi block request	Cluster	file#	block#	class#
gc cr request	Cluster	file#	block#	class#
gc current block 2-way	Cluster
gc current block 3-way	Cluster
gc current block busy	Cluster
gc current block congested	Cluster
gc current block unknown	Cluster
gc current cancel	Cluster	le
gc current grant 2-way	Cluster
gc current grant busy	Cluster
gc current grant congested	Cluster
gc current grant unknown	Cluster
gc current multi block request	Cluster	file#	block#	id#
gc current request	Cluster	file#	block#	id#
gc current retry	Cluster
gc current split	Cluster
gc domain validation	Cluster
gc freelist	Cluster
gc prepare	Cluster
gc quiesce wait	Cluster
gc recovery free	Cluster
gc recovery quiesce	Cluster
gc remaster	Cluster
gcs ddet enter server mode	Other
gcs domain validation	Other	cluinc	rcvinc
gcs drm freeze begin	Other
gcs drm freeze in enter server mode	Other
gcs enter server mode	Other
gcs log flush sync	Other	waittime	poll	event
gcs remastering wait for read latch	Other
gcs remastering wait for write latch	Other
gcs remote message	Idle	waittime	poll	event
gcs resource directory to be unfrozen	Other
gcs to be enabled	Other
ges cached resource cleanup	Other	waittime
ges cancel	Other
ges cgs registration	Other
ges enter server mode	Other
ges generic event	Other
ges global resource directory to be frozen	Other
ges inquiry response	Other	type|mode	id1	id2
ges lmd and pmon to attach	Other
ges LMD suspend for testing event	Other
ges LMD to inherit communication channels	Other
ges LMD to shutdown	Other
ges lmd/lmses to freeze in rcfg - mrcvr	Other
ges lmd/lmses to unfreeze in rcfg - mrcvr	Other
ges LMON for send queues	Other
ges LMON to get to FTDONE	Other
ges LMON to join CGS group	Other
ges master to get established for SCN op	Other
ges performance test completion	Other
ges pmon to exit	Other
ges process with outstanding i/o	Other	pid
ges reconfiguration to start	Idle
ges remote message	Idle	waittime	loop	p3
ges resource cleanout during enqueue open	Other
ges resource cleanout during enqueue open-cvt	Other
ges resource directory to be unfrozen	Other
ges reusing os pid	Other	pid	count
ges user error	Other	error
ges wait for lmon to be ready	Other
ges1 LMON to wake up LMD - mrcvr	Other
ges2 LMON to wake up LMD - mrcvr	Other
ges2 LMON to wake up lms - mrcvr 2	Other
ges2 LMON to wake up lms - mrcvr 3	Other
ges2 proc latch in rm latch get 1	Other
ges2 proc latch in rm latch get 2	Other
global cache busy	Other	group	file#	block#
global enqueue expand wait	Other
GV$: slave acquisition retry wait time	Other
HS message to agent	Idle
i/o slave wait	Other	msg ptr
imm op	Other	msg ptr
inactive session	Other	session#	waited
inactive transaction branch	Other	branch#	waited
index (re)build online cleanup	Administrative	object	mode	wait
index (re)build online merge	Administrative	object	mode	wait
index (re)build online start	Administrative	object	mode	wait
index block split	Other	rootdba	level	childdba
instance state change	Other	layer	value	waited
io done	System I/O	msg ptr
IPC busy async request	Other
IPC send completion sync	Other	send count
IPC wait for name service busy	Other
IPC waiting for OSD resources	Other
job scheduler coordinator slave wait	Other
jobq slave wait	Idle
JS coord start wait	Other
JS external job	Idle
JS kgl get object wait	Other
JS kill job wait	Other
kcbzps	Other
kcrrrcp	Other
kdic_do_merge	Other
kfcl: instance recovery	Other	group	obj#	block#
kfk: async disk IO	System I/O	count	intr	timeout
kgltwait	Other
kjbdomalc allocate recovery domain - retry	Other
kjbdrmcvtq lmon drm quiesce: ping completion	Other
kjbopen wait for recovery domain attach	Other
KJC: Wait for msg sends to complete	Other	msg	dest|rcvr	mtype
kjctcisnd: Queue/Send client message	Other
kjctssqmg: quick message send wait	Other
kjudomatt wait for recovery domain attach	Other
kjudomdet wait for recovery domain detach	Other
kjxgrtest	Other
kkdlgon	Other
kkdlhpon	Other
kkdlsipon	Other
kksfbc child completion	Other
kksfbc research	Other
kkshgnc reloop	Other
kksscl hash split	Other
knlqdeq	Other
knlWaitForStartup	Other
knpc_acwm_AwaitChangedWaterMark	Other
knpc_anq_AwaitNonemptyQueue	Other
knpsmai	Other
ksbcic	Other
ksbsrv	Other
ksdxexeother	Other
ksdxexeotherwait	Other
ksfd: async disk IO	System I/O	count	intr	timeout
ksfd: fib/fob latch	Other
ksim generic wait event	Other	where	wait_count
ksqded	Other
ksv slave avail wait	Other
ksxr poll remote instances	Other
ktfbtgex	Other	tsn
ktm: instance recovery	Other	undo segment#
ktsambl	Other
kttm2d	Other
Kupp process shutdown	Other	nalive	sleeptime	loop
kupp process wait	Other
kxfxse	Other	kxfxse debug wait: stalling for slave 0
kxfxsp	Other	kxfxsp debug wait: stalling for slave 0
L1 validation	Other	seghdr	l1bmb
latch activity	Other	address	number	process#
latch free	Other	address	number	tries
latch: cache buffer handles	Other	address	number	tries
latch: cache buffers chains	Concurrency	address	number	tries
latch: cache buffers lru chain	Other	address	number	tries
latch: checkpoint queue latch	Other	address	number	tries
latch: enqueue hash chains	Other	address	number	tries
latch: gcs resource hash	Other	address	number	tries
latch: ges resource hash list	Other	address	number	tries
latch: In memory undo latch	Concurrency	address	number	tries
latch: KCL gc element parent latch	Other	address	number	tries
latch: latch wait list	Other	address	number	tries
latch: library cache	Concurrency	address	number	tries
latch: library cache lock	Concurrency	address	number	tries
latch: library cache pin	Concurrency	address	number	tries
latch: messages	Other	address	number	tries
latch: MQL Tracking Latch	Concurrency	address	number	tries
latch: object queue header heap	Other	address	number	tries
latch: object queue header operation	Other	address	number	tries
latch: parallel query alloc buffer	Other	address	number	tries
latch: redo allocation	Other	address	number	tries
latch: redo copy	Configuration	address	number	tries
latch: redo writing	Configuration	address	number	tries
latch: row cache objects	Other	address	number	tries
latch: session allocation	Other	address	number	tries
latch: shared pool	Configuration	address	number	tries
latch: undo global data	Other	address	number	tries
latch: virtual circuit queues	Other	address	number	tries
LGWR random i/o	System I/O
LGWR sequential i/o	System I/O
LGWR simulation latency wait	Other
LGWR wait for redo copy	Other	copy latch #
LGWR wait on ATTACH	Network
LGWR wait on DETACH	Network
LGWR wait on full LNS buffer	Other
LGWR wait on LNS	Network
LGWR wait on SENDREQ	Network
LGWR-LNS wait on channel	Other
library cache load lock	Concurrency	object address	lock address	100*mask+namespace
library cache lock	Concurrency	handle address	lock address	100*mode+namespace
library cache pin	Concurrency	handle address	pin address	100*mode+namespace
library cache revalidation	Other
listen endpoint status	Other	end-point#	status
LNS simulation latency wait	Other
LNS wait for LGWR redo	Other
LNS wait on ATTACH	Network
LNS wait on DETACH	Network
LNS wait on LGWR	Network
LNS wait on SENDREQ	Network
local write wait	User I/O	file#	block#
lock close	Other	group	lms#
lock deadlock retry	Other
lock escalate retry	Other
lock release pending	Other	group	file#	block#
lock remastering	Cluster
log buffer space	Configuration
log file parallel write	System I/O	files	blocks	requests
log file sequential read	System I/O	log#	block#	blocks
log file single write	System I/O	log#	block#	blocks
log file switch (archiving needed)	Configuration
log file switch (checkpoint incomplete)	Configuration
log file switch (clearing log file)	Configuration
log file switch completion	Configuration
log file sync	Commit	buffer#
log switch/archive	Configuration	thread#
log write(even)	Other	group#
log write(odd)	Other	group#
master exit	Other	alive slaves
master wait	Other
MMON (Lite) shutdown	Other	process#	waited
MMON slave messages	Other
MRP wait on archivelog archival	Other
MRP wait on archivelog arrival	Other
MRP wait on archivelog delay	Other
MRP wait on process death	Other
MRP wait on process restart	Other
MRP wait on process start	Other
MRP wait on startup clear	Other
MRP wait on state change	Other
MRP wait on state n_a	Other
MRP wait on state reset	Other
multiple dbwriter suspend/resume for file offline	Administrative
name-service call wait	Other	waittime
no free buffers	Other	group#	obj#	block#
no free locks	Other
null event	Other
OLAP Aggregate Client Deq	Other	sleeptime/senderid	passes
OLAP Aggregate Client Enq	Other	sleeptime/senderid	passes
OLAP Aggregate Master Deq	Other	sleeptime/senderid	passes
OLAP Aggregate Master Enq	Other	sleeptime/senderid	passes
OLAP Null PQ Reason	Other	sleeptime/senderid	passes
OLAP Parallel Temp Grew	Other	sleeptime/senderid	passes
OLAP Parallel Temp Grow Request	Other	sleeptime/senderid	passes
OLAP Parallel Temp Grow Wait	Other	sleeptime/senderid	passes
OLAP Parallel Type Deq	Other	sleeptime/senderid	passes
opishd	Other
parallel recovery coordinator waits for cleanup of slaves	Idle
pending global transaction(s)	Other	scans
pi renounce write complete	Cluster	file#	block#
pipe get	Idle	handle address	buffer length	timeout
pipe put	Concurrency	handle address	record length	timeout
PL/SQL lock timer	Idle	duration
pmon timer	Idle	duration
process shutdown	Other	type	process#	waited
process startup	Other	type	process#	waited
PX create server	Other	nservers	sleeptime	enqueue
PX Deq Credit: free buffer	Other	sleeptime/senderid	passes	qref
PX Deq Credit: need buffer	Idle	sleeptime/senderid	passes	qref
PX Deq Credit: send blkd	Other	sleeptime/senderid	passes	qref
PX Deq: Execute Reply	Idle	sleeptime/senderid	passes
PX Deq: Execution Msg	Idle	sleeptime/senderid	passes
PX Deq: Index Merge Close	Idle	sleeptime/senderid	passes
PX Deq: Index Merge Execute	Idle	sleeptime/senderid	passes
PX Deq: Index Merge Reply	Idle	sleeptime/senderid	passes
PX Deq: Join ACK	Idle	sleeptime/senderid	passes
PX Deq: kdcph_mai	Idle	kdcph_mai
PX Deq: kdcphc_ack	Idle	kdcphc_ack
PX Deq: Msg Fragment	Idle	sleeptime/senderid	passes
PX Deq: OLAP Update Close	Other	sleeptime/senderid	passes
PX Deq: OLAP Update Execute	Other	sleeptime/senderid	passes
PX Deq: OLAP Update Reply	Other	sleeptime/senderid	passes
PX Deq: Par Recov Change Vector	Idle	sleeptime/senderid	passes
PX Deq: Par Recov Execute	Idle	sleeptime/senderid	passes
PX Deq: Par Recov Reply	Idle	sleeptime/senderid	passes
PX Deq: Parse Reply	Idle	sleeptime/senderid	passes
PX Deq: reap credit	Other
PX Deq: Signal ACK	Other	sleeptime/senderid	passes
PX Deq: Table Q Close	Other	sleeptime/senderid	passes
PX Deq: Table Q Get Keys	Other	sleeptime/senderid	passes
PX Deq: Table Q Normal	Idle	sleeptime/senderid	passes
PX Deq: Table Q qref	Other	sleeptime/senderid	passes
PX Deq: Table Q Sample	Idle	sleeptime/senderid	passes
PX Deq: Test for msg	Other	sleeptime/senderid	passes
PX Deq: Txn Recovery Reply	Idle	sleeptime/senderid	passes
PX Deq: Txn Recovery Start	Idle	sleeptime/senderid	passes
PX Deque wait	Idle	sleeptime/senderid	passes
PX Idle Wait	Idle	sleeptime/senderid	passes
PX Nsq: PQ descriptor query	Other
PX Nsq: PQ load info query	Other
PX qref latch	Other	function	sleeptime	qref
PX Send Wait	Other
PX server shutdown	Other	nalive	sleeptime	loop
PX signal server	Other	serial	error	nbusy
PX slave connection	Other
PX slave release	Other
qerex_gdml	Other
queue messages	Idle	queue id	process#	wait time
Queue Monitor IPC wait	Idle
Queue Monitor Shutdown Wait	Idle
Queue Monitor Slave Wait	Idle
Queue Monitor Task Wait	Other
Queue Monitor Wait	Idle
queue slave messages	Other
rdbms ipc message	Idle	timeout
rdbms ipc message block	Other
rdbms ipc reply	Other	from_process	timeout
read by other session	User I/O	file#	block#	class#
recovery area: computing applied logs	Other
recovery area: computing backed up files	Other
recovery area: computing dropped files	Other
recovery area: computing identical files	Other
recovery area: computing obsolete files	Other
recovery read	System I/O
refresh controlfile command	Administrative
reliable message	Other	channel context	channel handle	broadcast message
Replication Dequeue	Other	sleeptime/senderid	passes
resmgr:become active	Scheduler	location
resmgr:cpu quantum	Scheduler	location
resmgr:internal state change	Concurrency	location
resmgr:internal state cleanup	Concurrency	location
resmgr:sessions to exit	Concurrency	location
retry contact SCN lock master	Cluster
rfc_open_retry	Other	DMON waiting to retry configuration file open
rfi_drcx_site_del	Other	DRCX waiting for site to delete metadata
rfi_insv_shut	Other	wait for INSV to shutdown
rfi_insv_start	Other	wait for INSV to start
rfi_nsv_deldef	Other	NSVx to defer delete response message post to DMON
rfi_nsv_md_close	Other	NSVx metadata file close wait
rfi_nsv_md_write	Other	NSVx metadata file write wait
rfi_nsv_postdef	Other	NSVx to defer message post to DMON
rfi_nsv_shut	Other	wait for NSVx to shutdown
rfi_nsv_start	Other	wait for NSVx to start
rfi_recon1	Other	letting site register with its local listener before connect ret
rfi_recon2	Other	retrying connection for sending to remote DRCX
rfm_dmon_last_gasp	Other	DMON waiting on the last gasp event
rfm_dmon_pdefer	Other	DMON phase deferral wait
rfm_dmon_shut	Other	wait for DMON to shutdown
rfm_dmon_timeout_op	Other	DMON waiting to timeout an operation
rfm_pmon_dso_stall	Other	PMON delete state object stall
rfrdb_dbop	Other	waiting for database to be opened
rfrdb_recon1	Other	reconnecting back to new primary site during standby viability c
rfrdb_recon2	Other	waiting for standby database to be mounted
rfrdb_try235	Other	waiting for retrying the query to mask ORA-235 error
rfrla_lapp1	Other	waiting for logical apply engine to initialize
rfrla_lapp2	Other	checking for logical apply engine run-down progress
rfrla_lapp3	Other	waiting for new primary to initialize tables
rfrla_lapp4	Other	waiting for v$logstdby_stats view to be initialized
rfrla_lapp5	Other	waiting to reconnect to primary that is in BUILD_UP
rfrm_dbcl	Other	RSM notifier: waiting for sql latch on db close
rfrm_dbop	Other	RSM notifier: waiting for sql latch on db open
rfrm_nonzero_sub_count	Other	wait for subscriber count to become nonzero
rfrm_rsm_shut	Other	wait for RSMx processes to shutdown
rfrm_rsm_so_attach	Other	wait for RSMx to attach to state object
rfrm_rsm_start	Other	wait for RSMx processes to start
rfrm_stall	Other	RSM stall due to event RSM_STALL
rfrm_zero_sub_count	Other	wait for subscriber count to become zero
rfrpa_mrpdn	Other	waiting for MRP0 to stop while bringing physical apply engine of
rfrpa_mrpup	Other	waiting for MRP0 to start while bringing physical apply engine o
rfrxpt_pdl	Other	waiting for retrying potential dataloss calculation before switc
rfrxptarcurlog	Other	waiting for logical apply engine to finish initialization
RFS announce	Other
RFS attach	Other
RFS close	Other
RFS create	Other
RFS detach	Other
RFS dispatch	Other
RFS ping	Other
RFS random i/o	System I/O
RFS register	Other
RFS sequential i/o	System I/O
RFS write	System I/O
rollback operations active	Other	operation count
rollback operations block full	Other	max operations
row cache lock	Concurrency	cache id	mode	request
row cache read	Concurrency	cache id	address	times
RVWR wait for flashback copy	Other	copy latch #
scginq AST call	Other
secondary event	Other	event #	wait time
select wait	Other
simulated log write delay	Other
single-task message	Idle
slave exit	Other	nalive	sleeptime	loop
slave shutdown wait	Other
slave TJ process wait	Other
smon timer	Idle	sleep time	failed
sort segment request	Configuration
SQL*Net break/reset to client	Application	driver id	break?
SQL*Net break/reset to dblink	Application	driver id	break?
SQL*Net message from client	Idle	driver id	#bytes
SQL*Net message from dblink	Idle	driver id	#bytes
SQL*Net message to client	Network	driver id	#bytes
SQL*Net message to dblink	Network	driver id	#bytes
SQL*Net more data from client	Network	driver id	#bytes
SQL*Net more data from dblink	Network	driver id	#bytes
SQL*Net more data to client	Network	driver id	#bytes
SQL*Net more data to dblink	Network	driver id	#bytes
statement suspended, wait error to be cleared	Idle
STREAMS apply coord waiting for slave message	Idle
STREAMS apply slave idle wait	Idle
STREAMS apply slave waiting for coord message	Application
STREAMS capture process filter callback wait for ruleset	Idle
STREAMS capture process waiting for archive log	Other
STREAMS fetch slave waiting for txns	Idle
Streams: Wait for inter instance ack	Other
Streams: Wating for DDL to apply	Application	sleep time
switch logfile command	Administrative
switch undo - offline	Administrative
SWRF RWM Auto Capture Event	Other
SWRF Wait on Flushing	Other
Sync ASM rebalance	Other
test long ops	Other
timer in sksawat	Other
trace continue	Other	delay time
trace unfreeze	Other
trace writer flush	Other
trace writer I/O	Other
transaction	Other	undo seg#|slot#	wrap#	count
txn to complete	Other
unbound tx	Other
undo segment extension	Configuration	segment#
undo segment recovery	Other	segment#	tx flags
undo segment tx slot	Configuration	segment#
unspecified wait event	Other
virtual circuit status	Idle	circuit#	status
wait active processes	Other
wait for a paralle reco to abort	Other
wait for a undo record	Other
wait for activate message	Idle
wait for another txn - rollback to savepoint	Other
wait for another txn - txn abort	Other
wait for another txn - undo rcv abort	Other
wait for assert messages to be sent	Other
wait for change	Other
Wait for Dictionary Build to lock all tables	Other
wait for EMON to die	Other
wait for EMON to process ntfns	Configuration
wait for EMON to spawn	Other
wait for FMON to come up	Other
wait for Logical Standby Apply shutdown	Other
wait for master scn	Other	waittime	startscn	ackscn
wait for membership synchronization	Other
wait for message ack	Other
wait for MTTR advisory state object	Other
wait for possible quiesce finish	Administrative
wait for record update	Other
wait for resize request completion	Other
wait for rr lock release	Other
wait for scn ack	Other	pending_nd	scnwrp	scnbas
wait for SGA component shrink	Other	component id	current size	target size
wait for sga_target resize	Other
Wait for shrink lock	Other	object_id	lock_mode
Wait for shrink lock2	Other	object_id	lock_mode
wait for split-brain resolution	Other
wait for stopper event to be increased	Other
wait for sync ack	Other	cluinc	pending_nd
Wait for Table Lock	Application
wait for tmc2 to complete	Other
wait for transaction	Idle
Wait for TT enqueue	Other	tsn
wait for unread message on broadcast channel	Idle	channel context	channel handle
wait for unread message on multiple broadcast channels	Idle	channel context	channel handle count
wait for verification ack	Other	cluinc	pending_insts
wait for votes	Other
wait list latch activity	Other	address	number	process#
wait list latch free	Other	address	number	tries
Wait on stby instance close	Other
waiting for low memory condition to be resolved	Idle
waiting for subscribers to catch up	Idle
waiting to get CAS latch	Other
waiting to get RM CAS latch	Other
wakeup blocked enqueuers	Other
wakeup event for builder	Idle
wakeup event for preparer	Idle
wakeup event for reader	Idle
wakeup time manager	Idle
write complete waits	Configuration	file#	block#
writes stopped by instance recovery or database suspension	Other	by thread#	our thread#

Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com