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