Concurrent Requests – Usage

If you spend much time doing Oracle EBS support, you probably spend quite a while looking at concurrent requests. While users with access to the System Administrator responsibility have the option to review which concurrent requests have run, I find the interface is pretty cumbersome, since you can’t easily access all of the bits of info you need in one place, such as the username of the person who submitted the request, the responsibility they used, the date the request was submitted, when it started, how long it ran for etc.

The following are some queries I’ve come to rely on when I want to query the database to check on concurrent requests, and their usage. I have other queries to investigate request definitions, schedule information, parameters used when the request was submitted and so on. The queries in this post focus on their actual usage.

Additional Notes

ofile_size
This is the size of the request’s output file in bytes – I find it useful to see this value because if you’re comparing a request which runs a number of times, and the request is one which creates a lot of output, e.g Create Accounting, then you can check if the request did something meaningful – if the value is the same value most of the time then is a lot bigger on a certain day or for certain parameters, you can see that the request created a larger output file.
lfile_size
This is the size of the request’s log file in bytes.
parent_request_id
Concurrent Requests are often spawned / launched via another request – e.g. Create Accounting will trigger a series of jobs. If a request’s parent_request_id is populated, having that info to hand can be useful for following the sequence of jobs. There’s a peice of SQL you can run to look at concurrent request hierarchy.
argument_text
When you submit a concurrent request, the request normally accepts a number of parameters, or arguments. The first 25 arguments are listed in the fnd_concurrent_requests table as argument1 through to argument25. All of the arguments used in the request are concatenated into the argument_text field – even if the request has more than 25 parameters. Therefore reviewing the data held in argument_text can be useful. For years I only looked at argument_text, but when I realised the first 25 parameter values are held in their own fields, I started using those too, because it allows your queries to be narrowed down a lot more easily – e.g. return all records where argument2 = ‘200’ – rather than where argument_text is like ‘%200%’.
resub_count
If a request is submitted via a schedule, and runs every n days / hours / minutes, then each time it runs, the resub_count value is incremented by 1. It can be useful to review this info for some jobs, as you can see at a glance if the request has been run many times, or, if this value is NULL, if it’s been run as a one-off.
root_request_id
If a request is submitted via a schedule, then the root_request_id ID is the request_id of the first job that ever ran as part of the schedule. If your Purge Concurrent Request and/or Manager Data jobs haven’t deleted the data for that root job, then sometimes it can be useful to go back to that root_request_id to review it.

The SQL

-- ##############################################################################
--         CONCURRENT REQUESTS
-- ############################################################################*/

    SELECT  fcr.request_id id
           , fcr.ofile_size
           , fcr.parent_request_id
--           , fcp.concurrent_program_name job_name
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , fcr.request_date
--           , fcr.status_code
           , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
           , DECODE (fcr.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
           , TO_CHAR(fcr.actual_start_date, 'Dy') day
           , TRUNC(fcr.actual_start_date) run_date
           , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
           , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
           , CASE WHEN fcr.phase_code = 'R' AND fcr.actual_completion_date IS NULL THEN TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) ELSE TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) END duration
           , CASE WHEN TRUNC(fcr.actual_start_date) = TRUNC(SYSDATE) THEN '*' END t
           , frt.responsibility_name resp
           , fu.user_name || ' (' || substr(fu.description, 0, 30) || ')' submitted_by
           , (REPLACE(REPLACE(fcr.completion_text,CHR(10),''),CHR(13),' ')) completion_text
--           , fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4, fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8, fcr.argument9, fcr.argument10, fcr.argument11, fcr.argument12, fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16, fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20, fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24, fcr.argument25
           , fcr.argument_text
           , fcr.resub_count
           , fcr.root_request_id
        FROM applsys.fnd_concurrent_requests fcr
        JOIN applsys.fnd_user fu                     ON fcr.requested_by =           fu.user_id
        JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id =  fcpt.concurrent_program_id
        JOIN applsys.fnd_concurrent_programs fcp     ON fcp.concurrent_program_id =  fcpt.concurrent_program_id
        JOIN applsys.fnd_application fat             ON fcr.program_application_id = fat.application_id AND fat.application_id = fcpt.application_id AND fcp.application_id = fcpt.application_id
        JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_id
   LEFT JOIN applsys.fnd_conc_pp_actions fcpa        ON fcr.request_id =             fcpa.concurrent_request_id AND fcpa.program_application_id = fcp.application_id
       WHERE 1 = 1
         AND fcr.request_date > '19-APR-2017'
