Oracle v$session_event

Inside V$Session_Event

The v$session_event view shows the cumulative time that each session has spent waiting for a particular event to complete.  Unlike the v$session_wait view, the v$session_event view collects aggregate wait information, organized by System ID (SID) and a named event.

SQL> desc v$session_event

Name              Null?    Type

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

SID                        NUMBER

EVENT                      VARCHAR2(64)

TOTAL_WAITS                NUMBER

TOTAL_TIMEOUTS             NUMBER

TIME_WAITED                NUMBER

AVERAGE_WAIT               NUMBER

MAX_WAIT                   NUMBER

It will be useful to look at examples of how the v$session_event view might be used in real life. In this example, end users have started complaining about experiencing large delays when running a production application.

The following script, events.sql, will generate a report on current Oracle events. The script, sys_events_pct.sql, which then follows, provides a more detailed view of the event profile by adding the CPU contribution and calculating the overall percentages each wait is contributing.

COLUMN sid HEADING Sid
COLUMN event HEADING Event FORMAT a40
COLUMN total_waits HEADING Total|Waits
COLUMN total_timeouts HEADING Total|Timeouts
COLUMN time_waited HEADING Time|Waited
COLUMN average_wait HEADING Average|Wait
COLUMN username HEADING User

BREAK ON username
ttitle "Session Events By User"
SPOOL events
SET LINES 132 PAGES 59 VERIFY OFF FEEDBACK OFF

SELECT
   username,
   event,
   total_waits,total_timeouts,
   time_waited,average_wait
FROM
   sys.v_$session_event a,
   sys.v_$session b
WHERE
   a.sid= b.sid
   ORDER BY 1;

Notice that the report is by username. This can help isolate which database users are generating the most wait events. However, it can be more useful to capture the events as a percentage of all wait time, including that for the CPU usage time. Note that on multi-CPU systems the CPU usage may be skewed high and the need may exist to divide by the number of CPUs to get a useful number for the current CPU used by a session. Next, a different cut of this report that uses a percentage calculation to show the relative weight of each event by percent will be reviewed.

In some ERP applications (Oracle Applications, SAP), a single user account is used to connect to the database. In these cases, the DBA can issue the following statement to determine the particular event application for which the sessions are waiting:

The output of this script might look like the following:

                  Waits for user SAPR3

                                            SUM   SUM         TIME

EVENT                                     WAITS  TIMEOUTS    WAITED

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

SQL*Net E "SQL*Net"  message to client      7,824   0         .06

SQL*Net E "SQL*Net"  message from client    7,812   0  312,969.73

db file sequential read                     3,199   0       16.23

SQL*Net E "SQL*Net"  more data to client      590   0         .08

SQL*Net E "SQL*Net"  break/reset to client    418   0          .2

direct path read                              328   0         .01

SQL*Net E "SQL*Net"  more data from client     78   0        3.29

latch free                                     62  10         .08

db file scattered read                         56   0         .75

log file sync                                  47   0         .96

direct path write                              32   0          .4

file open                                      32   0           0

library cache pin                              13   0           0

log file switch completion                      3   0         .53

From the listing above, the DBA can conclude that end users spend most of their wait time waiting on the event SQL*Net message from client. This may indicate that there is some network-related issue causing clients too much wait time to send data to the database server. 

Unlike these old-fashioned v$session   and v$session_wait   accumulation views where waits can only be seen at the exact instant when they occurred, the new v$session_wait_history  and v$sys_time_model  views allow Oracle10g to capture system waits details in a time-series mode. The following section will provide a look at these new ASH table structures and see how time series wait event tuning gives unprecedented insights.

COLUMN wait_class format a20
COLUMN name       format a30
COLUMN time_secs  format 999,999,999,999.99
COLUMN pct        format 99.99

SELECT   
   wait_class,
   NAME,
   ROUND (time_secs, 2) time_secs,
   ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
   (SELECT
      n.wait_class,
      e.event NAME,
      e.time_waited / 100 time_secs
    FROM
      v$system_event e,
      v$event_name n
    WHERE
       n.NAME = e.event AND n.wait_class <> 'Idle'
    AND
       time_waited > 0
    UNION
    SELECT
      'CPU',
      'server CPU',
      SUM (VALUE / 1000000) time_secs
    FROM
      v$sys_time_model
    WHERE
      stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY
   time_secs DESC;

Also see v$session_event with ash