Oracle monitors the wait events which occur in a database in two ways: Session level and System level. Session level wait events records events which affect a single user activities within the database. System level wait events records all events which affect the entire database system and all user activities within it. When considering which to use during active performance issues, it is normally best to concentrate on session level which would show which user is actually causing the problem. However, situations such as loss of a hard disk would appear across all session and thus it would be best to monitor from a system level. In this article we will examine how to find the top system wait events occurring. This procedure will work regardless of OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 01 22:14:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. For this procedure we will be using the SYS view of V_$SYSTEM_EVENT, to see what is causing possible database slowdown. Below is a description of the view:

SQL> desc V_$SYSTEM_EVENT
Name Null? Type
—————————————– ——– —————————-
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

4. First, let us look at retrieving the TOTAL_WAITS for a particular event, execute the following SQL: select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc

SQL> select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc
2 ;

EVENT TOTAL_WAITS
——————————- ———–
rdbms ipc message 59943
PX Idle Wait 19430
db file sequential read 12983
control file sequential read 12177
SQL*Net more data from dblink 9250

5. Second, you see the TOTAL_TIMEOUTS (halt of work) due to wait events.

select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1 order
by TOTAL_TIMEOUTS desc;

SQL> select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1
order by TOTAL_TIMEOUTS desc;

EVENT TOTAL_TIMEOUTS
———————– ————–
rdbms ipc message 59949
PX Idle Wait 19701
pmon timer 6339
PX qref latch 1954
jobq slave wait 1408

6. Third, we see the total TIME_WAITED for a type of event, recorded in milisecond.

select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by
TIME_WAITED desc;

EVENT TIME_WAITED
——————— ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

7. Finally, we get the average time in miliseconds of a wait event.

select event, AVERAGE_WAIT from V_$SYSTEM_EVENT where average_wait>.1
AVERAGE_WAIT order by desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

EVENT TIME_WAITED
————————- ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

The above information can be used to fine tune the Oracle RDBMS and improve the performance of your system.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com