Wait Event Enhancements in Oracle 10g

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:

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

NameWait ClassP1P2P3
alter rbs offlineAdministrative   
alter system set dispatcherAdministrativewaited  
AQ Deallocate WaitConfiguration   
AQ Proxy Cleanup WaitIdle   
ARCH random i/oSystem I/O   
ARCH sequential i/oSystem I/O   
ARCH wait for archivelog lockOther   
ARCH wait for flow-controlNetwork   
ARCH wait for net re-connectNetwork   
ARCH wait for netserver detachNetwork   
ARCH wait for netserver init 1Network   
ARCH wait for netserver init 2Network   
ARCH wait for netserver startNetwork   
ARCH wait for pending I/OsSystem I/O   
ARCH wait for process death 1Other   
ARCH wait for process death 2Other   
ARCH wait for process death 3Other   
ARCH wait for process death 4Other   
ARCH wait for process death 5Other   
ARCH wait for process start 1Other   
ARCH wait for process start 2Other   
ARCH wait for process start 3Other   
ARCH wait for process start 4Other   
ARCH wait on ATTACHNetwork   
ARCH wait on c/f tx acquire 1Other   
ARCH wait on c/f tx acquire 2Other   
ARCH wait on DETACHNetwork   
ARCH wait on SENDREQNetwork   
ASM background runningOther   
ASM background startingOther   
ASM background timerIdle   
ASM db client existsOther   
ASM mount : wait for heartbeatAdministrative   
ASM PST query : wait for [PM][grp][0] grantCluster   
Backup: sbtbackupAdministrative   
Backup: sbtcloseAdministrative   
Backup: sbtclose2Administrative   
Backup: sbtcommandAdministrative   
Backup: sbtendAdministrative   
Backup: sbterrorAdministrative   
Backup: sbtinfoAdministrative   
Backup: sbtinfo2Administrative   
Backup: sbtinitAdministrative   
Backup: sbtinit2Administrative   
Backup: sbtopenAdministrative   
Backup: sbtpcbackupAdministrative   
Backup: sbtpccancelAdministrative   
Backup: sbtpccommitAdministrative   
Backup: sbtpcendAdministrative   
Backup: sbtpcquerybackupAdministrative   
Backup: sbtpcqueryrestoreAdministrative   
Backup: sbtpcrestoreAdministrative   
Backup: sbtpcstartAdministrative   
Backup: sbtpcstatusAdministrative   
Backup: sbtpcvalidateAdministrative   
Backup: sbtreadAdministrative   
Backup: sbtread2Administrative   
Backup: sbtremoveAdministrative   
Backup: sbtremove2Administrative   
Backup: sbtrestoreAdministrative   
Backup: sbtwriteAdministrative   
Backup: sbtwrite2Administrative   
BFILE check if existsOther   
BFILE check if openOther   
BFILE closureOther   
BFILE get lengthOther   
BFILE get name objectOther   
BFILE get path objectOther   
BFILE internal seekOther   
BFILE openOther   
BFILE readUser I/O   
block change tracking buffer spaceOther   
buffer busyOthergroup#obj#block#
buffer busy waitsConcurrencyfile#block#class#
buffer deadlockOtherdbaclass*10+modeflag
buffer dirty disabledOthergroup#  
buffer exterminateOtherfile#block#buf_ptr
buffer freelistbusyOthergroup#obj#block#
buffer invalidation waitOthergroup#obj#block#
buffer latchOtherlatch addrchain# 
buffer pool resizeAdministrativebuffer pool idcurrent sizenew size
buffer read retryUser I/Ofile#block# 
buffer rememberlist busyOthergroup#obj#block#
buffer resizeOther   
buffer write waitOthergroup#obj#block#
buffer writeList fullOthergroup#obj#block#
CGS skgxn join retryOtherretry count  
CGS wait for IPC msgOther   
change tracking file parallel writeOtherblocksrequests 
change tracking file synchronous readOtherblock#blocks 
change tracking file synchronous writeOtherblock#blocks 
check CPU wait timesOther   
checkpoint advancedOthergroup#  
checkpoint completedConfiguration   
class slave waitOtherslave id  
Cluster stablization waitOther   
Cluster Suspension waitOther   
contacting SCN server or SCN lock masterCluster   
control file heartbeatOther   
control file parallel writeSystem I/Ofilesblock#requests
control file sequential readSystem I/Ofile#block#blocks
control file single writeSystem I/Ofile#block#blocks
cr request retryOtherfile#block# 
Data Guard broker: wait upon ORA-12850 errorOtherwaiting for retrying the query to mask ORA-12850 error  
db file parallel readUser I/Ofilesblocksrequests
db file parallel writeSystem I/Orequestsinterrupttimeout
db file scattered readUser I/Ofile#block#blocks
db file sequential readUser I/Ofile#block#blocks
db file single writeUser I/Ofile#block#blocks
DBFG waiting for replyOther   
DBMS_LDAP: LDAP operation Other   
debugger commandOther   
dedicated server timerNetworkwait event  
DFS db file lockOtherfile#  
DFS lock handleOthertype|modeid1id2
DIAG dummy waitOther   
direct path readUser I/Ofile numberfirst dbablock cnt
direct path read tempUser I/Ofile numberfirst dbablock cnt
direct path writeUser I/Ofile numberfirst dbablock cnt
direct path write tempUser I/Ofile numberfirst dbablock cnt
dispatcher listen timerNetworksleep time  
dispatcher shutdownOtherwaited  
dispatcher timerIdlesleep time  
DLM lock cancelOtherle  
DLM lock cvt SOthergroupobj#block#
DLM lock cvt XOthergroupobj#block#
DLM lock escOthergroupobj#block#
DLM lock esc XOthergroupobj#block#
DLM lock openOthergroupobj#block#
DLM lock open SOthergroupobj#block#
DLM lock open XOthergroupobj#block#
DLM recovery lock convertOthergroupobj#block#
DLM recovery lock openOthergroupobj#block#
dma prepare busyOthergroupobj#block#
dupl. cluster keyOtherdba  
enq: AD - allocate AUOthername|modegroup and disk numberAU number
enq: AD - deallocate AUOthername|modegroup and disk numberAU number
enq: AF - task serializationOthername|modetask id0
enq: AG - contentionOthername|modeworkspace #generation
enq: AO - contentionOthername|modeworkspace #object #
enq: AS - contentionOthername|mode00
enq: AT - contentionOthername|mode00
enq: AW - AW generation lockOthername|modeoperationworkspace #
enq: AW - AW state lockOthername|modeoperationworkspace #
enq: AW - AW$ table lockOthername|modeoperationworkspace #
enq: AW - user access for AWOthername|modeoperationworkspace #
enq: BR - file shrinkOthername|modeoperationfile #
enq: BR - proxy-copyOthername|modeoperationfile #
enq: CF - contentionOthername|mode0operation
enq: CI - contentionOthername|modeopcodetype
enq: CL - compare labelsOthername|modeobject #0
enq: CL - drop labelOthername|modeobject #0
enq: CM - gateOthername|modedisk group #type
enq: CM - instanceOthername|modedisk group #type
enq: CT - change stream ownershipOthername|modeoperationoperation parm
enq: CT - CTWR process start/stopOthername|modeoperationoperation parm
enq: CT - global space managementOthername|modeoperationoperation parm
enq: CT - local space managementOthername|modeoperationoperation parm
enq: CT - readingOthername|modeoperationoperation parm
enq: CT - stateOthername|modeoperationoperation parm
enq: CT - state change gate 1Othername|modeoperationoperation parm
enq: CT - state change gate 2Othername|modeoperationoperation parm
enq: CU - contentionOthername|modehandlehandle
enq: DB - contentionAdministrativename|modeEnqMode0
enq: DD - contentionOthername|modedisk grouptype
enq: DF - contentionOthername|mode0file #
enq: DG - contentionOthername|modedisk grouptype
enq: DL - contentionOthername|modeobject #0
enq: DM - contentionOthername|modetypetype
enq: DN - contentionOthername|mode00
enq: DP - contentionOthername|mode00
enq: DR - contentionOthername|mode00
enq: DS - contentionOthername|mode00
enq: DT - contentionOthername|mode00
enq: DV - contentionOthername|modeobject #0
enq: DX - contentionOthername|modetransaction entry #0
enq: FA - access fileOthername|modedisk group numberfile number
enq: FB - contentionOthername|modetablespace #dba
enq: FC - open an ACD threadOthername|modedisk groupthread
enq: FC - recover an ACD threadOthername|modedisk groupthread
enq: FD - Flashback coordinatorOthername|modeInternalInternal
enq: FD - Flashback on/offOthername|modeInternalInternal
enq: FD - Marker generationOthername|modeInternalInternal
enq: FD - Tablespace flashback on/offOthername|modeInternalInternal
enq: FG - FG redo generation enq raceOthername|modedisk grouptype
enq: FG - LGWR redo generation enq raceOthername|modedisk grouptype
enq: FG - serialize ACD relocateOthername|modedisk grouptype
enq: FL - Flashback database logOthername|modeLog #zero
enq: FL - Flashback db commandOthername|modeLog #zero
enq: FM - contentionOthername|mode00
enq: FR - contentionOthername|modedisk groupunused
enq: FS - contentionOthername|mode0type
enq: FT - allow LGWR writesOthername|modedisk groupthread
enq: FT - disable LGWR writesOthername|modedisk groupthread
enq: FU - contentionOthername|mode00
enq: HD - contentionOthername|modedisk group0
enq: HP - contentionOthername|modetablespace #dba
enq: HQ - contentionOthername|modeobject #hash value
enq: HV - contentionOthername|modeobject #0
enq: HW - contentionConfigurationname|modetable space #block
enq: IA - contentionOthername|mode00
enq: ID - contentionOthername|mode00
enq: IL - contentionOthername|modeobject #0
enq: IM - contention for blrOthername|modepool #0
enq: IR - contentionOthername|mode00/1
enq: IR - contention2Othername|mode00/1
enq: IS - contentionOthername|mode0type
enq: IT - contentionOthername|modeobject #0
enq: JD - contentionOthername|mode00
enq: JI - contentionOthername|modeview object #0
enq: JQ - contentionOthername|mode00
enq: JS - contentionOthername|modeservice IDqueue type
enq: JS - coord post lockOthername|modeservice IDqueue type
enq: JS - coord rcv lockOthername|modeservice IDqueue type
enq: JS - global wdw lockOthername|modeservice IDqueue type
enq: JS - job chain evaluate lockOthername|modeservice IDqueue type
enq: JS - job recov lockOthername|modeservice IDqueue type
enq: JS - job run lock - synchronizeOthername|modeservice IDqueue type
enq: JS - q mem clnup lckOthername|modeservice IDqueue type
enq: JS - queue lockOthername|modeservice IDqueue type
enq: JS - running job cnt lockOthername|modeservice IDqueue type
enq: JS - running job cnt lock2Othername|modeservice IDqueue type
enq: JS - running job cnt lock3Othername|modeservice IDqueue type
enq: JS - slave enq get lock1Othername|modeservice IDqueue type
enq: JS - slave enq get lock2Othername|modeservice IDqueue type
enq: KK - contextOthername|mode0redo thread
enq: KM - contentionOthername|modetypetype
enq: KP - contentionOthername|mode00
enq: KT - contentionOthername|modeplan #0
enq: MD - contentionOthername|modemaster object #0
enq: MH - contentionOthername|mode00
enq: ML - contentionOthername|mode00
enq: MN - contentionOthername|modesession ID0
enq: MR - contentionOthername|mode0 or file #type
enq: MS - contentionOthername|modemaster object #0
enq: MW - contentionOthername|modeSchedule Id0
enq: OC - contentionOthername|mode12
enq: OL - contentionOthername|modehash value0
enq: OQ - xsoq*histrecbOthername|moderesource id0
enq: OQ - xsoqhiAllocOthername|moderesource id0
enq: OQ - xsoqhiCloseOthername|moderesource id0
enq: OQ - xsoqhiFlushOthername|moderesource id0
enq: OQ - xsoqhistrecbOthername|moderesource id0
enq: PD - contentionOthername|modeproperty namekey hash
enq: PE - contentionOthername|modeparno0
enq: PF - contentionOthername|mode00
enq: PG - contentionOthername|mode00
enq: PH - contentionOthername|mode00
enq: PI - contentionOthername|modeoperationserial #
enq: PL - contentionOthername|mode00
enq: PR - contentionOthername|mode00
enq: PS - contentionOthername|modeinstanceslave ID
enq: PT - contentionOthername|modedisk group #type
enq: PV - syncshutOthername|mode00
enq: PV - syncstartOthername|mode00
enq: PW - flush prewarm buffersApplicationname|mode00
enq: PW - perwarm status in dbw0Othername|mode00
enq: RB - contentionOthername|modedisk group0
enq: RF - atomicityOthername|modelock operationlock value
enq: RF - new AIOthername|modelock operationlock value
enq: RF - synch: per-SGA Broker metadataOthername|modelock operationlock value
enq: RF - synchronization: aifo masterOthername|modelock operationlock value
enq: RF - synchronization: chiefOthername|modelock operationlock value
enq: RF - synchronization: critical aiOthername|modelock operationlock value
enq: RF - synchronization: HC masterOthername|modelock operationlock value
enq: RN - contentionOthername|modethread numberlog number
enq: RO - contentionApplicationname|mode10
enq: RO - fast object reuseApplicationname|mode10
enq: RP - contentionOthername|modefile #1 or block
enq: RS - file deleteOthername|moderecord typerecord id
enq: RS - persist alert levelOthername|moderecord typerecord id
enq: RS - prevent aging list updateOthername|moderecord typerecord id
enq: RS - prevent file deleteOthername|moderecord typerecord id
enq: RS - read alert levelOthername|moderecord typerecord id
enq: RS - record reuseOthername|moderecord typerecord id
enq: RS - write alert levelOthername|moderecord typerecord id
enq: RT - contentionOthername|moderedo threadtype
enq: SB - contentionOthername|mode00
enq: SF - contentionOthername|mode00
enq: SH - contentionOthername|mode00
enq: SI - contentionOthername|modeobject #0
enq: SK - contentionOthername|modetablespace #dba
enq: SQ - contentionConfigurationname|modeobject #0
enq: SR - contentionOthername|modeoperationsequence # / apply #
enq: SS - contentionOthername|modetablespace #dba
enq: ST - contentionConfigurationname|mode00
enq: SU - contentionOthername|modetable space #0
enq: SW - contentionOthername|mode00
enq: TA - contentionOthername|modeoperationundo segment # / other
enq: TB - SQL Tuning Base Cache LoadOthername|mode12
enq: TB - SQL Tuning Base Cache UpdateOthername|mode12
enq: TC - contentionOthername|modecheckpoint ID0
enq: TC - contention2Othername|modecheckpoint ID0
enq: TD - KTF dump entriesOthername|mode00
enq: TE - KTF broadcastOthername|mode00
enq: TF - contentionOthername|modetablespace #relative file #
enq: TL - contentionOthername|mode00
enq: TM - contentionApplicationname|modeobject #table/partition
enq: TO - contentionOthername|modeobject #1
enq: TQ - DDL contentionOthername|modeQT_OBJ#0
enq: TQ - INI contentionOthername|modeQT_OBJ#0
enq: TQ - TM contentionOthername|modeQT_OBJ#0
enq: TS - contentionOthername|modetablespace IDdba
enq: TT - contentionOthername|modetablespace IDoperation
enq: TW - contentionAdministrativename|mode0operation
enq: TX - allocate ITL entryConfigurationname|modeusn<<16 | slotsequence
enq: TX - contentionOthername|modeusn<<16 | slotsequence
enq: TX - index contentionConcurrencyname|modeusn<<16 | slotsequence
enq: TX - row lock contentionApplicationname|modeusn<<16 | slotsequence
enq: UL - contentionApplicationname|modeid0
enq: US - contentionOthername|modeundo segment #0
enq: WA - contentionOthername|mode00
enq: WF - contentionOthername|mode00
enq: WL - contentionOthername|modelog # / thread id #sequence #
enq: WP - contentionOthername|mode00
enq: XH - contentionOthername|mode00
enq: XR - database force loggingOthername|modeoperation0
enq: XR - quiesce databaseOthername|modeoperation0
enq: XY - contentionOthername|modeid1id2
extent map load/unlockOthergroupfileextent
flashback buf free by RVWROther   
flashback free VI logOther   
flashback log switchOther   
Flow Control EventOther   
foreground creation: startOther   
foreground creation: waitOther   
free buffer waitsConfigurationfile#block#set-id#
free global transaction table entryOthertries  
free process state objectOther   
gc assumeClusterle  
gc block recovery requestClusterfile#block#class#
gc buffer busyClusterfile#block#id#
gc claimCluster   
gc cr block 2-wayCluster   
gc cr block 3-wayCluster   
gc cr block busyCluster   
gc cr block congestedCluster   
gc cr block unknownCluster   
gc cr cancelClusterle  
gc cr disk readCluster   
gc cr disk requestClusterfile#block#class#
gc cr failureCluster   
gc cr grant 2-wayCluster   
gc cr grant busyCluster   
gc cr grant congestedCluster   
gc cr grant unknownCluster   
gc cr multi block requestClusterfile#block#class#
gc cr requestClusterfile#block#class#
gc current block 2-wayCluster   
gc current block 3-wayCluster   
gc current block busyCluster   
gc current block congestedCluster   
gc current block unknownCluster   
gc current cancelClusterle  
gc current grant 2-wayCluster   
gc current grant busyCluster   
gc current grant congestedCluster   
gc current grant unknownCluster   
gc current multi block requestClusterfile#block#id#
gc current requestClusterfile#block#id#
gc current retryCluster   
gc current splitCluster   
gc domain validationCluster   
gc freelistCluster   
gc prepareCluster   
gc quiesce waitCluster   
gc recovery freeCluster   
gc recovery quiesceCluster   
gc remasterCluster   
gcs ddet enter server modeOther   
gcs domain validationOthercluincrcvinc 
gcs drm freeze beginOther   
gcs drm freeze in enter server modeOther   
gcs enter server modeOther   
gcs log flush syncOtherwaittimepollevent
gcs remastering wait for read latchOther   
gcs remastering wait for write latchOther   
gcs remote messageIdlewaittimepollevent
gcs resource directory to be unfrozenOther   
gcs to be enabledOther   
ges cached resource cleanupOtherwaittime  
ges cancelOther   
ges cgs registrationOther   
ges enter server modeOther   
ges generic eventOther   
ges global resource directory to be frozenOther   
ges inquiry responseOthertype|modeid1id2
ges lmd and pmon to attachOther   
ges LMD suspend for testing eventOther   
ges LMD to inherit communication channelsOther   
ges LMD to shutdownOther   
ges lmd/lmses to freeze in rcfg - mrcvrOther   
ges lmd/lmses to unfreeze in rcfg - mrcvrOther   
ges LMON for send queuesOther   
ges LMON to get to FTDONE Other   
ges LMON to join CGS groupOther   
ges master to get established for SCN opOther   
ges performance test completionOther   
ges pmon to exitOther   
ges process with outstanding i/oOtherpid  
ges reconfiguration to startIdle   
ges remote messageIdlewaittimeloopp3
ges resource cleanout during enqueue openOther   
ges resource cleanout during enqueue open-cvtOther   
ges resource directory to be unfrozenOther   
ges reusing os pidOtherpidcount 
ges user errorOthererror  
ges wait for lmon to be readyOther   
ges1 LMON to wake up LMD - mrcvrOther   
ges2 LMON to wake up LMD - mrcvrOther   
ges2 LMON to wake up lms - mrcvr 2Other   
ges2 LMON to wake up lms - mrcvr 3Other   
ges2 proc latch in rm latch get 1Other   
ges2 proc latch in rm latch get 2Other   
global cache busyOthergroupfile#block#
global enqueue expand waitOther   
GV$: slave acquisition retry wait timeOther   
HS message to agentIdle   
i/o slave waitOthermsg ptr  
imm opOthermsg ptr  
inactive sessionOthersession#waited 
inactive transaction branchOtherbranch#waited 
index (re)build online cleanupAdministrativeobjectmodewait
index (re)build online mergeAdministrativeobjectmodewait
index (re)build online startAdministrativeobjectmodewait
index block splitOtherrootdbalevelchilddba
instance state changeOtherlayervaluewaited
io doneSystem I/Omsg ptr  
IPC busy async requestOther   
IPC send completion syncOthersend count  
IPC wait for name service busyOther   
IPC waiting for OSD resourcesOther   
job scheduler coordinator slave waitOther   
jobq slave waitIdle   
JS coord start waitOther   
JS external jobIdle   
JS kgl get object waitOther   
JS kill job waitOther   
kcbzpsOther   
kcrrrcpOther   
kdic_do_mergeOther   
kfcl: instance recoveryOthergroupobj#block#
kfk: async disk IOSystem I/Ocountintrtimeout
kgltwaitOther   
kjbdomalc allocate recovery domain - retryOther   
kjbdrmcvtq lmon drm quiesce: ping completionOther   
kjbopen wait for recovery domain attachOther   
KJC: Wait for msg sends to completeOthermsgdest|rcvrmtype
kjctcisnd: Queue/Send client messageOther   
kjctssqmg: quick message send waitOther   
kjudomatt wait for recovery domain attachOther   
kjudomdet wait for recovery domain detachOther   
kjxgrtestOther   
kkdlgonOther   
kkdlhponOther   
kkdlsiponOther   
kksfbc child completionOther   
kksfbc researchOther   
kkshgnc reloopOther   
kksscl hash splitOther   
knlqdeqOther   
knlWaitForStartupOther   
knpc_acwm_AwaitChangedWaterMarkOther   
knpc_anq_AwaitNonemptyQueueOther   
knpsmaiOther   
ksbcicOther   
ksbsrvOther   
ksdxexeotherOther   
ksdxexeotherwaitOther   
ksfd: async disk IOSystem I/Ocountintrtimeout
ksfd: fib/fob latchOther   
ksim generic wait eventOtherwherewait_count 
ksqdedOther   
ksv slave avail waitOther   
ksxr poll remote instancesOther   
ktfbtgexOthertsn  
ktm: instance recoveryOtherundo segment#  
ktsamblOther   
kttm2dOther   
Kupp process shutdownOthernalivesleeptimeloop
kupp process waitOther   
kxfxseOtherkxfxse debug wait: stalling for slave 0  
kxfxspOtherkxfxsp debug wait: stalling for slave 0  
L1 validationOtherseghdrl1bmb 
latch activityOtheraddressnumberprocess#
latch freeOtheraddressnumbertries
latch: cache buffer handlesOtheraddressnumbertries
latch: cache buffers chainsConcurrencyaddressnumbertries
latch: cache buffers lru chainOtheraddressnumbertries
latch: checkpoint queue latchOtheraddressnumbertries
latch: enqueue hash chainsOtheraddressnumbertries
latch: gcs resource hashOtheraddressnumbertries
latch: ges resource hash listOtheraddressnumbertries
latch: In memory undo latchConcurrencyaddressnumbertries
latch: KCL gc element parent latchOtheraddressnumbertries
latch: latch wait listOtheraddressnumbertries
latch: library cacheConcurrencyaddressnumbertries
latch: library cache lockConcurrencyaddressnumbertries
latch: library cache pinConcurrencyaddressnumbertries
latch: messagesOtheraddressnumbertries
latch: MQL Tracking LatchConcurrencyaddressnumbertries
latch: object queue header heapOtheraddressnumbertries
latch: object queue header operationOtheraddressnumbertries
latch: parallel query alloc bufferOtheraddressnumbertries
latch: redo allocationOtheraddressnumbertries
latch: redo copyConfigurationaddressnumbertries
latch: redo writingConfigurationaddressnumbertries
latch: row cache objectsOtheraddressnumbertries
latch: session allocationOtheraddressnumbertries
latch: shared poolConfigurationaddressnumbertries
latch: undo global dataOtheraddressnumbertries
latch: virtual circuit queuesOtheraddressnumbertries
LGWR random i/oSystem I/O   
LGWR sequential i/oSystem I/O   
LGWR simulation latency waitOther   
LGWR wait for redo copyOthercopy latch #  
LGWR wait on ATTACHNetwork   
LGWR wait on DETACHNetwork   
LGWR wait on full LNS bufferOther   
LGWR wait on LNSNetwork   
LGWR wait on SENDREQNetwork   
LGWR-LNS wait on channelOther   
library cache load lockConcurrencyobject addresslock address100*mask+namespace
library cache lockConcurrencyhandle addresslock address100*mode+namespace
library cache pinConcurrencyhandle addresspin address100*mode+namespace
library cache revalidationOther   
listen endpoint statusOtherend-point#status 
LNS simulation latency waitOther   
LNS wait for LGWR redoOther   
LNS wait on ATTACHNetwork   
LNS wait on DETACHNetwork   
LNS wait on LGWRNetwork   
LNS wait on SENDREQNetwork   
local write waitUser I/Ofile#block# 
lock closeOthergrouplms# 
lock deadlock retryOther   
lock escalate retryOther   
lock release pendingOthergroupfile#block#
lock remasteringCluster   
log buffer spaceConfiguration   
log file parallel writeSystem I/Ofilesblocksrequests
log file sequential readSystem I/Olog#block#blocks
log file single writeSystem I/Olog#block#blocks
log file switch (archiving needed)Configuration   
log file switch (checkpoint incomplete)Configuration   
log file switch (clearing log file)Configuration   
log file switch completionConfiguration   
log file syncCommitbuffer#  
log switch/archiveConfigurationthread#  
log write(even)Othergroup#  
log write(odd)Othergroup#  
master exitOtheralive slaves  
master waitOther   
MMON (Lite) shutdownOtherprocess#waited 
MMON slave messagesOther   
MRP wait on archivelog archivalOther   
MRP wait on archivelog arrivalOther   
MRP wait on archivelog delayOther   
MRP wait on process deathOther   
MRP wait on process restartOther   
MRP wait on process startOther   
MRP wait on startup clearOther   
MRP wait on state changeOther   
MRP wait on state n_aOther   
MRP wait on state resetOther   
multiple dbwriter suspend/resume for file offlineAdministrative   
name-service call waitOtherwaittime  
no free buffersOthergroup#obj#block#
no free locksOther   
null eventOther   
OLAP Aggregate Client DeqOthersleeptime/senderidpasses 
OLAP Aggregate Client EnqOthersleeptime/senderidpasses 
OLAP Aggregate Master DeqOthersleeptime/senderidpasses 
OLAP Aggregate Master EnqOthersleeptime/senderidpasses 
OLAP Null PQ ReasonOthersleeptime/senderidpasses 
OLAP Parallel Temp GrewOthersleeptime/senderidpasses 
OLAP Parallel Temp Grow RequestOthersleeptime/senderidpasses 
OLAP Parallel Temp Grow WaitOthersleeptime/senderidpasses 
OLAP Parallel Type DeqOthersleeptime/senderidpasses 
opishdOther   
parallel recovery coordinator waits for cleanup of slavesIdle   
pending global transaction(s)Otherscans  
pi renounce write completeClusterfile#block# 
pipe getIdlehandle addressbuffer lengthtimeout
pipe putConcurrencyhandle addressrecord lengthtimeout
PL/SQL lock timerIdleduration  
pmon timerIdleduration  
process shutdownOthertypeprocess#waited
process startupOthertypeprocess#waited
PX create serverOthernserverssleeptimeenqueue
PX Deq Credit: free bufferOthersleeptime/senderidpassesqref
PX Deq Credit: need bufferIdlesleeptime/senderidpassesqref
PX Deq Credit: send blkdOthersleeptime/senderidpassesqref
PX Deq: Execute ReplyIdlesleeptime/senderidpasses 
PX Deq: Execution MsgIdlesleeptime/senderidpasses 
PX Deq: Index Merge CloseIdlesleeptime/senderidpasses 
PX Deq: Index Merge ExecuteIdlesleeptime/senderidpasses 
PX Deq: Index Merge ReplyIdlesleeptime/senderidpasses 
PX Deq: Join ACKIdlesleeptime/senderidpasses 
PX Deq: kdcph_maiIdlekdcph_mai  
PX Deq: kdcphc_ackIdlekdcphc_ack  
PX Deq: Msg FragmentIdlesleeptime/senderidpasses 
PX Deq: OLAP Update CloseOthersleeptime/senderidpasses 
PX Deq: OLAP Update ExecuteOthersleeptime/senderidpasses 
PX Deq: OLAP Update ReplyOthersleeptime/senderidpasses 
PX Deq: Par Recov Change VectorIdlesleeptime/senderidpasses 
PX Deq: Par Recov ExecuteIdlesleeptime/senderidpasses 
PX Deq: Par Recov ReplyIdlesleeptime/senderidpasses 
PX Deq: Parse ReplyIdlesleeptime/senderidpasses 
PX Deq: reap creditOther   
PX Deq: Signal ACKOthersleeptime/senderidpasses 
PX Deq: Table Q CloseOthersleeptime/senderidpasses 
PX Deq: Table Q Get KeysOthersleeptime/senderidpasses 
PX Deq: Table Q NormalIdlesleeptime/senderidpasses 
PX Deq: Table Q qrefOthersleeptime/senderidpasses 
PX Deq: Table Q SampleIdlesleeptime/senderidpasses 
PX Deq: Test for msgOthersleeptime/senderidpasses 
PX Deq: Txn Recovery ReplyIdlesleeptime/senderidpasses 
PX Deq: Txn Recovery StartIdlesleeptime/senderidpasses 
PX Deque waitIdlesleeptime/senderidpasses 
PX Idle WaitIdlesleeptime/senderidpasses 
PX Nsq: PQ descriptor queryOther   
PX Nsq: PQ load info queryOther   
PX qref latchOtherfunctionsleeptimeqref
PX Send WaitOther   
PX server shutdownOthernalivesleeptimeloop
PX signal serverOtherserialerrornbusy
PX slave connectionOther   
PX slave releaseOther   
qerex_gdmlOther   
queue messagesIdlequeue idprocess#wait time
Queue Monitor IPC waitIdle   
Queue Monitor Shutdown WaitIdle   
Queue Monitor Slave WaitIdle   
Queue Monitor Task WaitOther   
Queue Monitor WaitIdle   
queue slave messagesOther   
rdbms ipc messageIdletimeout  
rdbms ipc message blockOther   
rdbms ipc replyOtherfrom_processtimeout 
read by other sessionUser I/Ofile#block#class#
recovery area: computing applied logsOther   
recovery area: computing backed up filesOther   
recovery area: computing dropped filesOther   
recovery area: computing identical filesOther   
recovery area: computing obsolete filesOther   
recovery readSystem I/O   
refresh controlfile commandAdministrative   
reliable messageOtherchannel contextchannel handlebroadcast message
Replication Dequeue Othersleeptime/senderidpasses 
resmgr:become activeSchedulerlocation  
resmgr:cpu quantumSchedulerlocation  
resmgr:internal state changeConcurrencylocation  
resmgr:internal state cleanupConcurrencylocation  
resmgr:sessions to exitConcurrencylocation  
retry contact SCN lock masterCluster   
rfc_open_retryOtherDMON waiting to retry configuration file open  
rfi_drcx_site_delOtherDRCX waiting for site to delete metadata  
rfi_insv_shutOtherwait for INSV to shutdown  
rfi_insv_startOtherwait for INSV to start  
rfi_nsv_deldefOtherNSVx to defer delete response message post to DMON  
rfi_nsv_md_closeOtherNSVx metadata file close wait  
rfi_nsv_md_writeOtherNSVx metadata file write wait  
rfi_nsv_postdefOtherNSVx to defer message post to DMON  
rfi_nsv_shutOtherwait for NSVx to shutdown  
rfi_nsv_startOtherwait for NSVx to start  
rfi_recon1Otherletting site register with its local listener before connect ret  
rfi_recon2Otherretrying connection for sending to remote DRCX  
rfm_dmon_last_gaspOtherDMON waiting on the last gasp event  
rfm_dmon_pdeferOtherDMON phase deferral wait  
rfm_dmon_shutOtherwait for DMON to shutdown  
rfm_dmon_timeout_opOtherDMON waiting to timeout an operation  
rfm_pmon_dso_stallOtherPMON delete state object stall  
rfrdb_dbopOtherwaiting for database to be opened  
rfrdb_recon1Otherreconnecting back to new primary site during standby viability c  
rfrdb_recon2Otherwaiting for standby database to be mounted  
rfrdb_try235Otherwaiting for retrying the query to mask ORA-235 error  
rfrla_lapp1Otherwaiting for logical apply engine to initialize  
rfrla_lapp2Otherchecking for logical apply engine run-down progress  
rfrla_lapp3Otherwaiting for new primary to initialize tables  
rfrla_lapp4Otherwaiting for v$logstdby_stats view to be initialized  
rfrla_lapp5Otherwaiting to reconnect to primary that is in BUILD_UP  
rfrm_dbclOtherRSM notifier: waiting for sql latch on db close  
rfrm_dbopOtherRSM notifier: waiting for sql latch on db open  
rfrm_nonzero_sub_countOtherwait for subscriber count to become nonzero  
rfrm_rsm_shutOtherwait for RSMx processes to shutdown  
rfrm_rsm_so_attachOtherwait for RSMx to attach to state object  
rfrm_rsm_startOtherwait for RSMx processes to start  
rfrm_stallOtherRSM stall due to event RSM_STALL  
rfrm_zero_sub_countOtherwait for subscriber count to become zero  
rfrpa_mrpdnOtherwaiting for MRP0 to stop while bringing physical apply engine of  
rfrpa_mrpupOtherwaiting for MRP0 to start while bringing physical apply engine o  
rfrxpt_pdlOtherwaiting for retrying potential dataloss calculation before switc  
rfrxptarcurlogOtherwaiting for logical apply engine to finish initialization  
RFS announceOther   
RFS attachOther   
RFS closeOther   
RFS createOther   
RFS detachOther   
RFS dispatchOther   
RFS pingOther   
RFS random i/oSystem I/O   
RFS registerOther   
RFS sequential i/oSystem I/O   
RFS writeSystem I/O   
rollback operations activeOtheroperation count  
rollback operations block fullOthermax operations  
row cache lockConcurrencycache idmoderequest
row cache readConcurrencycache idaddresstimes
RVWR wait for flashback copyOthercopy latch #  
scginq AST callOther   
secondary eventOtherevent #wait time 
select waitOther   
simulated log write delayOther   
single-task messageIdle   
slave exitOthernalivesleeptimeloop
slave shutdown waitOther   
slave TJ process waitOther   
smon timerIdlesleep timefailed 
sort segment requestConfiguration   
SQL*Net break/reset to clientApplicationdriver idbreak? 
SQL*Net break/reset to dblinkApplicationdriver idbreak? 
SQL*Net message from clientIdledriver id#bytes 
SQL*Net message from dblinkIdledriver id#bytes 
SQL*Net message to clientNetworkdriver id#bytes 
SQL*Net message to dblinkNetworkdriver id#bytes 
SQL*Net more data from clientNetworkdriver id#bytes 
SQL*Net more data from dblinkNetworkdriver id#bytes 
SQL*Net more data to clientNetworkdriver id#bytes 
SQL*Net more data to dblinkNetworkdriver id#bytes 
statement suspended, wait error to be clearedIdle   
STREAMS apply coord waiting for slave messageIdle   
STREAMS apply slave idle waitIdle   
STREAMS apply slave waiting for coord messageApplication   
STREAMS capture process filter callback wait for rulesetIdle   
STREAMS capture process waiting for archive logOther   
STREAMS fetch slave waiting for txnsIdle   
Streams: Wait for inter instance ackOther   
Streams: Wating for DDL to applyApplicationsleep time  
switch logfile commandAdministrative   
switch undo - offlineAdministrative   
SWRF RWM Auto Capture EventOther   
SWRF Wait on FlushingOther   
Sync ASM rebalanceOther   
test long opsOther   
timer in sksawatOther   
trace continueOtherdelay time  
trace unfreezeOther   
trace writer flushOther   
trace writer I/OOther   
transactionOtherundo seg#|slot#wrap#count
txn to completeOther   
unbound txOther   
undo segment extensionConfigurationsegment#  
undo segment recoveryOthersegment#tx flags 
undo segment tx slotConfigurationsegment#  
unspecified wait eventOther   
virtual circuit statusIdlecircuit#status 
wait active processesOther   
wait for a paralle reco to abortOther   
wait for a undo recordOther   
wait for activate messageIdle   
wait for another txn - rollback to savepointOther   
wait for another txn - txn abortOther   
wait for another txn - undo rcv abortOther   
wait for assert messages to be sentOther   
wait for changeOther   
Wait for Dictionary Build to lock all tablesOther   
wait for EMON to dieOther   
wait for EMON to process ntfnsConfiguration   
wait for EMON to spawnOther   
wait for FMON to come upOther   
wait for Logical Standby Apply shutdownOther   
wait for master scnOtherwaittimestartscnackscn
wait for membership synchronizationOther   
wait for message ackOther   
wait for MTTR advisory state objectOther   
wait for possible quiesce finishAdministrative   
wait for record updateOther   
wait for resize request completionOther   
wait for rr lock releaseOther   
wait for scn ackOtherpending_ndscnwrpscnbas
wait for SGA component shrinkOthercomponent idcurrent sizetarget size
wait for sga_target resizeOther   
Wait for shrink lockOtherobject_idlock_mode 
Wait for shrink lock2Otherobject_idlock_mode 
wait for split-brain resolutionOther   
wait for stopper event to be increasedOther   
wait for sync ackOthercluincpending_nd 
Wait for Table LockApplication   
wait for tmc2 to completeOther   
wait for transactionIdle   
Wait for TT enqueueOthertsn  
wait for unread message on broadcast channelIdlechannel contextchannel handle 
wait for unread message on multiple broadcast channelsIdlechannel contextchannel handle count 
wait for verification ackOthercluincpending_insts 
wait for votesOther   
wait list latch activityOtheraddressnumberprocess#
wait list latch freeOtheraddressnumbertries
Wait on stby instance closeOther   
waiting for low memory condition to be resolvedIdle   
waiting for subscribers to catch upIdle   
waiting to get CAS latchOther   
waiting to get RM CAS latchOther   
wakeup blocked enqueuersOther   
wakeup event for builderIdle   
wakeup event for preparerIdle   
wakeup event for readerIdle   
wakeup time managerIdle   
write complete waitsConfigurationfile#block# 
writes stopped by instance recovery or database suspensionOtherby thread#our thread# 


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