Oracle - get concurrent program runtime / eval performance

Revision: 55014

at January 26, 2013 03:15 by theonlyalterego

Updated Code

SELECT * FROM (
    SELECT
    f.request_id ,
    pt.user_concurrent_program_name user_conc_program_name,
    f.actual_start_date start_on,
    f.actual_completion_date end_on,
floor(((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60)/3600)
    || ' HOURS ' ||
    floor((((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60) -
    floor(((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)
    || ' MINUTES ' ||
    round((((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60) -
    floor(((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60)/3600)*3600 -
    (floor((((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60) -
    floor(((nvl(f.actual_completion_date,sysdate)-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)*60) ))
    || ' SECS ' time_difference,
    p.concurrent_program_name concurrent_program_name,
    decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
    f.status_code,
    fuv.description requester,
    f.number_of_copies,
    f.printer,
    f.print_style,
    f.lfile_size,
    f.logfile_name,
    f.ofile_size,
    f.outfile_name
    /* additional values*/
    ,
    round((f.ofile_size/1024/1024),3) ofile_mb_size,
    f.argument_text params
    /**/
    FROM apps_fnd.fnd_concurrent_programs p,
    apps_fnd.fnd_concurrent_programs_tl pt,
    apps_fnd.fnd_concurrent_requests f,
    apps_fnd.fnd_user_v fuv
    WHERE f.concurrent_program_id = p.concurrent_program_id
    AND f.program_application_id = p.application_id
    AND f.concurrent_program_id = pt.concurrent_program_id
    AND f.program_application_id = pt.application_id
    AND pt.LANGUAGE = USERENV('Lang')
    AND f.actual_start_date IS NOT NULL
    AND f.requested_by = fuv.user_id
    ORDER BY
    f.actual_start_date DESC,
    f.actual_completion_date-f.actual_start_date
    ) WHERE user_conc_program_name LIKE :1

Revision: 55013

at May 30, 2012 03:09 by theonlyalterego

Updated Code

SELECT * FROM (
    SELECT
    f.request_id ,
    pt.user_concurrent_program_name user_conc_program_name,
    f.actual_start_date start_on,
    f.actual_completion_date end_on,
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)
    || ' HOURS ' ||
    floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)
    || ' MINUTES ' ||
    round((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600 -
    (floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)*60) ))
    || ' SECS ' time_difference,
    p.concurrent_program_name concurrent_program_name,
    decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
    f.status_code,
    fuv.description requester,
    f.number_of_copies,
    f.printer,
    f.print_style,
    f.lfile_size,
    f.logfile_name,
    f.ofile_size,
    f.outfile_name
    /* additional values*/
    ,
    round((f.ofile_size/1024/1024),3) ofile_mb_size,
    f.argument_text params
    /**/
    FROM apps_fnd.fnd_concurrent_programs p,
    apps_fnd.fnd_concurrent_programs_tl pt,
    apps_fnd.fnd_concurrent_requests f,
    apps_fnd.fnd_user_v fuv
    WHERE f.concurrent_program_id = p.concurrent_program_id
    AND f.program_application_id = p.application_id
    AND f.concurrent_program_id = pt.concurrent_program_id
    AND f.program_application_id = pt.application_id
    AND pt.LANGUAGE = USERENV('Lang')
    AND f.actual_start_date IS NOT NULL
    AND f.requested_by = fuv.user_id
    ORDER BY
    f.actual_start_date DESC,
    f.actual_completion_date-f.actual_start_date
    ) WHERE user_conc_program_name LIKE :1

Revision: 55012

at February 22, 2012 01:43 by theonlyalterego

Updated Code

SELECT * FROM (
    SELECT
    f.request_id ,
    pt.user_concurrent_program_name user_conc_program_name,
    f.actual_start_date start_on,
    f.actual_completion_date end_on,
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)
    || ' HOURS ' ||
    floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)
    || ' MINUTES ' ||
    round((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600 -
    (floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)*60) ))
    || ' SECS ' time_difference,
    p.concurrent_program_name concurrent_program_name,
    decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
    f.status_code,
    fuv.description requester,
    f.number_of_copies,
    f.printer,
    f.print_style,
    f.lfile_size,
    f.logfile_name,
    f.ofile_size,
    f.outfile_name
    FROM apps_fnd.fnd_concurrent_programs p,
    apps_fnd.fnd_concurrent_programs_tl pt,
    apps_fnd.fnd_concurrent_requests f,
    apps_fnd.fnd_user_v fuv
    WHERE f.concurrent_program_id = p.concurrent_program_id
    AND f.program_application_id = p.application_id
    AND f.concurrent_program_id = pt.concurrent_program_id
    AND f.program_application_id = pt.application_id
    AND pt.LANGUAGE = USERENV('Lang')
    AND f.actual_start_date IS NOT NULL
    AND f.requested_by = fuv.user_id
    ORDER BY
    f.actual_start_date DESC,
    f.actual_completion_date-f.actual_start_date
    ) WHERE user_conc_program_name LIKE :1

Revision: 55011

at February 22, 2012 01:33 by theonlyalterego

Updated Code

SELECT * FROM (
    SELECT
    f.request_id ,
    pt.user_concurrent_program_name user_conc_program_name,
    f.actual_start_date start_on,
    f.actual_completion_date end_on,
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)
    || ' HOURS ' ||
    floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)
    || ' MINUTES ' ||
    round((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600 -
    (floor((((f.actual_completion_date-f.actual_start_date)
    *24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)
    *24*60*60)/3600)*3600)/60)*60) ))
    || ' SECS ' time_difference,
    p.concurrent_program_name concurrent_program_name,
    decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
    f.status_code,
    fuv.description requester
    FROM apps_fnd.fnd_concurrent_programs p,
    apps_fnd.fnd_concurrent_programs_tl pt,
    apps_fnd.fnd_concurrent_requests f,
    apps_fnd.fnd_user_v fuv
    WHERE f.concurrent_program_id = p.concurrent_program_id
    AND f.program_application_id = p.application_id
    AND f.concurrent_program_id = pt.concurrent_program_id
    AND f.program_application_id = pt.application_id
    AND pt.LANGUAGE = USERENV('Lang')
    AND f.actual_start_date IS NOT NULL
    AND f.requested_by = fuv.user_id
    ORDER BY
    f.actual_start_date DESC,
    f.actual_completion_date-f.actual_start_date
    ) WHERE user_conc_program_name LIKE :1

Revision: 55010

at January 21, 2012 08:33 by theonlyalterego

Initial Code

select * from (
select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps_fnd.fnd_concurrent_programs p,
      apps_fnd.fnd_concurrent_programs_tl pt,
      apps_fnd.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date 
) where user_conc_program_name like :1

Initial URL

http://imdjkoch.wordpress.com/2011/05/12/know-your-concurrent-programs-performance/

Initial Description

can be used to track Oracle Apps concurrent program performance

Initial Title

Oracle - get concurrent program runtime / eval performance

Initial Tags

Oracle

Initial Language

SQL

Choose a language for easy browsing: