Idle Process In Oracle

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s