Concurrent Requests – Diagnostics

These are some hacky-bodgy and general all round heath-robinson type SQLs I messed about with to try and find the SQL linked to concurrent requests.

-- ##############################################################################
--     SQL ID & CURRENT SQL FOR RUNNING REQUESTS
-- ##############################################################################

    SELECT   fcr.request_id id
           , fcr.ORACLE_PROCESS_ID
           , fcp.concurrent_program_name job
           , NVL(fcr.description, fcpt.user_concurrent_program_name) job
           , 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
           , (select sql_id || '____' || sql_text from v$sqlarea where sql_id = (select b.sql_id FROM v$process a, v$session b WHERE a.addr = b.paddr and a.spid = (select ORACLE_PROCESS_ID from applsys.fnd_concurrent_requests where REQUEST_ID = fcr.request_id))) sql_info
           , (select b.sid FROM v$process a, v$session b WHERE a.addr = b.paddr and a.spid = (select ORACLE_PROCESS_ID from applsys.fnd_concurrent_requests where REQUEST_ID = fcr.request_id)) sid          
        FROM applsys.fnd_concurrent_requests fcr 
        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
       WHERE 1 = 1
         AND fcr.phase_code = 'R'  -- running
         AND fcr.request_id = 21454094
    ORDER BY fcr.actual_start_date DESC;

