Oracle Process CPU UtilizationOracle Process CPU Utilization
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 FROM 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;
Leave a Comment
Recent Comments