DBA Arena

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:%');
Read more...

Cause:

Blocking sessions occur when one session holds an exclusive lock on an object and doesn't release it before another session wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.

Solution:

The following query shows all the blocking sessions which can help you to identify the problem.

col WAIT_CLASS for a12;
select blocking_session,sid,serial#,wait_class,seconds_in_wait,status from v$session where blocking_session is not NULL order by blocking_session;
Read more...