Description:
Sometimes after closing the Oracle EBS the respective form sessions still run at O.S level and tend to take up heavy CPU resources impacting performance.When there is heavy load on the server and the application is running slow, it is better to kill these processes from back end to release CPU resources taken by inactive sessions.
The query given below finds form sessions that have been inactive for more than 8 hours.
It provides a formatted output where you just have to copy and paste it at the O.S level.
set pagesize 1200;
set linesize 1200;
select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select sid from v$session where status like 'INACTIVE' and logon_time < sysdate-0.33 and action like 'FRM:%');
'KILL-9'||P.SPID
--------------------
kill -9 23330
kill -9 22598
kill -9 22598
kill -9 22598
kill -9 7415
kill -9 7415
kill -9 2109
kill -9 606
kill -9 20241
kill -9 4804
kill -9 24783
kill -9 17592
kill -9 6384
kill -9 29714
kill -9 29714
kill -9 19519
kill -9 22387
kill -9 22387
kill -9 22387
kill -9 29748
kill -9 11106
kill -9 11106
kill -9 15136
23 rows selected.
SQL> exit
Execute the above commands at O.S level
oratest#kill -9 23330
oratest#kill -9 22598
oratest#kill -9 22598
oratest#kill -9 22598
oratest#kill -9 7415
oratest#kill -9 7415
bash: kill: (7415) - No such process
oratest#kill -9 2109
oratest#kill -9 606
oratest#kill -9 20241
oratest#kill -9 4804
oratest#kill -9 24783
oratest#kill -9 17592
oratest#kill -9 6384
oratest#kill -9 29714
oratest#kill -9 29714
oratest#kill -9 19519
oratest#kill -9 22387
oratest#kill -9 22387
oratest#kill -9 22387
oratest#kill -9 29748
oratest#kill -9 11106
oratest#kill -9 11106
oratest#kill -9 15136
SQL> select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select sid from v$session where status like 'INACTIVE' and logon_time < sysdate-0.33 and action like 'FRM:%');
no rows selected
You can change the time from 8 hours to a greater or lesser value depending on the load.
Change the parameter sysdate-0.33 for different hours.
6 hours: 0.25
3 hours: 0.12
2 hours: 0.08
1 hour: 0.04
30 Minutes: 0.013