10 Replies Latest reply on Sep 22, 2017 10:37 AM by John_K

    Concurrent managers; query for the managers ran out of target

    2835760

      Can you provide the sql to track how long the concurrentrequest been waiting for the shortage of availability of managers target
      process in EBS R12.

      Ex: We set the standard managers target count 50... but on a month end because of the heavy usage programs been waiting for the shortage of
      the managers.. so how can we track the time duration of availability for the managers to process.. in other words how to track what time(Total time duration) the managers are ran out of managers  and the pending jobs been waiting for the availability of the managers process.

       

      Thanks.

        • 1. Re: Concurrent managers; query for the managers ran out of target
          Vybhav Reddy-Oracle

          Hi,

           

          You need to check this document Concurrent Processing - ANALYZEREQ.SQL Detailed Analysis of One Concurrent Request (Release 11 and up) (Doc ID 134035.1) , information.

           

          It will help you understand what is the background action occurring for a particular request. Once you gather enough information about how your concurrent managers are processing the requests, you can then setup the number of managers or processes value, reduce the SLEEP seconds and change the CACHE value according to your needs.

           

          Further you can refer to these documents as well =>

          ========================================================

          Concurrent Processing (CP) / APPS Reporting Scripts (Doc ID 213021.1)

          REQCHECK.sql - Diagnostic Script for Concurrent Requests (Doc ID 164978.1)

           

          Regards,

          Vybhav

          • 2. Re: Concurrent managers; query for the managers ran out of target
            John_K

            As a rough estimate you could look at the requested start date vs the actual start date in fnd_concurrent_requests. There will be a bit of a difference caused by the time taken for the manager to attempt picking up the job, but that shouldn't be huge. The below qquery should give you the starting point. See the wait_seconds column.

             

              select fcr.request_id req_id,
                     fcr.parent_request_id parent_id,
                     fu.user_name requestor,
                     nvl (fcr.description, fcpv.user_concurrent_program_name) prog,
                     decode (fcr.hold_flag, 'Y', 'Hold', fl_pha.meaning) phase,
                     fl_stat.meaning status,
                     fcr.request_date submitted,
                     fcr.requested_start_date req_start,
                     fcr.actual_start_date act_start,
                     extract (second from numtodsinterval (fcr.actual_start_date - fcr.requested_start_date, 'Day')) wait_seconds,
                     fcr.actual_completion_date completed,
                     fcqv.user_concurrent_queue_name mgr
                from fnd_concurrent_requests fcr,
                     fnd_concurrent_programs_vl fcpv,
                     fnd_lookups fl_pha,
                     fnd_lookups fl_stat,
                     fnd_lookups fl_exe,
                     fnd_concurrent_processes fcp,
                     fnd_concurrent_queues_vl fcqv,
                     fnd_executables_vl fev,
                     fnd_user fu
               where fcpv.concurrent_program_id = fcr.concurrent_program_id
                 and fcpv.application_id = fcr.program_application_id
                 and fl_pha.lookup_code = fcr.phase_code
                 and fl_stat.lookup_code = fcr.status_code
                 and fu.user_id = fcr.requested_by
                 and fcp.concurrent_process_id(+) = fcr.controlling_manager
                 and fcqv.concurrent_queue_id(+) = fcp.concurrent_queue_id
                 and fev.executable_id(+) = fcpv.executable_id
                 and fev.application_id(+) = fcpv.application_id
                 and fl_exe.lookup_code(+) = fev.execution_method_code
                 and fl_pha.lookup_type = 'CP_PHASE_CODE'
                 and fl_stat.lookup_type = 'CP_STATUS_CODE'
                 and fl_exe.lookup_type(+) = 'CP_EXECUTION_METHOD_CODE'
                 and fcr.phase_code = 'C'
                 and fcr.requested_start_date > sysdate - 10 -- Alter this to your date range
            order by fcr.requested_start_date desc, fcr.actual_start_date desc, fcr.request_date desc;
            
            • 3. Re: Concurrent managers; query for the managers ran out of target
              2835760

              Thank you very much this really helps. But my question is different sorry for that.

              I am looking for the sql that shows the Concurrent managers that ran out of process and the time when the managers ran out of the process.

              Ex: We set standard managers as  10 but the  submitted program are like 15.. so in this case the standard managers has been completely occupied by running jobs 10 and the remaining 5 jobs been
              waiting for the managers to get free .

              • 4. Re: Concurrent managers; query for the managers ran out of target
                John_K

                Yes, I was just giving a quick and easy way to derive that information to see whether it was sufficient. I.e. if you restrict the above query to where wait_seconds > 15, then you will have all concurrent programs where the program was waiting to start - i.e. it was waiting on a slot on the manager. There is always a short delay between the requested start and actual start, but when there are no manager slots available, that differential will increase. Does that make sense? Or are you looking at reporting on say time intervals and the number of processes waiting at that time? I.e.

                 

                Time    Running   Waiting 

                08:00   10             5

                08:10   10             8

                08:15    9              0

                 

                 

                etc

                • 5. Re: Concurrent managers; query for the managers ran out of target
                  Vybhav Reddy-Oracle

                  Hi,

                   

                  Run and upload the output of script provided in this document => Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)

                   

                  Note :- Its a data gathering script and would not change any settings of your environment.

                   

                  Regards,

                  Vybhav

                  Global Customer Services

                  • 6. Re: Concurrent managers; query for the managers ran out of target
                    2835760

                    I am looking for the managers when the waiting process are more than 0 and the actual managers count has been ran out of process with the time.

                    that is:

                     

                    Mgr name :               Actual :  Target   Running  waiting/pending    Time

                    Standard manager        10         10         10                  5                  10:25

                     

                    Thanks!

                    • 7. Re: Concurrent managers; query for the managers ran out of target
                      Vybhav Reddy-Oracle

                      You need to check the SLEEP SECONDS and CACHE value for the STANDARD MANAGER.

                      • 8. Re: Concurrent managers; query for the managers ran out of target
                        2835760

                        I am not looking to tuning the managers.. I am looking for the managers when the waiting process are more than 0 and the actual managers count has been ran out of process with the time.

                        that is:

                         

                        Mgr name :               Actual :  Target   Running  waiting/pending    Time

                        Standard manager        10         10         10                  5                  10:25

                        • 9. Re: Concurrent managers; query for the managers ran out of target
                          Vybhav Reddy-Oracle

                          Hi,

                           

                          There is no such query readily available.

                           

                          You will have to design it by yourself as part of a customization.

                           

                          Regards,

                          Vybhav

                          • 10. Re: Concurrent managers; query for the managers ran out of target
                            John_K

                            To do what you want would be possible but more hard work - you'd have to detect distinct windows of when the concurrent manager became overloaded, and then report on the start time of that window. A perfect job for the match_recognize syntax in 12c actually. However a better option might be to just sample at say 2 minute intervals and report on it that way?