Oracle Processes CPU Usage

Is that long running job doing anything? This SQL query (Oracle 11) will list user processes and the amount of CPU usage the current job has used so far. Re-query to see how the CPU usage progesses.

SELECT vs.username, vs.sid, vs.serial#, vp.spid, vs.machine, vs.module, vs.program, vs.action, vs.status, vs.last_call_et, ROUND((vss_sess.value-vss_call.value)/100) "CPU Usage" FROM gv$session vs, gv$process vp, v$sesstat vss_sess, v$sesstat vss_call WHERE vs.paddr = vp.addr(+) AND vs.inst_id = vp.inst_id(+) AND vs.sid = vss_sess.sid AND vs.sid = vss_call.sid AND vss_call.statistic# = 13 -- CPU used when call started AND vss_sess.statistic# = 14 -- CPU used by this session AND vs.username IS NOT NULL ORDER BY ( CASE vs.status WHEN 'INACTIVE' THEN 0 ELSE vss_sess.value-vss_call.value END ) DESC, vs.status, vs.last_call_et;

Maybe useful here...

Several ways to kill a process.

The kill session syntax. The immediate option will not kill the session faster, however it will return the prompt immediately after the statement has been issued.

ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

The kill session statement asks the session to end, whereas the disconnect session statement cuts the session process.

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The post transaction option is equivilent to kill session.

For those even more stubborn processes, you need to move to the operating system, using the spid value from the query above.

In Windows:

orakill ORACLE_SID spid

In UNIX:

kill -9 spid

Done and Killed (hopefully).