Most of the time we look at the processes that consume more cpu, more i / o, more buffer gets, etc. But it is also good idea to look for processes that do nothing, especially in large database systems with hundreds or thousands of sessions that are idle. Although they consume many resources, necessary to maintain, manage, monitor them, manage DTD if is active …. In most cases it is healthy they are not. I include below a small script that tells us which carry more than an hour without doing anything. It’s just an example that you can modify to your liking.
-- This script is for platform UNIX/Linux SELECT S.USERNAME, SPID AS "UNIX PID", 'ps -ef | grep -v ' || SPID AS "WHAT", 'kill -9 ' || SPID AS "KILL FROM OS", 'ALTER SYSTEM KILL SESSION '||''''||S.SID||','||S.SERIAL#||''';' "KILL FROM DB", TO_CHAR(TRUNC(LAST_CALL_ET/3600,0))||' '||' HOURS '|| TO_CHAR(TRUNC((LAST_CALL_ET - TRUNC(LAST_CALL_ET/3600,0)*3600) / 60,0))|| ' MINS IDLE' AS "TIME IDLE" FROM V$SESSION S, V$PROCESS P WHERE TYPE='USER' AND P.ADDR=S.PADDR AND EVENT <> 'SQL*Net message from client' AND LAST_CALL_ET > 3600 -- more than one hour without activity ORDER BY LAST_CALL_ET DESC /
HTH – Antonio NAVARRO