Long Running Concurrent requests

1.) Get the list of  long running concurrent request from below queries .

Long Running concurrent request -1

SELECT   fcr.oracle_session_id

        ,fcr.request_id rqst_id

        ,fcr.requested_by rqst_by

        ,fu.user_name

        ,fr.responsibility_name

        ,fcr.concurrent_program_id cp_id

        ,fcp.user_concurrent_program_name cp_name

        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')act_start_datetime

        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status

        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min

        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_sec

        ,fcr.oracle_process_id "oracle_pid/SPID"

        ,fcr.os_process_id os_pid

        ,fcr.argument_text

        ,fcr.outfile_name

        ,fcr.logfile_name

        ,fcr.enable_trace

    FROM apps.fnd_concurrent_requests fcr

        ,apps.fnd_user fu

        ,apps.fnd_responsibility_tl fr

        ,apps.fnd_concurrent_programs_tl fcp

   WHERE fcr.status_code LIKE 'R'

     AND fu.user_id = fcr.requested_by

     AND fr.responsibility_id = fcr.responsibility_id

     AND fcr.concurrent_program_id = fcp.concurrent_program_id

     AND fcr.program_application_id = fcp.application_id

     AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 1

ORDER BY fcr.concurrent_program_id

        ,request_id DESC;

Long Running concurrent request -1 ( more than 30 minutes )

select r.request_id "Request ID",

       round((sysdate-r.actual_start_date)*1440,0) "Runtime(Mins)",

       p.concurrent_program_name "Conc Program Short Name",

       p.user_concurrent_program_name "Concurrent Program Name",

       to_char(r.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') "Requested Start Date",

       to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Actual Start Date",

       TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "System Date",

       u.user_name,

       u.description,

       argument_text,

       s.sid,

       r.os_process_id,

       r.oracle_process_id

  from applsys.fnd_concurrent_requests r,

       apps.fnd_concurrent_programs_vl p,

       v$session s,

       applsys.fnd_user u

 where r.program_application_id = p.application_id

   and r.concurrent_program_id = p.concurrent_program_id

   and r.requested_by = u.user_id

   and r.phase_code = 'R'

   and r.status_code = 'R'

   and to_char(r.os_process_id) = s.process(+)

   and s.action(+) = 'Concurrent Request'

   and p.concurrent_class_id is null

   and round((sysdate-r.actual_start_date)*1440,0) > 30

 order by "Runtime(Mins)" desc

/

---------------------------

2.) SQL will give you all request ID’s related to Request

  -- Get the run history of the request 

SELECT f.request_id,

         pt.user_concurrent_program_name user_conc_program_name,

         f.actual_start_date start_on,

         f.actual_completion_date end_on,

            FLOOR (

                 (  (f.actual_completion_date - f.actual_start_date)

                  * 24

                  * 60

                  * 60)

               / 3600)

         || ' HOURS '

         || FLOOR (

                 (  (  (f.actual_completion_date - f.actual_start_date)

                     * 24

                     * 60

                     * 60)

                  -   FLOOR (

                           (  (f.actual_completion_date - f.actual_start_date)

                            * 24

                            * 60

                            * 60)

                         / 3600)

                    * 3600)

               / 60)

         || ' MINUTES '

         || ROUND (

               (  (  (f.actual_completion_date - f.actual_start_date)

                   * 24

                   * 60

                   * 60)

                -   FLOOR (

                         (  (f.actual_completion_date - f.actual_start_date)

                          * 24

                          * 60

                          * 60)

                       / 3600)

                  * 3600

                - (  FLOOR (

                          (  (  (f.actual_completion_date - f.actual_start_date)

                              * 24

                              * 60

                              * 60)

                           -   FLOOR (

                                    (  (  f.actual_completion_date

                                        - f.actual_start_date)

                                     * 24

                                     * 60

                                     * 60)

                                  / 3600)

                             * 3600)

                        / 60)

                   * 60)))

         || ' SECS '

            time_difference,

         p.concurrent_program_name concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'Running',

                 'C', 'Complete',

                 f.phase_code)

            Phase,

         f.status_code,

         f.Argument_text

    FROM apps.fnd_concurrent_programs p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests f

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME like '%LOG%'

         AND pt.language = USERENV ('Lang')

         AND f.actual_start_date IS NOT NULL

ORDER BY f.actual_start_date DESC;

------------------------------

3.)-- Need request id to find out child requests

-- Find all child requests for a request set (OR if no children, just get details on an individual request)

-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/

       fcr.request_id    "Request ID",

       fcr.requested_by "User",

       substr(DECODE (FCR.DESCRIPTION,  NULL,

       FCPTL.USER_CONCURRENT_PROGRAM_NAME,

       FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",

       (fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time",

       oracle_process_id "Trace File ID" ,

       fcr.phase_code "Phase",

       fcr.status_code "Status",

       to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS')   "Submitted",

       (fcr.actual_start_date - fcr.request_date)*1440 "Delay",

       to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS')  "Start Time",

       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",

       fcr.argument_text "Parameters"

  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */

               fcr1.request_id

          from fnd_concurrent_requests fcr1

         where 1=1

         start with fcr1.request_id = &parent_request_id

       connect by prior fcr1.request_id = fcr1.parent_request_id) x,

       fnd_concurrent_requests fcr,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcptl

where fcr.request_id = x.request_id

   and fcr.concurrent_program_id = fcp.concurrent_program_id

   and fcr.program_application_id = fcp.application_id

   and fcp.application_id = fcptl.application_id

   and fcp.concurrent_program_id = fcptl.concurrent_program_id

   and fcptl.language = 'US'

order by 1;

--------------------------------

4.)  SQL Need request id to find out SQLID

SELECT a.request_id,

       d.sid,

       d.serial#,

       d.status,

       d.osuser,

       d.process

  FROM apps.fnd_concurrent_requests a,

       apps.fnd_concurrent_processes b,

       gv$process c,

       gv$session d

WHERE     a.controlling_manager = b.concurrent_process_id

       AND c.pid = b.oracle_process_id

       AND b.session_id = d.audsid

       AND a.request_id = &Request_ID

       AND a.phase_code = 'R';

    ************

 SELECT fcr.REQUEST_ID,

       fcr.PHASE_CODE,

       fcr.STATUS_CODE,

       fcr.OS_PROCESS_ID,

       vs.sid,

       fcr.USER_CONCURRENT_PROGRAM_NAME,

       vs.SQL_ID

  FROM apps.fnd_conc_requests_form_v fcr, gv$session vs

 WHERE  fcr.REQUEST_ID = '128335339'

 ---fcr.USER_CONCURRENT_PROGRAM_NAME = 'Workflow Background Process'

       AND fcr.OS_PROCESS_ID = VS.PROCESS;