Logo Background

Kill Long Running Jobs In Oracle Applications

  • By on January 27, 2009 | No Comments

    When you have complain about the system slow or hang in Oracle Applications, it is wise to issue command as below to check the running concurrent jobs in Oracle Applications.

    The output from the query below gave you the SPID of the long running jobs in Oracle Applications including what is the report name, running period and etc.

    Based on the SPID, you can login to UNIX box to issue “top” command to check on the resource consumption of this particular job.

    SELECT
    a.oracle_process_id "SPID",
    a.request_id
    ,frt.responsibility_name
    ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
    ,a.description
    ,a.ARGUMENT_TEXT
    ,b.node_name
    ,b.db_instance
    ,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 IN ('I','P','R','T')
    --and a.phase_code = 'C'
    --and a.status_code = 'E'
    --and actual_start_date > sysdate - 30
    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 ctl.language = 'US'
    AND a.requested_by = fu.user_id
    AND a.responsibility_id = frt.responsibility_id
    ORDER BY a.actual_start_date DESC
    Previous
    Next
    » Oracle Database ORA-00600 Error
Leave a Comment