The Oracle RDBMS provides the use of the following view to determine most costly wait events since instance startup: V$SESSION_WAIT_HISTORY. This view can be used to determine possible problem areas in the database. This article covers how to determine the top wait events in the Oracle database since system startup. 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 Tues Jan 26 20:41:52 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 top wait events in the database since startup.
select distinct(a.event) as event, (select count(*) from v$session_wait_history
b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc;
SQL> select distinct(a.event) as event, (select count(*) from
v$session_wait_history b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc; 2
EVENT TOT_WAITS
————————– ———-
jobq slave wait 90
rdbms ipc message 81
PX Deq: Execution Msg 54
PX Deq: Table Q Normal 20
…
…
…
15 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