13 Replies Latest reply on May 14, 2015 5:26 AM by aJohny

    Need a script which will provide the details of concurrent request which ran beyond the avg run time.

    2671975

      Hi Guru's--- It will be really helpful if any one can provide a script which will get the details of concurrent request which ran beyond the avg run time.

       

      Thanks,

        • 1. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
          ;) ApPsMasTi ;)

          Change 3600 seconds as per your need

          --  FILE:   fnd_long.sql

          --

          --  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com

          --          Copyright (C) 1999 AppsDBA Consulting

          --

          --  DATE:   unknown

          --

          --  DESCRIPTION:

          --          Query to display long running concurrent requests.

          --         

          --  MODIFICATIONS:

          --          A. Rivenes, 08/09/1999, Changed elapsed time to minutes.

          --

          --

          -- This program is free software: you can redistribute it and/or modify

          -- it under the terms of the GNU General Public License as published by

          -- the Free Software Foundation, either version 3 of the License, or

          -- (at your option) any later version.

          --

          -- This program is distributed in the hope that it will be useful,

          -- but WITHOUT ANY WARRANTY; without even the implied warranty of

          -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

          -- GNU General Public License for more details.

          --

          -- You should have received a copy of the GNU General Public License

          -- along with this program.  If not, see <http://www.gnu.org/licenses/>.

          --          

          --

          SET LINESIZE 132;

          --

          COLUMN request_id   HEADING 'Request ID'      FORMAT 99999999;

          COLUMN strttime     HEADING 'Start|Time'      FORMAT A17;

          COLUMN endtime      HEADING 'End|Time'        FORMAT A17;

          COLUMN rtime        HEADING 'Elapsed|(Min)'   FORMAT 9990.99;

          SELECT REQUEST_ID,

                 TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,

                 TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,

          --       ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE time,

          --       TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) seconds,

                 ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,

                 OUTCOME_CODE,

                 SUBSTR(completion_text,1,20) compl_txt

            FROM fnd_concurrent_requests

          -- WHERE TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) > 3600

          -- WHERE ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE > 0.01

          WHERE ACTUAL_START_DATE > sysdate-10

          ORDER BY 2

          /

          1 person found this helpful
          • 2. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
            aJohny

            Replace the parameter with the seconds ':p_value_in_seconds' value.

             

            select FCP.user_concurrent_program_name
                  ,FCR.argument_text
                  ,FCR.completion_text
                  ,to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') start_time
                  ,to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') end_time
                  ,ROUND((FCR.actual_completion_date  - FCR.actual_start_date)*86400) time_in_sec
            from fnd_concurrent_requests FCR
                ,fnd_concurrent_programs_vl FCP
            where FCR.concurrent_program_id = FCP.concurrent_program_id
            and ((FCR.actual_completion_date  - FCR.actual_start_date)*86400) > :p_value_in_seconds;
            

             

            Hope this helps.

             

            Cheers

            AJ

            • 3. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
              2671975

              Thank you for the update. I just want. If as per job history if the avg run time is 2 hr.so i will get those details also.

               

              Thanks,

              • 4. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                Bashar.

                Hi,

                 

                Your requirement means that the jobs out of the normal elapsed range will affect this average.

                Anyway, you can add a subquery like the following:

                 

                and ((FCR.actual_completion_date  - FCR.actual_start_date)*86400) > (select avg((FCR.actual_completion_date  - FCR.actual_start_date)*86400) from fnd_concurrent_requests

                           where concurrent_program_id = fcr. concurrent_program_id)


                Of course you'll have to remove the last line of the query provided by AJ.


                Regards,

                Bashar

                • 5. Re: Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                  aJohny

                  Are you after something like this ? Last column displays the average time also.

                   

                  select FCP.user_concurrent_program_name
                        ,FCR.argument_text
                        ,FCR.completion_text
                        ,to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') start_time
                        ,to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') end_time
                        ,ROUND((FCR.actual_completion_date  - FCR.actual_start_date)*86400) time_in_sec
                        ,ROUND(AVG_VALUES.avg_time) AVG_TIME_IN_SEC
                  from fnd_concurrent_requests FCR
                      ,fnd_concurrent_programs_vl FCP
                      ,(select  FCR1.concurrent_program_id
                               ,avg((FCR1.actual_completion_date  - FCR1.actual_start_date)*86400) avg_time
                        from fnd_concurrent_requests FCR1
                        group by FCR1.concurrent_program_id) AVG_VALUES
                  where FCR.concurrent_program_id = FCP.concurrent_program_id
                  and   (AVG_VALUES.concurrent_program_id = FCR.concurrent_program_id
                         and   
                         ((FCR.actual_completion_date  - FCR.actual_start_date)*86400) > AVG_VALUES.avg_time)
                  ORDER BY FCR.concurrent_program_id,FCR.actual_start_date desc;
                  

                   

                   

                  Cheers

                  AJ

                  • 7. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                    2671975

                    Hi,

                     

                    Just a small question on this again. Will the avg time which we are getting here for the request. Will the avg run time for the request taken from last 2 days as a average.

                     

                    If I want to see the Running normal request which are crossed the avg run time. So shall we need to add only Phase_code='R' and Status_Code='R' on the above query.

                     

                    Please clear me .Thank you again for for your help.

                    • 8. Re: Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                      aJohny

                      No,

                      The average is taken from all the runs. If you want to get the average only from last 2 days, please use the below query.

                       

                      I think  the completion date (don't have access to a running instance at the moment) will be null for the running request, so I have modified the query to use the sysdate if the completion date is null. So you don't need to use the extra condition.

                      I have added the phase code and status_code in the select clause so that you can get the details and filter if you need.

                       

                      select FCP.user_concurrent_program_name  
                            ,FCR.argument_text  
                            ,FCR.completion_text
                            ,FCR.phase_code
                            ,FCR.status_code
                            ,to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') start_time  
                            ,to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') end_time  
                            ,ROUND((NVL(FCR.actual_completion_date,sysdate)  - FCR.actual_start_date)*86400) time_in_sec  
                            ,ROUND(AVG_VALUES.avg_time) AVG_TIME_IN_SEC  
                      from fnd_concurrent_requests FCR  
                          ,fnd_concurrent_programs_vl FCP  
                          ,(select  FCR1.concurrent_program_id  
                                   ,avg((FCR1.actual_completion_date  - FCR1.actual_start_date)*86400) avg_time  
                            from fnd_concurrent_requests FCR1  
                            where NVL(FCR1.actual_completion_date,sysdate-3) > TRUNC(sysdate -2) -- This will ignore the running programs as sysdate-3 is greater than sysdate-2
                            group by FCR1.concurrent_program_id) AVG_VALUES  
                      where FCR.concurrent_program_id = FCP.concurrent_program_id  
                      and   (AVG_VALUES.concurrent_program_id = FCR.concurrent_program_id  
                             and     
                             ((NVL(FCR.actual_completion_date,sysdate)  - FCR.actual_start_date)*86400) > AVG_VALUES.avg_time)  
                      ORDER BY FCR.concurrent_program_id,FCR.actual_start_date desc;
                      

                       

                      Please test the query with an example scenario as I didn't get a chance to test it at the moment. Let me know if you have any issues.

                       

                      Cheers

                      AJ

                      P.S: Please mark the helpful and correct answers if it is .

                      1 person found this helpful
                      • 10. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                        2671975

                        Hi,

                         

                        If I want to check the request(Running -Normal) which exceeds the avg run time. So do i need to just add phase_code='R' and status_code='R' or need to do anything else.

                         

                        Please clear me.

                        • 11. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                          ;) ApPsMasTi ;)

                          please see

                           

                           

                          TABLE: APPLSYS.FND_CONCURRENT_REQUESTS

                          http://etrm.oracle.com/pls/et1211d9/etrm_pnav.show_object?c_name=FND_CONCURRENT_REQUESTS&c_owner=APPLSYS&c_type=TABLE

                           

                          http://heliosguneserol.com/2010/04/26/concurrent-manager-phase-and-status-codes-meaning-in-oracle-e-business-suite/

                          PHASE CODE:

                          I = Inactive

                          P = Pending

                          R = Running

                          C = Completed

                           

                          STATUS CODE:

                          U = Disabled

                          W = Paused

                          X = Terminated

                          Z = Waiting

                          M = No Manager

                          Q = Standby

                          R = Normal

                          S = Suspended

                          T = Terminating

                          D = Cancelled

                          E = Error

                          F = Scheduled

                          G = Warning

                          H = On Hold

                          I =  Normal

                          A = Waiting

                          B = Resuming

                          C = Normal

                           

                          ApPsMaStI

                          sharing is Caring

                          • 12. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                            2671975

                            Yes Thank you. I have idea about the phase code and status code. I just modified the script here. This will show the "Avg Run time for the program exceed the actual run time"

                             

                            select FCP.user_concurrent_program_name   

                                  ,FCR.argument_text   

                                  ,FCR.completion_text 

                                  ,FCR.phase_code 

                                  ,FCR.status_code 

                                  ,to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') start_time   

                                  ,to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') end_time   

                                  ,ROUND((NVL(FCR.actual_completion_date,sysdate)  - FCR.actual_start_date)*86400) time_in_sec   

                                  ,ROUND(AVG_VALUES.avg_time) AVG_TIME_IN_SEC   

                            from fnd_concurrent_requests FCR   

                                ,fnd_concurrent_programs_vl FCP   

                                ,(select  FCR1.concurrent_program_id   

                                         ,avg((FCR1.actual_completion_date  - FCR1.actual_start_date)*86400) avg_time   

                                  from fnd_concurrent_requests FCR1   

                                  where NVL(FCR1.actual_completion_date,sysdate-3) > TRUNC(sysdate -2) -- This will ignore the running programs as sysdate-3 is greater than sysdate-2 

                                  group by FCR1.concurrent_program_id) AVG_VALUES   

                            where FCR.concurrent_program_id = FCP.concurrent_program_id   

                            and  phase_code='R' and status_code='R' and (AVG_VALUES.concurrent_program_id = FCR.concurrent_program_id   

                                   and      

                                   ((NVL(FCR.actual_completion_date,sysdate)  - FCR.actual_start_date)*86400) > AVG_VALUES.avg_time)   

                            ORDER BY FCR.concurrent_program_id,FCR.actual_start_date desc;

                            • 13. Re: Need a script which will provide the details of concurrent request which ran beyond the avg run time.
                              aJohny

                              I think you got everything working now

                               

                              You could also use the below condition :

                              AND FCR.actual_completion_date IS NULL

                               

                              Cheers

                              AJ

                              1 person found this helpful