Query To Get Oracle SID Details for a Given Concurrent Request ID

Download(sidfrmreqid.sql)

REM +======================================================================+
REM                    
REM File Name: sidfrmreqid.sql
REM 
REM Description:
REM   Query To Get Oracle SID Details for a Given Concurrent Request ID
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @sidfrmreqid.sql 
REM 
REM Input Required:
REM    REQID : Concurrent Request ID
REM   
REM +======================================================================+

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
    WHERE a.request_id in ('&REQID')
   AND s.paddr = c.addr
         AND a.oracle_process_id = c.spid
         AND a.phase_code = UPPER ('R');

Query To Get Concurrent Request ID for a Given Oracle SID

Download(reqidfrmsid.sql)

REM +======================================================================+
REM                    
REM File Name: reqidfrmsid.sql
REM 
REM Description:
REM   Query To Get Concurrent Request ID for a Given Oracle SID
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @reqidfrmsid.sql 
REM 
REM Input Required:
REM    SID : Oracle SID
REM   
REM +======================================================================+

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&SID')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Query To Analyze a Concurrent Request

REM +======================================================================+
REM                    
REM File Name: analyzereq.sql
REM 
REM Description:
REM   SQL Script To Analyze a Concurrent Request
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @analyzereq.sql request_id
REM
REM   This Query Requires Request ID as an Input to Analyze
REM
REM +======================================================================+

Click on below link to download and view SQL Script

Download(analyzereq.sql)

Query To Check Cocurrent Request Status

Download(reqstatus.sql)

REM +======================================================================+
REM                    
REM File Name: reqstatus.sql
REM 
REM Description:
REM   Query To Check Cocurrent Request Status
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @reqstatus.sql 
REM 
REM Input Required:
REM    Concurrent Request ID
REM   
REM +======================================================================+

ACCEPT REQ_ID PROMPT "Enter Concurrent Request ID: ";

clear columns
 set lines 180
 set pages 100
 col Parameters for a20 WORD_WRAPPED
 set pages 100
 col "Conc Program Name" for a30 WORD_WRAPPED
 col "Started at" for a20
 col "Completed at" for a20
 col "Username" for a10 WORD_WRAPPED
 SELECT distinct t.user_concurrent_program_name "Conc Program Name",
 r.REQUEST_ID "Request ID",
 to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
 to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
 decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
 decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
 'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",
 u.user_name "Username",
 --ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
 --              2
  --            ) "Runtime (in Minutes)" 
 round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
 FROM
 apps.fnd_concurrent_requests r ,
 apps.fnd_concurrent_programs p ,
 apps.fnd_concurrent_programs_tl t,
 apps.fnd_user u, apps.fnd_conc_req_summary_v v
 WHERE 
 r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
 --AND r.actual_start_date >= (sysdate - $NO_DAYS)
 --AND r.requested_by=22378
 AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
 AND t.concurrent_program_id=r.concurrent_program_id
 AND r.REQUESTED_BY=u.user_id
 AND v.request_id=r.request_id
 AND r.request_id ='&REQ_ID' 
 --and t.user_concurrent_program_name like '$CONC_PROG_NAME'
 order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

undef REQ_ID

Query To Check Concurrent Program Run History

Download(reqhistory.sql)

REM +======================================================================+
REM                    
REM File Name: reqhistory.sql
REM 
REM Description:
REM   Query To Check Concurrent Program Run History
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql 
REM 
REM Input Required :
REM    Number of days and User Concurrent Program Name  
REM   
REM +======================================================================+

ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";
ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";

        clear columns

 set lines 180
 set pages 100
 col Parameters for a20 WORD_WRAPPED
 set pages 100
 col "Conc Program Name" for a30 WORD_WRAPPED
 col "Started at" for a20
 col "Completed at" for a20
 col "Username" for a10 WORD_WRAPPED
 SELECT distinct t.user_concurrent_program_name "Conc Program Name",
 r.REQUEST_ID "Request ID",
 to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
 to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
 decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
 decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
 'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",
 u.user_name "Username",
 --ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
 --              2
  --            ) "Runtime (in Minutes)" 
 round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
 FROM
 apps.fnd_concurrent_requests r ,
 apps.fnd_concurrent_programs p ,
 apps.fnd_concurrent_programs_tl t,
 apps.fnd_user u, apps.fnd_conc_req_summary_v v
 WHERE 
 r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
 AND r.actual_start_date >= (sysdate - &NO_DAYS)
 --AND r.requested_by=22378
 AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
 AND t.concurrent_program_id=r.concurrent_program_id
 AND r.REQUESTED_BY=u.user_id
 AND v.request_id=r.request_id
 --AND r.request_id ='2260046' in ('13829387','13850423')
 and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'
 order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

undef NO_DAYS
undef USER_CONC_PROG_NAME

Query To Get Details of All Current Running Concurrent Requests

Download(cmrun.sql)

REM +======================================================================+
REM                    
REM File Name: cmrun.sql
REM 
REM Description:
REM   Query To Get Details of All Current Running Concurrent Requests
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @cmrun.sql 
REM   
REM +======================================================================+

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
   order by 11 desc;

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