Tuesday 23 June 2009

Concurrent program and time

I use the following sql statement to check concurrent program and time that it took to run. It will give you an overview of how long a program takes and results can be exported to excel for further analysis.

select
u.user_name username,
user_concurrent_queue_name QName,
cp2.user_concurrent_program_name programName,
--cp.concurrent_program_name short,
to_char(cr.actual_start_date,'DD-Mon-YYYY HH24:MI:SS') started,
to_char(cr.actual_completion_date,'DD-Mon-YYYY HH24:MI:SS') Completed,
round((nvl(cr.actual_completion_date,sysdate)-cr.actual_start_date)*1440,1)
ttl_rtime,
request_id ,
decode(status_code,'E','Err','T','Term','G','Warn','C','Normal','R','Normal','W','Pending',status_code)
scode
from fnd_application a,
fnd_concurrent_programs_tl cp2,
fnd_concurrent_programs cp,
fnd_user u,
fnd_concurrent_requests cr,
fnd_concurrent_queues_tl cq,
fnd_concurrent_processes cpr
where a.application_id = cp.application_id
and cp.concurrent_program_id = cr.concurrent_program_id
and cp2.concurrent_program_id = cr.concurrent_program_id
and u.user_id = cr.requested_by
and cq.concurrent_queue_id = cpr.concurrent_queue_id
and cr.controlling_manager = cpr.concurrent_process_id
--and status_code in ('E','G')
--and cp.concurrent_program_name <> 'FNDRSSUB'
--and cr.actual_completion_date>sysdate - 1
--AND TO_NUMBER(TO_CHAR(actual_start_date, 'HH24')) between 4 and 6
--AND to_char(actual_start_date, 'DD-MON-YY') = '23-JUN-09'
AND round((nvl(cr.actual_completion_date,sysdate)-cr.actual_start_date)*1440,1) > 10
order by cr.actual_start_date DESC ;

No comments:

Post a Comment