Oracle Real-Time Wait Events v$system_event

Oracle Real-Time Wait Events v$system_event 

While tuning disk I/O waits is an important task, it should not be considered as a comprehensive approach to Oracle tuning.

The DBA must start by learning about the specific tables and indexes that are associated with the waits.  Since the v$ views are accumulators, one can only see the sum the total number of waits since the instance started.

The v$session_wait view is a great place to start.  As disk read waits occur within the Oracle database, they appear in the v$session_wait view for a very short period of time.

Prior to the Automated Session History (ASH) table in Oracle10g, it was impossible to catch all of the run time waits because of the transient appearance of read waits.  Regardless, it is possible to take a frequent sample of the v$session_wait view and catch a representative sample of the system-waits details at the exact moment that the events occur.

Determining the exact table or index where the wait occurred, when the file and block number are available, is also credible.  Therefore, the enticing thing about the v$session_wait view is that the exact time the wait occurred can be captured as well as the file and block number that was being waited upon.

This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

select *
from
   v$system_event
where
   event like ‘%wait%’;

 EVENT                TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

--------------------- ----------- -------------- ----------- ------------

buffer busy waits          636528           1557      549700   .863591232

write complete waits         1193              0       14799   12.4048617

free buffer waits            1601              0         622   .388507183

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where count is the sum of all waits for the class of block, and time is the sum of all wait times for that class:

select *
from
 v$waitstat;

CLASS                   COUNT       TIME

 ------------------ ---------- ----------

 data block            1961113    1870278

 segment header          34535     159082

 undo header            233632      86239

 undo block               1886       1706

Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

SQL> desc v$session_wait

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------

 SID                                                NUMBER

 SEQ#                                               NUMBER

 EVENT                                              VARCHAR2(64)

 P1TEXT                                             VARCHAR2(64)

 P1                                                 NUMBER

 P1RAW                                              RAW(4)

 P2TEXT                                             VARCHAR2(64)

 P2                                                 NUMBER

 P2RAW                                              RAW(4)

 P3TEXT                                             VARCHAR2(64)

 P3                                                 NUMBER

 P3RAW                                              RAW(4)

 WAIT_TIME                                          NUMBER

 SECONDS_IN_WAIT                                    NUMBER

 STATE                                              VARCHAR2(19)

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

The following is an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where   
event = 'buffer busy waits';

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should be used to show the name and type of the segment:

Once the segment is identified, the v$segment_statistics performance view facilitates real time monitoring of segment level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

select
    object_name,
    statistic_name,
    value
from
    V$SEGMENT_STATISTICS
where
    object_name = 'SOURCE$';

The output looks like the following:

OBJECT_NAME   STATISTIC_NAME               VALUE

-----------  -------------------------     ----------

SOURCE$       logical reads                     11216

SOURCE$       buffer busy waits                   210

SOURCE$       db block changes                     32

SOURCE$       physical reads                    10365

SOURCE$       physical writes                       0

SOURCE$       physical reads direct                 0

SOURCE$       physical writes direct                0

SOURCE$       ITL waits                             0

SOURCE$       row lock waits

The output of this script, in the Ion tool, looks like the following:

The main wait class within ASH that relates to user I/O can be determined with the following script:

select  A.SID,
        B.USERNAME,
        A.WAIT_CLASS,
        A.TOTAL_WAITS,
        A.TIME_WAITED
from    V$SESSION_WAIT_CLASS A,
        V$SESSION B
where   B.SID = A.SID
and
        B.USERNAME IS NOT NULL
and
        A.WAIT_CLASS like ‘%I/O%’
order by 1,2,3;

Its output looks like this:

       SID USERNAME             WAIT_CLASS           TOTAL_WAITS TIME_WAITED

---------- -------------------- -------------------- ----------- -----------

       131 SYSMAN               User I/O                      11           5

       132 SYSMAN               User I/O                       3           2

       133 SYSMAN               User I/O                      13           7

       134 SYSMAN               User I/O                     173          97

       138 SPV                  User I/O                      10           3

       140 SYSMAN               User I/O                     387         413

       141 DBSNMP               User I/O                     201         433

       142 SYSMAN               User I/O                      36          71

       144 SYSMAN               User I/O                       6           7

       146 SYSMAN               User I/O                      35          28

       149 SYSMAN               User I/O                      46          42

       154 DBSNMP               System I/O                    84          68

       160 SYS                  System I/O                     5           3

       162 SYSMAN               User I/O                      16          10

SEE CODE DEPOT FOR FULL SCRIPTS