--         AND fcr.request_date < '01-SEP-2016'
--         AND fcr.request_id IN (2096378)
         AND fcr.actual_start_date IS NOT NULL
--         AND fu.user_name IN ('SYSADMIN')
--         AND NVL(fcr.description, fcpt.user_concurrent_program_name) = 'Diagnostic Apps Check'
--         AND NVL(fcr.description, fcpt.user_concurrent_program_name) != 'OAM Applications Dashboard Collection'
--         AND fcr.actual_completion_date BETWEEN TO_DATE('17-JUN-2016 22:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('17-JUN-2016 23:59:00', 'DD-MON-YYYY HH24:MI:SS')
--         AND fcr.argument1 = '200'
--         AND fcr.argument_text like '%761574%'
--         AND fcr.actual_completion_date IS NOT NULL
--         AND fcr.phase_code = 'P'
--         AND fcr.phase_code = 'R'
--         AND fcr.status_code = 'E'
    ORDER BY fcr.actual_start_date DESC;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--         CONCURRENT REQUESTS
-- ############################################################################*/
SELECT   fcr.request_id id
--       , REPLACE(fcr.parent_request_id, -1, '') parent
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
--       , fcpt.description
       , fat.application_short_name app
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.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
       , to_char (fcr.actual_start_date, 'Dy') day
       , TRUNC(fcr.actual_start_date) run_date
       , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
       , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
       , CASE WHEN fcr.phase_code = 'R' AND fcr.actual_completion_date IS NULL THEN TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) ELSE TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) END dur
       , CASE WHEN TRUNC(fcr.actual_start_date) = TRUNC(SYSDATE) THEN '*' END t
       , frt.responsibility_name resp
       , fu.user_name || ' (' || fu.description || ')' submitted_by
       , fcr.argument_text params
       , fcr.completion_text
       , fcr.printer
       , fcr.number_of_copies copies
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_user fu
       , applsys.fnd_concurrent_programs_tl fcpt
       , applsys.fnd_application fat
       , applsys.fnd_responsibility_tl frt
   WHERE fcr.requested_by = fu.user_id
     AND fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fat.application_id 
     AND fcpt.application_id = fat.application_id
     AND fcr.responsibility_id = frt.responsibility_id
--     AND fcr.program_application_id = fcpt.application_id
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) NOT IN ('Actual Cost Worker', 'OAM Applications Dashboard Collection', 'PO Output for Communication', 'Receiving Transaction Processor', 'Requisition Import', 'Workflow Background Process')
--     AND fcpt.description != 'Request Set Stage Master Program'
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) IN ('PRC: Update Project Summary Amounts')
--     AND NVL(fcr.description, fcpt.user_concurrent_program_name) LIKE 'IEX%'
--     AND fcr.request_date > '01-JUN-2015'
--     AND fcr.actual_completion_date IS NOT NULL
--     AND fcr.status_code = 'E'
--     AND fu.user_name IN ('SYSADMIN')
--     AND fcr.request_id IN (18971266)
     AND fcr.phase_code = 'R'  -- running
--     AND fcr.status_code = 'E' -- error
--     AND fcr.phase_code != 'P' -- not pending
--     AND fcr.status_code NOT IN ('D','X')                 -- not cancelled (D) or teminated (X)
--     AND fcr.status_code = 'W'
ORDER BY fcr.actual_start_date DESC;

Additional Queries

