7 Replies Latest reply: Aug 16, 2013 2:50 AM by BluShadow RSS

    Merge is taking time.

    Aparna16

      Hello,

      Am running one procedure, in that there are 8 Merges and one merge is taking 40 mins   rest are taking few minutes.

      All the 8 Merges are using DB Links to access different views (All 8 mereges are using different views) and one JOIN table.

      FROM  V_PR_RPRT_SCHED@L2_BPMS52REP s

          JOIN  TBL_BUS_DATE dt

            ON   .......

       

      The merge which is taking time, and the view that it is accessing is having 2.5 lacs records.

       

      How about going for Bulk Collect concept here.. but not sure how to do insert as well as update using bulk collect. Or is there any way to do. Tried Parallel, append hint ..nothing is working out.

      Can we create synonyms and without a db link can we use those ?

       

      Please help me here to come out of it

        • 1. Re: Merge is taking time.
          Paul  Horth

          I can see the problem in the code and explain plans you didn't post, it's just here -->

           

           

           

          Seriously though, you have to give much more information: please readHOW TO: Post a SQL statement tuning request - template posting

           

          As for bulk collect and for loops? Only if you want things to go much slower.

           

          Finally the official language for this forum is International English. Please use the correct terms (thousands, millions etc.)

          instead of lac which may not be understood.

          • 2. Re: Merge is taking time.
            Hoek

            I don't think that switching to Bulk Collect will improve performance significantly.

            If you can do it in a single SQL statement, then do it in a single  SQL statement.

            Have you compared the execution plans of the MERGE's?

            Have you tried the driving_site hint?

            • 3. Re: Merge is taking time.
              BluShadow

              Please use international units when using the forum as people here are from all around the world.  "lacs" is not easily recognised by anyone outside of your locality.  Typical units to use would be "thousands" or "millions" etc.

               

              BULK COLLECT is NOT the answer to performance issues that you experience with pure SQL statements.  It will simply slow down the whole process.  PL/SQL cannot process data any faster than SQL can.

               

              Before tring to apply fixes to your merge statement you need to identify why it's going slow.  That could be because of the views being queried and how optimal they are, or the way in which you are joining those views together.  You could be missing indexes somewhere or you could have too many indexes so the wrong one is being selected.  The statistics on your data could be out of date and the opimizer doesn't pick the best plan to execute the query.  There are many reasons.

               

              Please read the two threads linked to in the FAQ: Re: 3. How to  improve the performance of my query? / My query is running slow.

               

              and post all the relevant details as described in those threads.

              • 4. Re: Merge is taking time.
                Aparna16

                Sorry about the language.

                 

                View is not very complex ( dont have the code for now ), but yes the cost of the View is near to 50 K.. but the logic as such is not complex, straight select with 6 table left joins.

                I could not find much scope for tuning the view. So, was finding some alternative of merge or not using links. I have used APPEND hint.. but the same execution time.

                • 5. Re: Merge is taking time.
                  Paul  Horth

                  Until you have read the link we have given and provided the information required, there's not much we can do for you.

                  • 6. Re: Merge is taking time.
                    jgarry

                    Hoek mentioned the driving site hint.  The purpose of that hint is to avoid sending the large amount of data over the link to your local database, then to be manipulated, but rather to do it on the remote db first.  You need to post the explain plans with predicates and code, as explained in the how to post faq, so we can help you figure out if the hint is appropriate or you have to perhaps write a remote procedure.  You might try googling and looking at the docs for that hint.

                    • 7. Re: Merge is taking time.
                      BluShadow

                      Aparna16 wrote:

                       

                      Sorry about the language.

                       

                      View is not very complex ( dont have the code for now ), but yes the cost of the View is near to 50 K.. but the logic as such is not complex, straight select with 6 table left joins.

                      I could not find much scope for tuning the view. So, was finding some alternative of merge or not using links. I have used APPEND hint.. but the same execution time.

                       

                      6 left joins could certainly cause a performance issue.  Without seeing the query and the plans though it's hard to exactly know.