The Oracle RDBMS provides the use of the following view to determine most costly wait events currently being experience by users: V$SESSION_WAIT. V$SESSION_WAIT contains all the current wait events which are active. This view can be used to determine what is causing an issue while experiencing a system slow down. This article covers how to determine the top wait events in the Oracle database. 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 Sun Jan 10 10:28:02 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. Execute the following SQL to extract the current top wait events in the database.
select distinct(a.event) as event, (select count(*) from v$session_wait b where
a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc;
SQL> select distinct(a.event) as event, (select count(*) from v$session_wait b
where a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc; 2
EVENT TOT_WAITS
————————- ———-
rdbms ipc message 9
Streams AQ: waiting for time management or cleanup tasks 2
SQL*Net message to client 1
…
…
…
7 rows selected.
SQL>
4. This information can be used to further inspect the system to determine the cause of performance loss.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com