10 Replies Latest reply: Sep 18, 2013 2:21 AM by Billy~Verreynne RSS

    Procedure run time is different.!

    SudhakarR

      Hi All,

       

      I have procedure which contains insert,delete,update statements for different tables. During the night batch, we are running this same procedure for different countries one by one. Except last country/run(total 48mins), all other countries/runs are taking very less time. Obviously, the last country is having more volumn than other country. but if I rerun the same procedure for last country, its taking very less time(13mins). So summary of issue as normal run is 48mins, but rerun is 13mins only. I took below steps for this issue, but no solve so far.

       

      * monitor through vsql during the normal run, transaction is keep happening and not stuck anywhere.

      * collected stats on table level and schema level.

      * tried to rebuild the indexes.

      * though I did flush shared pool, it took only 13-14mins in the 2nd run.

       

      not sure where its stucking and what I need to do next?

       

      Can anyone help me for this issue?

       

      Thanks and Regards,

      Sudhakar.M

       

       

      BANNER

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE 10.2.0.4.0 Production
      TNS for Linux: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

        • 1. Re: Procedure run time is different.!
          SudhakarR

          Can anyone suggest something for this issue? it will be really helpful for me.

           

          Thanks.

          • 2. Re: Procedure run time is different.!
            Ishan

            Check this out:

             

            DBMS_PROFILER

            DBMS_TRACE

             

            These will help you identify the bottlenecks in the Procedure.

             

            Thanks,

            Ishan

            • 3. Re: Procedure run time is different.!
              EdStevens

              KMSudhakar wrote:

               

              Hi All,

               

              I have procedure which contains insert,delete,update statements for different tables. During the night batch, we are running this same procedure for different countries one by one. Except last country/run(total 48mins), all other countries/runs are taking very less time. Obviously, the last country is having more volumn than other country. but if I rerun the same procedure for last country, its taking very less time(13mins). So summary of issue as normal run is 48mins, but rerun is 13mins only. I took below steps for this issue, but no solve so far.

               

              * monitor through vsql during the normal run, transaction is keep happening and not stuck anywhere.

              * collected stats on table level and schema level.

              * tried to rebuild the indexes.

              * though I did flush shared pool, it took only 13-14mins in the 2nd run.

               

              not sure where its stucking and what I need to do next?

               

              Can anyone help me for this issue?

               

              Thanks and Regards,

              Sudhakar.M

               

               

              BANNER

              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
              PL/SQL Release 10.2.0.4.0 - Production
              CORE 10.2.0.4.0 Production
              TNS for Linux: Version 10.2.0.4.0 - Production
              NLSRTL Version 10.2.0.4.0 - Production

               

              Perhaps some (a lot) of the work done on the initial run simply doesn't occur on the re-run ... inserts and updates that don't have to happen on the re-run

              • 4. Re: Procedure run time is different.!
                sb92075

                post both EXPLAIN PLAN  when fast & slow

                • 5. Re: Procedure run time is different.!
                  davidp 2

                  "During the night batch" ... it takes 48 minutes, rerun only takes 13 minutes

                  A common reason is because it's running while your system or database backups are running, so the system disks or SAN are really busy. When you re-run it the backups aren't running and the procedure can run faster.

                  Ask your system administrator and DBA when the backups happen.

                  Last year I had similar problems, and it was other systems' backups on the same SAN, although different disks on the SAN, saturating the SAN processor.

                  • 6. Re: Procedure run time is different.!
                    Billy~Verreynne

                    To add to Dave's response - you need to

                    • look at the AMOUNT of work done (should be the same from your description)
                    • look at the WAIT STATES of each (this seems to vary greatly from your description)

                     

                    As Dave said, the batch run can have very high I/O wait times due to SAN contention, whereas the rerun does not.

                    • 7. Re: Procedure run time is different.!
                      SudhakarR

                      our night batch will start only after backup is completed. no backups are running at that time.

                      • 8. Re: Procedure run time is different.!
                        SudhakarR

                        am not sure about SAN contention. do I need to contact DBA for the same? or please share how to verify the same.

                         

                        Thanks.

                        • 9. Re: Procedure run time is different.!
                          SudhakarR

                          one more thing, I want to share, not sure this will cause for this issue.

                           

                          we are trying to insert/update records through triggers which are in another schema. the purpose of trigger is validation. once validation is completed, only the valid record will go to target tables which are in same schema where the procedure is running.

                           

                          so,

                           

                          run the procedure from schema A,

                          do the validation by triggers from schema B (like interface). These interface tables dont hold any records always.

                          if data look good, move to target table in schema A. if not, move to error table in A.

                           

                          I collected stats on both schema(A,B), but no help for this issue.

                           

                          is there anyother way to make the schema in good condition? Please suggest.

                           

                          Thanks.

                          • 10. Re: Procedure run time is different.!
                            Billy~Verreynne

                            KMSudhakar wrote:

                            am not sure about SAN contention. do I need to contact DBA for the same?

                            What would this achieve if you do not know whether SAN I/O is a bottleneck.

                             

                            As I mentioned already, you need to identify the workload. Is the workload the same for the batch run as it is for the rerun? Data volumes are seldom static. So in order to compare the batch and rerun processes, you need to confirm that they are each doing the same workload in order for a meaningful comparison.

                             

                            Secondly, you need to determine what the batch process is doing. How much I/O is done? How much CPU is used? How much time is spend on waiting for some resource or some lock or latch? Each process has events (stuff that happens) and wait state (stuff waited on). The virtual performance view details these. And this is needed to determine what the process is doing.

                             

                            If it is spending little time on I/O and not waiting on I/O, looking at SAN as the potential problem will be a pretty useless exercise.

                             

                             

                            we are trying to insert/update records through triggers which are in another schema. the purpose of trigger is validation. once validation is completed, only the valid record will go to target tables which are in same schema where the procedure is running.

                            This is likely a major overhead in processing. Triggers are not the best answer in many cases.

                             

                            is there anyother way to make the schema in good condition?

                            Why do you blame the schema for being in a "bad" condition? I'm pretty sure that what is bad is the design of the tables, the use of triggers, and PL/SQL and SQL code design.

                             

                            You have not provided any evidence to say that this is not the case. The fact that the rerun is faster than batch run, is meaningless without context. Can these processes be compared directly (does it have the same workload)? Are the same resources available (e.g. does a backup cause an I/O bottleneck that does not exist with the rerun).

                             

                            Performance Tuning Overview : Principles

                            Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck.

                             

                            You have not identified the problem. And without knowing what the problem is, tuning cannot be done. And adhoc gathering schema stats is not going to fix this unkown problem. Make it a known problem first.