Script to compare processing time of specific concurrent programs

In some cases you need to check out performance of specific concurrent programs. You can use the following query to check for a specific program that runs daily.


SELECT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id
            ,a.parent_request_id
            ,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS') request_date
            ,To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS') actual_start_date
            ,  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS') actual_completion_date
            , round ((a.actual_completion_date-a.actual_start_date)*24*60*60) AS secs_to_run
            , round ((a.actual_start_date-a.request_date)*24*60*60) AS wait_in_secs
            ,d.user_name
            , a.phase_code
            ,a.status_code
            ,a.argument_text
            ,a.priority
FROM   fnd_concurrent_requests a,
            fnd_concurrent_programs b ,
            FND_CONCURRENT_PROGRAMS_TL c,
            fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id
            AND c.USER_CONCURRENT_PROGRAM_NAME='Process transaction interface'
            and c.language = 'US'
            ;


Or use the following to find programs that run for a long time (10 minutes) last week.


SELECT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id
            ,a.parent_request_id
            ,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS') request_date
            ,To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS') actual_start_date
            ,  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS') actual_completion_date
            , round ((a.actual_completion_date-a.actual_start_date)*24*60*60) AS secs_to_run
            , round ((a.actual_start_date-a.request_date)*24*60*60) AS wait_in_secs
            ,d.user_name
            , a.phase_code
            ,a.status_code
            ,a.argument_text
            ,a.priority
FROM   fnd_concurrent_requests a,
            fnd_concurrent_programs b ,
            FND_CONCURRENT_PROGRAMS_TL c,
            fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id 
            and c.language = 'US'
            and a.actual_start_date >= sysdate-7           
            and round ((a.actual_completion_date-a.actual_start_date)*24*60*60) > 60*10 -- more than 10 minutes
            ;