How to resolve long running concurrent request in oracle apps

How to resolve long running concurrent request in oracle apps

Here we will be discuss about how to resolve Long running concurrent requests in oracle apps. Long running concurrent requests can impact the system performance so we need to keep monitor on the concurrent requests which are keep running from log time but to identify the long running concurrent requests is not the task , We also need to identify the reason for this. here below I will share the most common ways to resolve long running concurrent request in oracle apps.

 

 



Common Methods to resolve long running concurrent request in oracle apps

1. There could be the reason , Your concurrent request report query is not fine tune. so we need to generate the TK proof for the Concurrent Program and analyze the all parts of the report where this report is taking so much time so that we can work on that part of the report to more fine tune this report query.

 

2.We need to check is there any database lock happened for this Concurrent request or session id.

 

3.We Can kill the session for this report and then manually complete this Concurrent request to be completed.

 

Find Long Running concurrent Requests with Session and Serial Id.

 

 

select distinct vp.spid "SPID",qt.user_concurrent_queue_name "Queue Name"
       ,c2.user_concurrent_program_name "Concurrent Program Name"
      ,a.request_id "Request Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
      ,FLV.meaning
      ,FLVS.meaning
      ,a.argument_text
      ,vs.inst_id
      ,vs.sid "SID"
      ,vs.serial# "Serial#"
      ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
      ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "TimeSpent"
      ,u.user_name
   from APPS.fnd_Concurrent_requests a,APPS.fnd_concurrent_processes b
    ,APPS.fnd_concurrent_queues q
    ,APPS.fnd_concurrent_queues_tl qt
    ,APPS.fnd_concurrent_programs_tl c2
    ,APPS.fnd_concurrent_programs c
    ,APPS.FND_LOOKUP_VALUES FLV
    ,APPS.FND_LOOKUP_VALUES FLVS
    ,APPS.FND_USER u
    ,gv$session vs
    ,gv$process vp
   where a.controlling_manager = b.concurrent_process_id
     and a.concurrent_program_id = c.concurrent_program_id
     and u.user_id=a.requested_by
     and a.phase_code=FLV.Lookup_Code
     and FLV.Lookup_Type='CP_PHASE_CODE'
     and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > :P_TIME_LIMIT
     and FLV.language='US'
     and a.status_code=FLVS.Lookup_Code
     and FLVS.Lookup_Type='CP_STATUS_CODE'
     and FLVS.language='US'
     and FLVS.view_application_id=0
     and b.queue_application_id = q.application_id
     and b.concurrent_queue_id = q.concurrent_queue_id
     and q.application_id = qt.application_id
     and qt.language='US'
     and q.concurrent_queue_id = qt.concurrent_queue_id
     and c2.language = 'US'
     and a.oracle_process_id = vp.spid
     and vs.paddr = vp.addr (+)
     and a.status_code='R'
     and vs.inst_id=vp.inst_id
     and a.program_application_id = c.application_id
     and c2.concurrent_program_id = c.concurrent_program_id
     and a.phase_code in ('I','P','R','T')
   order by 1

 

 

--ALTER SYSTEM KILL SESSION 'SID, serial#' IMMEDIATE;
ALTER SYSTEM KILL SESSION '523, 4965' IMMEDIATE;

 

 

update fnd_concurrent_requests
   set status_code='X',

    phase_code='C'
   where request_id=:P_CONCURRENT_REQUEST_ID