-- ##############################################################################
--     RECENT V$SQLAREA ACTIVITY
-- ##############################################################################

    SELECT * 
      FROM v$sqlarea 
     WHERE last_active_time BETWEEN TO_DATE('09-MAY-2016 08:21:50', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('09-MAY-2016 08:22:54', 'DD-MON-YYYY HH24:MI:SS')
       AND sql_text like '%GL_BALANC%'
  ORDER BY last_active_time DESC;

-- ##############################################################################
--     GET SQL DETAILS FOR A CONCURRENT REQUEST ID
-- ##############################################################################

select *
from v$sqlarea
where sql_id = (SELECT Ses.sql_id 
                  FROM v$session ses
                     , v$process pro 
                 WHERE ses.paddr = pro.addr 
                   AND pro.spid IN (SELECT oracle_process_id 
                                      FROM applsys.fnd_concurrent_requests 
                                     WHERE request_id IN (20527487)));

Concurrent Requests – Scheduled

Background

While working in one job where we were doing an R12 upgrade, we needed to cancel all of the scheduled jobs before the upgrade started, and then start them up again after the upgrade.

I wanted to report on our scheduled concurrent requests. I had SQL to extract the names of requests, who requested them, and via which responsibility – all the usual stuff.

What I didn’t have was a way to extract when the jobs ran. The only option I had was to click into the details of every request, clicking on the “Schedule” button, and copy down the details for hundreds of jobs. It didn’t look hopeful!

On doing some digging, I found some really useful SQL on community.oracle.com. It turned out the info was held in the FND_CONC_RELEASE_CLASSES table – with the nitty gritty schedule data mainly held in the RELEASE_CLASS_ID field.

Examples

The value of release_class_id varies depending on your schedule:

  • Mon, Tue, Wed, Thu and Fri: 000000000000000000000000000000000111110
  • Tue, Wed, Thu, Fri, Sat: 000000000000000000000000000000000011111
  • Saturday: 000000000000000000000000000000000000001
  • Sunday: 000000000000000000000000000000001000000
  • 6th, 13th, 20th, and 27th of every month: 000001000000100000010000001000000000000

To make sense of that required some decode statements. I didn’t write them myself – I found those on the web too.

SQL for Scheduled Concurrent Requests

-- ##############################################################################
--        SCHEDULED CONCURRENT REQUESTS
-- ##############################################################################
        SELECT fcr.request_id
             , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
             , fcr.description
             , fcr.request_date
             , fcr.requested_start_date requested_start
             , TRUNC(fcr.requested_start_date) requested_start_trim
             , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
             , 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
             , fat.application_short_name application
             , fu.user_name requester
             , fu.description requested_by
             , frt.responsibility_name requested_by_resp
             , fcr.argument_text "PARAMETERS"
             , CASE WHEN TO_CHAR(fcr.requested_start_date, 'D') = 1 THEN 'Monday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 2 THEN 'Tuesday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 3 THEN 'Wednesday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 4 THEN 'Thursday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 5 THEN 'Friday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 6 THEN 'Saturday' WHEN TO_CHAR(fcr.requested_start_date, 'D') = 7 THEN 'Sunday' END requested_start_day
             , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
             , '------>' holds
             , fcr.hold_flag
             , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
             , CASE
                  WHEN fcr.hold_flag = 'Y'
                     THEN SUBSTR(
                            u2.description
                          , 0
                          , 40
                         )
               END last_update_by
             , CASE
                  WHEN fcr.hold_flag = 'Y'
                     THEN fcr.last_update_date
               END last_update_date
             , '------>' prints
             , fcr.number_of_copies print_count
             , fcr.printer
             , fcr.print_style
             , '------>' schedule
             , DECODE (fcrc.class_type,
                   'P', 'Periodic',
                   'S', 'On Specific Days',
                   'X', 'Advanced',
                   fcrc.class_type
               ) schedule_type
             , DECODE(fcr.increment_dates, 'N', '', 'Y', 'Yes') increment_dates
             , CASE WHEN fcrc.class_info IS NULL THEN
                TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
               END run_once
             , CASE WHEN fcrc.class_type = 'P' THEN
                SUBSTR(fcrc.class_info, 1, INSTR(fcrc.class_info, ':') - 1)
               END repeat_interval
             , CASE WHEN fcrc.class_type = 'P' THEN
                DECODE(SUBSTR(fcrc.class_info, INSTR(fcrc.class_info, ':', 1, 1) + 1, 1),
                       'N', 'minutes',
                       'M', 'months',
                       'H', 'hours',
                       'D', 'days')
               END repeat_interval_unit       
             , CASE WHEN fcrc.class_type = 'P' THEN
                DECODE(SUBSTR(fcrc.class_info, INSTR(fcrc.class_info, ':', 1, 2) + 1, 1),
                       'S', ' from the start of the prior run',
                       'C', ' from the completion of the prior run')
               END from_the     
               , CASE WHEN fcrc.class_type = 'S' AND INSTR(SUBSTR(fcrc.class_info, 33),'1',1) > 0 THEN
                       DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun ')
                 end  days_of_week 
               , CASE WHEN fcrc.class_type = 'S' AND INSTR(SUBSTR(fcrc.class_info, 1, 31),'1',1) > 0 THEN
                       DECODE(SUBSTR(fcrc.class_info, 1,  1), '1', '1st, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 2,  1), '1', '2nd, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 3,  1), '1', '3rd, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 4,  1), '1', '4th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 5,  1), '1', '5th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 6,  1), '1', '6th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 7,  1), '1', '7th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 8,  1), '1', '8th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 9,  1), '1', '9th, ')  ||
                       DECODE(SUBSTR(fcrc.class_info, 10, 1), '1', '10th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 11, 1), '1', '11th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 12, 1), '1', '12th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 13, 1), '1', '13th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 14, 1), '1', '14th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 15, 1), '1', '15th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 16, 1), '1', '16th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 17, 1), '1', '17th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 18, 1), '1', '18th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 19, 1), '1', '19th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 20, 1), '1', '20th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 21, 1), '1', '21st, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 22, 1), '1', '22nd, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 23, 1), '1', '23rd,' ) ||
                       DECODE(SUBSTR(fcrc.class_info, 24, 1), '1', '24th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 25, 1), '1', '25th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 26, 1), '1', '26th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 27, 1), '1', '27th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 28, 1), '1', '28th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 29, 1), '1', '29th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 30, 1), '1', '30th, ') ||
                       DECODE(SUBSTR(fcrc.class_info, 31, 1), '1', '31st. ')
                 END days_of_month
               , CASE WHEN fcrc.class_type = 'S' AND SUBSTR(fcrc.class_info, 32, 1) = '1' THEN
                          'Yes'
                 END last_day_of_month_ticked
               , fcrc.class_info
          FROM applsys.fnd_concurrent_requests fcr
          JOIN applsys.fnd_user fu                     ON fcr.requested_by =           fu.user_id
          JOIN applsys.fnd_user u2                     ON fcr.last_updated_by =        u2.user_id
          JOIN applsys.fnd_concurrent_programs fcp     ON fcr.concurrent_program_id =  fcp.concurrent_program_id  AND fcr.program_application_id = fcp.application_id
          JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcp.concurrent_program_id =  fcpt.concurrent_program_id AND fcp.application_id =         fcpt.application_id
          JOIN applsys.fnd_printer_styles_tl fpst      ON fcr.print_style =            fpst.printer_style_name 
     LEFT JOIN applsys.fnd_conc_release_classes fcrc   ON fcr.release_class_id =       fcrc.release_class_id
          JOIN applsys.fnd_responsibility_tl frt       ON fcr.responsibility_id =      frt.responsibility_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 
         WHERE fcr.phase_code = 'P' -- scheduled (pending)
           AND fcr.hold_flag = 'N' -- not on hold
           AND 1 = 1;