Logo Background

Monitor Oracle Session Waits

  • By on November 14, 2008 | No Comments

    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 = '&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;
    Previous
    Next
    » Oracle Process CPU Utilization
Leave a Comment