Killing runaway processes after terminating concurrent Request

Written By askMLabs on Monday, October 12, 2009 | 8:58 PM

Killing runaway processes after terminating concurrent Request:

Every concurrent Request uses some resources for running. If you feel 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
, 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;

Share this article :

+ comments + 2 comments

Bhanu

September 17, 2010 at 2:14 PM

Hi Sri,

Can you provide a query which actually gives the list of pending requests for eac and every manager at single shot without the need of logging to front end.

Thanks
Bhanu Prakash

learnwithme11g

September 17, 2010 at 3:30 PM

Bhanu,
If my understanding is correct, there is no concept of pending request with the concurrent managers. When ever you submit a request, its information is written to fnd_concurrent_requests. Till the relavant concurrent manager picks up this request from the table, the request will be with either "INACTIVE" or "PENDING" status. So when this particular request is assigned to a conc manager, it changes its status.

PENDING/Normal -Request is waiting for the next available manager.
PENDING/Standby-Program to run request is incompatible with other program currently running.
PENDING/Scheduled-Request is scheduled to start at a future time or date.
PENDING/Waiting-A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete.

INACTIVE/Disabled-Program to run request is not enabled. Contact your system administrator.
INACTIVE/On Hold-Pending request is placed on hold by choosing the Hold Request button in the Requests window.
INACTIVE/No Manager-No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.

Concurrent manager will be running in the background waiting for a concurrent program to be submitted. As soon as a concurrent program is submitted, it then gets put in an execution queue by concurrent manager.
Because at any given point in time a concurrent manager can run no more than say 10 programs concurrently. This figure of 10 is configurable of course. First the manager puts a submitted program into a queue, next the manager checks if there is a slot available (i.e. Less than 10 programs are currently running). If a slot is found available, the concurrent manager then runs the program, or else it keeps the concurrent program in a queue with status Pending.

So to find out the request/conc prog which are in pending status, Use

col user_concurrent_program_name for a50
set linesize 100
select fcp.user_concurrent_program_name,fcr.phase_code,fcr.status_code,fcr.request_id
from fnd_concurrent_programs_tl fcp, fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id and fcr.phase_code='p' order by user_concurrent_program_name;

The phase codes can be found here:
select lookup_code, meaning from apps.fnd_lookup_values
where view_application_id>0
and LOOKUP_TYPE = 'CP_PHASE_CODE';

Status codes here:
select lookup_code, meaning from apps.fnd_lookup_values
where view_application_id>0
and LOOKUP_TYPE = 'CP_STATUS_CODE';

If you see more pending requests in your applications, then you need to change the concurrent manager definition to process more requests concurrently. This can be done in two ways ,
Concurrent -> Manager -> Define :

1)Increase the Number of Target processes for the manager
2)Change the cache size of the concurrent manager as this determines how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.

Thanks

askMLabs