How to kill long running/hang concurrent request

How To Kill runaway processes After Terminating
==============================================

Concurrent Request:

Every concurrent Request uses some resources for running. If we find that the
concurrent request is taking long time and decided to terminate the concurrent
request , the resources may not be released soon. These processes are called
runaway processes. So we need to manually kill the processes at database and os
level to have the resources released to the system.
Terminate the concurrent request from the front end. Then

SQL>select request_id,oracle_process_id,os_process_id from
fnd_concurrent_requests where request_id=’&Req_Id’;


SQL>select p.spid , s.sid , s.serial# from v$session s , v$process p where s.paddr
= p.addr and s.process = &os_process_id ;

SQL> alter system kill session ‘session-id,session-serial’


$ kill -9 <server pid>


Complete details about the request can be found using the following query :
SELECT qt.user_concurrent_queue_name
, fcr.Request_Id Request_id
, fu.User_name
, p.spid
, s.sid ||’, ‘|| s.serial# SIDSERIAL
, substr( Fcpv.Concurrent_Program_Name ||’ – ‘||
Fcpv.User_Concurrent_Program_Name, 1,46) Program
, to_char( fcr.actual_start_date, ‘mm/dd hh24:mi’ ) actual_start_date
, phase_code, status_code
, to_char( trunc(sysdate) + ( sysdate – fcr.actual_start_date )
, ‘hh24:mi:ss’ ) duration
FROM apps.Fnd_Concurrent_Queues Fcq
, apps.fnd_concurrent_queues_tl qt
, apps.Fnd_Concurrent_Requests Fcr

Source –http://appsdba.info
, apps.Fnd_Concurrent_Programs Fcp
, apps.Fnd_User Fu
, apps.Fnd_Concurrent_Processes Fpro
, v$session s
, v$process p
, apps.Fnd_Concurrent_Programs_Vl Fcpv
WHERE phase_code = ‘C’
AND status_Code = ‘X’
AND s.paddr = p.addr
AND fcr.requested_by = user_id
AND fcq.application_id = qt.application_id
AND fcq.concurrent_queue_id = qt.concurrent_queue_id
AND userenv(’lang’) = qt.language
AND fcr.os_process_id = s.process
AND fcr.Controlling_Manager = Concurrent_Process_Id
AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id
AND fcq.application_id = fpro.queue_application_id )
AND (fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id )
AND (fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id )
ORDER BY fcr.actual_start_date;





========================*********************=======================================

How to kill long running/hang concurrent request
================================================

When concurrent program taking more cpu utlization and can't open front-end application also that time we are going to this activity.


SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id =11613265);


You will get spid & pid from above request. Pass your request id in above query.


And run below query with sys user.





SQL> ALTER SYSTEM KILL SESSION ' 1114, 8017' IMMEDIATE;

System altered.


Issue: Request is in pending from long time,




Action:
#First Terminate the Request as follows

update fnd_concurrent_requests
   set status_code='X', phase_code='C'
   where request_id=31783706;

commit;

#Then change the status with Completed-Error as follows.

update fnd_concurrent_requests
   set status_code='E', phase_code='C'
   where request_id=31783706;

commit;

#This will change the status of any request.
#Status Code
E -  Error
X -  Terminate
G -  Warning




===============


(or)


Find below query and Kill oracle_process_id and Os_process_id

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,oracle_process_id,os_process_id,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.FND_CONCURRENT_PROGRAMS_TL c,
    apps.fnd_user d
WHERE   a.concurrent_program_id=b.concurrent_program_id AND
    b.concurrent_program_id=c.concurrent_program_id AND
    a.requested_by=d.user_id AND
    status_code='R' order by Process_time desc;


kill -9 <oracle_process_id>

kill -9 <os_process_id>
 
 
 update fnd_concurrent_requests set status_code='X', phase_code='C' where request_id='21889851';