How to find long running concurrent request in oracle apps

How to find long running concurrent request in oracle apps

Here I will share the some of the methods to find the long running concurrent request in oracle apps. This is all depend on your business Requirment , to treat which concurrent request as a long Running request in the application. For Example , If any request is Running from Last 3 hours and its still not completed then you will be treat it like a long running concurrent request but may be for some other business it could 5 hours long running request so its all depend upon business to business. Here below is the complete description about how to find long running concurrent request in oracle apps.

 

2 Methods to find long running concurrent request in oracle apps

1. Concurrent Request Window (Front End)

2. Write Some SQL queries to Fetch the Long Running Concurrent Request Data (Back End)

 

 

 

Concurrent Request Window (Front End):-

Go to the System Administrator Responsibility ==> Concurrent ==> Requests 

 

Here you can find all the Concurrent requests running in the Application   

 

Here below you can search for Specific Request.

Click on the Specific Request Radio button.

Enter the Status 'Running' and Click on Find.

 

 

 

2.Write Some SQL queries to Fetch the Long Running Concurrent Request Data (Back End)

 

 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