Wait Event Enhancements in Oracle 10g（zt）

Integrigy Corporation Oracle, Database, audit, security, Session, v$session, sys_context, fine grained auditing, fga 11.9999 Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

by Terry Sutton and Roger Schrag
Database Specialists, Inc.

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 .)

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 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 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26651/viewspace-1040984/，如需轉載，請註明出處，否則將追究法律責任。