Logo Background

Oracle Process CPU Utilization

  • By on November 14, 2008 | No Comments

    Bad written SQL usually will hunger for CPU resource. As APPS DBA, we should monitor the CPU utilization per process to avoid the database load going high like skyrocket. Consider end those high CPU process consumption by killing the SID in database level.

    SQL>SELECT ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time,
    w.seq#, q.sql_text,command
    v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
    WHERE ss.statistic# IN
    (SELECT statistic#
    FROM v$statname
    WHERE name = 'CPU used by this session')
    AND se.sid=ss.sid
    AND ss.sid>6
    AND se.paddr=p.addr
    AND se.sql_address=q.address
    ORDER BY ss.value DESC,ss.sid;

    Check as well the session with highest CPU consumption in the last 4 hours. You can adjust the time parameter to get your desired result.

    SQL>SELECT s.sid, s.serial#, p.spid AS "OS PID",s.username,
    s.module, st.value/100 AS "CPU sec"
    FROM v$sesstat st, v$statname sn, v$session s, v$process p
    WHERE sn.name = 'CPU used by this session' -- CPU
    AND st.statistic# = sn.statistic#
    AND st.sid = s.sid
    AND s.paddr = p.addr
    AND s.last_call_et <>(SYSDATE - 240/1440) -- sessions logged on within 4 hours
    ORDER BY st.value;
    » Oracle HTTP Server Component
Leave a Comment