-- ##############################################################################
--       CONCURRENT REQUEST - PENDING COUNT
-- ############################################################################*/

    SELECT   fcp.concurrent_program_name job
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , COUNT(*) ct
        FROM applsys.fnd_concurrent_requests fcr 
        JOIN applsys.fnd_user fu                     ON fcr.requested_by =           fu.user_id
        JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id =  fcpt.concurrent_program_id
        JOIN applsys.fnd_concurrent_programs fcp     ON fcp.concurrent_program_id =  fcpt.concurrent_program_id
        JOIN applsys.fnd_application fat             ON fcr.program_application_id = fat.application_id AND fat.application_id = fcpt.application_id AND fcp.application_id = fcpt.application_id
        JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_id
   LEFT JOIN applsys.fnd_conc_pp_actions fcpa        ON fcr.request_id =             fcpa.concurrent_request_id AND fcpa.program_application_id = fcp.application_id
       WHERE 1 = 1
         AND fcr.phase_code = 'P'  -- pending
   GROUP BY fcp.concurrent_program_name
          , NVL(fcr.description, fcpt.user_concurrent_program_name)
   ORDER BY 3 DESC
          , 2;

-- ##############################################################################
--         CONCURRENT REQUESTS - Volumes
-- ############################################################################*/

-- Volumes per hour

SELECT   TRUNC(fcr.actual_start_date) run_date
       , TO_CHAR((fcr.actual_start_date), 'HH24') start_
       , COUNT(*) ct
    FROM applsys.fnd_concurrent_requests fcr 
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id =  fcpt.concurrent_program_id
--   WHERE NVL(fcr.description, fcpt.user_concurrent_program_name) = 'Compile value set hierarchies'
GROUP BY TRUNC(fcr.actual_start_date)
       , TO_CHAR((fcr.actual_start_date), 'HH24')
ORDER BY 1 DESC, 2;

select user_id from applsys.fnd_user where user_name = 'MEWTSSP2';

-- by day
SELECT   trunc(fcr.request_date) date_
       , to_char (trunc(fcr.request_date), 'Dy') ddd
       , MIN(fcr.actual_start_date) min_start
       , MAX(fcr.actual_completion_date) max_end
       , count(*) job_ct
    FROM applsys.fnd_concurrent_requests fcr
   WHERE fcr.requested_by = 1157
GROUP BY trunc(fcr.request_date)
       , to_char (trunc(fcr.request_date), 'Dy')
ORDER BY trunc(fcr.request_date) DESC;

-- by resp
SELECT   frt.responsibility_name
       , count(*) ct
    FROM apps.fnd_concurrent_requests fcr
    JOIN apps.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id 
    JOIN apps.fnd_responsibility_tl frt       ON fcr.responsibility_id =     frt.responsibility_id 
   WHERE fcr.actual_completion_date IS NOT NULL
     AND fcr.actual_completion_date > SYSDATE - 2
GROUP BY frt.responsibility_name
ORDER BY 2 DESC;

-- https://community.oracle.com/thread/2622109
-- Volumes split into 30 minute blocks
WITH limitdata AS
(SELECT   fcr.request_id id
       , fcr.actual_start_date dt
    FROM applsys.fnd_concurrent_requests fcr
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id
   WHERE fcr.actual_start_date BETWEEN TO_DATE('11-MAY-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('11-MAY-2016 07:00:00', 'DD-MON-YYYY HH24:MI:SS')
     AND fcpt.user_concurrent_program_name NOT IN ('PO Output for Communication','Cost Manager','Workflow Background Process','OAM Applications Dashboard Collection','Actual Cost Worker'))
     ,chunks as (select rownum as chnk_no
                    ,0 + (86400/48) * (rownum -1) as secs_from
                    ,0 + (86400/48) * rownum -1 as secs_to
                from dual
               connect by rownum <= 48)
SELECT d
      ,tfrom
      ,tto
      ,COUNT(id) as cntr
  FROM (select  TO_CHAR(x.dt,'DD.MM.YYYY') as d
               ,TO_CHAR(x.dt + c.secs_from/86400,'HH24:MI') as tfrom
               ,TO_CHAR(x.dt + c.secs_to/86400,'HH24:MI') as tto
               ,c.chnk_no
               ,l.id
           from chunks c
                CROSS join (SELECT distinct TRUNC(dt) dt FROM limitdata) x
           left outer join limitdata l ON (    TRUNC(l.dt) = x.dt  
                                           AND to_number(to_char(l.dt,'SSSSS')) BETWEEN c.secs_from and c.secs_to)) x
GROUP by d, tfrom, tto, chnk_no
ORDER BY d, chnk_no;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*