Monitor Oracle Session Waits

Whenever your Oracle Applications running slow, the Oracle session waits were the first thing to be monitor in Oracle Database. Session waits means that your running process were queuing for next run or perhaps waiting for next free block to be allocated to your running process. Monitor your session waits using this query: -

SQL> SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;

In RAC (Real Application Cluster) environment, the monitoring were slightly different than single environment. Please refer the script below for the Oracle session waits monitoring in RAC database environment.

SQL> SELECT s.INST_ID, s.username, s.osuser, s.server, s.machine, s.serial#, w.*
FROM gv$session_wait w, gv$session s
WHERE s.sid=w.sid AND w.event NOT IN
( 'SQL*Net message from client', 'rdbms ipc message', 'jobq slave wait')
ORDER BY s.username;

Check out the session ID that use the highest waiting time in the last 4 hours.

SQL> SELECT s.sid, s.serial#, p.spid AS "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&amp;event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

« Database Session Memory Usage

» Oracle Process CPU Utilization

Monitor Oracle Session Waits

Related Posts

Recent Posts