Re: find Stale process

From: srivenu <srivenu_at_hotmail.com>
Date: 5 Sep 2002 03:21:30 -0700
Message-ID: <1a68177.0209050221.155cd6cc@posting.google.com>

1. On HP you can use TOP to see the CPU consumption of the unix processes.
2. I think you are running in Dedicated Server mode and by stale processes he meant idle sessions.

You can use this query to find "STALE" sessions.

set linesize 95
col name form a35
col value form a20
set head off
set feedback off
select ' '||upper(name)name,value from v$parameter
where name = 'timed_statistics'
/

set feedback on
set head on
col event form a13 trunc head "Event| Waiting For" col p2 form 99999 trunc
col wait_time form 999 trunc head "Last|Wait|Time" col program form a13 trunc
col command form a7 trunc head "Command" col username form a8 trunc
col state form a10
col sid form 999 trunc
col last_call_et form 9999999 trunc head "Last Call|In Secs" select a.sid,username,b.program program,

	 decode(command,0,'None',2,'Insert',3,'Select',
		 6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',
		 45,'Rollback',47,'PL/SQL',command) command,
	 last_call_et,
	 event,p1,p2,state,wait_time

from v$session_wait a,V$session b
where b.sid=a.sid
order by 1
/

The rows with null usernames are for background processes. So the sessions with high "Last Call In Secs" and the wait event of "SQL*Net message from client" are stale.

regards
Srivenu Received on Thu Sep 05 2002 - 05:21:30 CDT