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:
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
tsu...@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 rsc...@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
阿飞
jx wn
>From: "闃块" <rocfy...@gmail.com>
>To: "unix_db" <uni...@googlegroups.com>
>Subject: Re: Wait Event Enhancements in Oracle 10g
>Date: Sun, 10 Sep 2006 09:30:59 -0000
>
>鍏蜂綋璇锋煡鐪
?>http://www.dbspecialists.com/presentations/wait_events_10g.html銆€涓烘簮璐
达紒
>
>>
_________________________________________________________________
享用世界上最大的电子邮件系统— MSN Hotmail。 http://www.hotmail.com