Query To Check Long Running Concurrent Requests

Download(longrunner.sql)

REM +======================================================================+
REM                    
REM File Name: longrunner.sql
REM 
REM Description:
REM   Query To Check Long Running Concurrent Requests
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @longrunner.sql 
REM
REM   This Query Requires Time Duration(THRESHOLD) for Long Running Request 
REM   in Minutes as an Input
REM   
REM +======================================================================+

set lines 180
ACCEPT THRESHOLD PROMPT "Enter Time Duration(THRESHOLD) for Long Running Request in Minutes: ";

clear columns
 set lines 180
 set pages 100
 col "Start Date" for a20
 col "Queue Name" for a10 WORD_WRAPPED
 col "Program Name" for a18 WORD_WRAPPED
 col "Parent Req" for a10 WORD_WRAPPED
 col "Phase" for a8 WORD_WRAPPED
 col "Status" for a8 WORD_WRAPPED
 col "Arguments"  for a10 WORD_WRAPPED
 col "OS Process"  for a10
 col "SPID" for a7
 col "OS_PID" for a7
 col "USERNAME" for a9 WORD_WRAPPED
 col inst_id for 99999
 col SID for 999999
 col "Serial#" for 99999
 select distinct qt.user_concurrent_queue_name "Queue Name"
       ,c2.user_concurrent_program_name "Program Name" 
      ,a.request_id "Request Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
      ,FLVP.meaning "Phase"
      ,FLVS.meaning "Status"
      ,a.argument_text "Arguments" 
      --,b.os_process_id "OS_PID"
      ,vs.inst_id
      ,vs.sid "SID"
      ,vs.serial# "Serial#"
      ,vp.spid "SPID"
      ,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 "USERNAME"
   from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,applsys.fnd_concurrent_queues_tl qt
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.FND_LOOKUP_VALUES FLVP
    ,APPLSYS.FND_LOOKUP_VALUES FLVS
    ,APPLSYS.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 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')
     and u.user_id=a.requested_by
     and a.phase_code=FLVP.Lookup_Code
     and FLVP.Lookup_Type='CP_PHASE_CODE'
     and FLVP.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 vs.process (+) = b.os_process_id
     and a.oracle_process_id = vp.spid
     and vs.paddr = vp.addr (+)
     and a.status_code='R'
     --and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
     and vs.inst_id=vp.inst_id
     and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > &THRESHOLD --To get list long running requests which are running for more than given threshold time
   order by 11 desc;

undef THRESHOLD