3 Replies Latest reply on May 5, 2016 7:43 AM by Jagadekara

    Which concurrent programs is causing performance issue?

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      11g

      OL5

       

      Our instance usually hangs during month-end peak processing period.

      The users can not connect anymore hence, our last resort is to bounce the instance.

       

      We have list of 8,323+ concurrent programs being run during peak day (month-end).

      Can you help identify which of the following is candidate for performance issue?

       

            

      CONC_PROGREQUESTED_DATEACTUAL_START_DATEACTUAL_COMPLETION_DATEElapsed Time (sec)Completion Remarks
      @Tax Preference Report4/26/2016 8:374/26/2016 8:374/26/2016 8:3888Normal completion
      @Tax Preference Report4/26/2016 13:064/26/2016 13:064/26/2016 13:0758Normal completion
      @Tax Preference Report4/26/2016 15:254/26/2016 15:254/26/2016 15:2510Normal completion
      3
      Account Analysis - (180 Char)4/26/2016 9:304/26/2016 9:304/26/2016 9:303Normal completion
      Account Analysis - (180 Char)4/26/2016 10:114/26/2016 10:114/26/2016 10:1112Normal completion
      Account Analysis - (180 Char)4/26/2016 16:544/26/2016 16:544/26/2016 16:543Normal completion
      Account Analysis - (180 Char)4/26/2016 17:014/26/2016 17:014/26/2016 17:0110Normal completion
      Account Analysis - (180 Char)4/26/2016 17:064/26/2016 17:064/26/2016 17:064Normal completion
      5

      ...More

       

       

      Kind regards,

      jc

        • 1. Re: Which concurrent programs is causing performance issue?
          Beauty_and_dBest

          How do I attach a text file for the complete list of concurrent programs?

           

           

          Kind regards,

          • 2. Re: Which concurrent programs is causing performance issue?
            Bashar.

            Use advanced editor to attach files.

            It will not be practical to look at such a very long list to determine which ones are responsible for the performance issues.

             

            Regards,

            Bashar

            1 person found this helpful
            • 3. Re: Which concurrent programs is causing performance issue?
              Jagadekara

              Hi,

               

              Check the following. It may help you...

               

              The following query will display the time taken to execute the concurrent Programs--for a particular user with the latest concurrent programs sorted in least time taken -- to complete the request.


              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_codefrom  apps.fnd_concurrent_programs p,      apps.fnd_concurrent_programs_tl pt,      apps.fnd_concurrent_requests fwhere 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 nullorder by      f.actual_start_date desc;


              ******* Rafi ORACLE DBA & APPS DBA Blog*******: Useful Queries for Checking Concurrent Requests and Programs

              1 person found this helpful