11 February 2011

How to check the Long running Concurrent Process

SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC

Output:-
REQUEST_ID,SPID,RESPONSIBILITY_NAME,C.CONCURRENT_PROGRAM_NAME||':'||CTL.USER_CONCURRENT_PROGRAM_NAME,DESCRIPTION,ARGUMENT_TEXT,NODE_NAME,DB_INSTANCE,LOGFILE_NAME,LOGFILE_NODE_NAME,OUTFILE_NAME,CONCURRENT_QUEUE_NAME,PHASE_CODE,STATUS_CODE,COMPLETION_TEXT,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,USER_NAME,MINS,AVG_MINS,DURATION_IN_HOURS
1655903,17481,Inventory,INCOIN: Import Items,,, 1, 1, 1, 1, 4100, 1,OUR12-APPS1,OURPROD,/applprod/conc_log/log/l1655903.req,OUR12-APPS1,/applprod/conc_log/out/o1655903.out,STANDARD,R,R,,11-Feb-11 12:08:44 PM,,SETUPUSER,9.23333333333333,162.816666666667,

No comments: