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)));