I wrote a small select that helps me troubleshoot the concurrent processing. The goal is to quickly understand which concurrent requests are executing at the moment and which ones are still in the queue. It’s also very important to get some basic information about running requests to start troubleshooting quickly after a problem is spotted, so the important information is: which concurrent node and which DB node are the request processes running on, what are the process IDs and what is the DB session doing.

Features and of the select:

  • Shows all running and pending requests. (except those that are pending on Conflict resolution manager);
  • Can handle RAC and multiple Concurrent nodes, does not meter which node you run the select from;
  • Sorts the requests by putting the running requests in the front and pending requests are sorted the same order they will be picked up for execution by concurrent managers.

So here’s the output that’s provided by the select:

Monitoring the requests queue

Monitoring the requests queue

The meaning of columns is:

  • REQ_ID - request ID;
  • PREQ_ID - parent request ID, important if you need understand relation of request set stages to request sets;
  • PROG_NAME - Short name of the concurrent program
  • REQ_NAME- full name of concurrent program, request set or request set stage;
  • P, S- Phase and Status codes, you can decode these using FND_LOOKUPS;
  • USER_NAME- does not need explanation ;)
  • PRIO - request priorit;
  • CP_PROCESS- Concurrent node hostname and process ID for the OS process handling the execution of the request (format “HOST:PID”);
  • DB_SID- Instance name and SID of oracle session executing the requests (format “INSTANCE_NAME:SID”);
  • DB_PROCESS- DB host and process id for the corresponding Oracle DB session (format “HOST:PID”);
  • STAT- DB session status A-Active, I-Inactive;
  • EVENT, SW - Event end seconds in wait fields from v$session_wait view;
  • ACTUAL_START_DATE- time when request started execution;
  • ARGUMENT_TEXT - Parameters of the request.

And here goes the select, you are welcome to ask for improvements and suggestions. I’ll also be happy to hear some feedback if you found it useful or too buggy to use (you know you have to test in your test environment before running in production, don’t you?):

select r.request_id req_id,
       r.parent_request_id preq_id,
       ps.concurrent_program_name prog_name,
       p.user_concurrent_program_name || case
         when concurrent_program_name = 'FNDRSSUB' then
          (select ': ' || rs.user_request_set_name
             from fnd_request_sets_tl rs
            where rs.application_id = to_number(argument1)
              and rs.request_set_id = to_number(argument2)
              and rs.language = 'US')
       end || case
         when concurrent_program_name = 'FNDRSSTG' then
          (select ': ' || rss.user_stage_name
             from fnd_request_set_stages_tl rss
            where rss.set_application_id = to_number(argument1)
              and rss.request_set_id = to_number(argument2)
              and rss.request_set_stage_id = to_number(argument3)
              and rss.language = 'US')
       end req_name,
       r.phase_code p,
       r.status_code s,
       u.user_name,
       r.priority PRIO,
       (select node_name || ':'
          from fnd_concurrent_processes cp
         where concurrent_process_id = r.controlling_manager) ||
       r.os_process_id cp_process,
       gi.INSTANCE_NAME || ':' || ss.sid db_sid,
       gi.HOST_NAME || ':' || pp.spid db_process,
       decode(ss.status, 'ACTIVE', 'A', 'INACTIVE', 'I', ss.status) STAT,
       w.event,
       w.seconds_in_wait sw,
       r.actual_start_date,
       argument_text
  from fnd_user                   u,
       fnd_concurrent_requests    r,
       fnd_concurrent_programs_tl p,
       fnd_concurrent_programs    ps,
       gv$session                 ss,
       gv$process                 pp,
       gv$session_wait            w,
       gv$instance                gi
 where 1 = 1
   and r.requested_by = u.user_id
   and (r.phase_code = 'R' or r.status_code = 'I')
   And r.Requested_Start_Date <= Sysdate
   and p.concurrent_program_id = r.concurrent_program_id
   and ps.concurrent_program_id = r.concurrent_program_id
   and p.language = 'US'
   and ss.audsid(+) = r.oracle_session_id
   and r.hold_flag = 'N'
   and pp.inst_id(+) = ss.inst_id
   and pp.addr(+) = ss.paddr
   and w.INST_ID(+) = ss.inst_id
   and w.sid(+) = ss.sid
   and gi.inst_id(+) = ss.inst_id
 order by decode(r.phase_code, 'R', 0, 1),
          NVL(R.priority, 999999999),
          R.Priority_Request_ID,
          R.Request_ID;