Forum Stats

  • 3,782,321 Users
  • 2,254,637 Discussions
  • 7,880,045 Comments

Discussions

Why Does This Merge Statement Take Forever?

Ray Winkelman
Ray Winkelman Member Posts: 75 Green Ribbon
edited Jul 27, 2016 7:49PM in SQL & PL/SQL

The title says it all. I've crafted a merge statement and it literally takes forever. I've waited up to 5 minutes and it's ridiculous considering the table, and query being merged only return/contain one row each. 

merge into pmm$pmmmanagedaccount p

using (select ma.accountname,

              ma.managedaccountid

       from   pmm$pmmmanagedaccount ma

              inner join dbo$managedentity me

                      on ma.managedsystemid = me.managedentityid

              left join dbo$asset alocal

                     on me.assetid = alocal.assetid

              left join pmm$pmmmanagedsystem_do_285jzc lda

                     on ma.managedaccountid = lda.domainaccountid

              left join dbo$managedentity medomain

                     on lda.managedsystemid = medomain.managedentityid

              left join dbo$asset adomain

                     on medomain.assetid = adomain.assetid

       where  ma.accountname like '%Managed%'

          and ( ma.managedaccountid = 1 )

          and 1 = ( case

                      when me.type = 3

                           and lda.domainaccountid is null then 0

                      else 1

                    end )) w

on (p.managedaccountid = w.managedaccountid)

when matched then

  update set maxreleaseduration = 2,

             defaultreleaseduration = 2;

Frank KulashCarlosDLGAndrewSayerRay WinkelmanPaulzipJohn Stegeman

Best Answer

  • Ray Winkelman
    Ray Winkelman Member Posts: 75 Green Ribbon
    edited Jul 27, 2016 3:40PM Accepted Answer

    I've figured it out guys.

    I was connected as the same user with both SQL Plus (Session 1) and SQL Developer (Session 2). The changes made to the data in the table by calling the procedure were not cross-session (Session 1). However, changes made to the stored procedure were (Session 2).

    The merge statement must have taken forever (Session 2) because the affected rows had become locked by calling the stored procedure in Session 1. However, I was not able to query locked rows with the session experiencing a long running merge (Session 2) - probably because of some cross-session limitation.

    Thanks for all of your help, it turns out the source code was correct all along.

«134

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 27, 2016 1:08PM
  • Paulzip
    Paulzip Member Posts: 8,541 Blue Diamond
    edited Jul 27, 2016 1:17PM

    Why are you joining onto tables that you aren't referring to or don't need?!  That will simply make the CBO make a more resource intensive explain plan.  Seems to me your query could be re-written as....

    merge into pmm$pmmmanagedaccount p

    using (select ma.accountname,

                  ma.managedaccountid

           from   pmm$pmmmanagedaccount ma

                  inner join dbo$managedentity me

                          on ma.managedsystemid = me.managedentityid

                  left join pmm$pmmmanagedsystem_do_285jzc lda

                         on ma.managedaccountid = lda.domainaccountid

           where  ma.accountname like '%Managed%'

              and ( ma.managedaccountid = 1 )

              and 1 = ( case

                          when me.type = 3

                               and lda.domainaccountid is null then 0

                          else 1

                        end )) w

    on (p.managedaccountid = w.managedaccountid)

    when matched then

      update set maxreleaseduration = 2,

                 defaultreleaseduration = 2;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 Red Diamond
    edited Jul 27, 2016 1:19PM

    Hi,

    Ray Winkelman wrote:
    
    The title says it all. I've crafted a merge statement and it literally takes forever. I've waited up to 5 minutes and it's ridiculous considering the table, and query being merged only return/contain one row each.  
    
    merge into pmm$pmmmanagedaccount p 
    using (select ma.accountname, 
                  ma.managedaccountid 
           from   pmm$pmmmanagedaccount ma 
                  inner join dbo$managedentity me 
                          on ma.managedsystemid = me.managedentityid 
                  left join dbo$asset alocal 
                         on me.assetid = alocal.assetid 
                  left join pmm$pmmmanagedsystem_do_285jzc lda 
                         on ma.managedaccountid = lda.domainaccountid 
                  left join dbo$managedentity medomain 
                         on lda.managedsystemid = medomain.managedentityid 
                  left join dbo$asset adomain 
                         on medomain.assetid = adomain.assetid 
           where  ma.accountname like '%Managed%' 
              and ( ma.managedaccountid = 1 ) 
              and 1 = ( case 
                          when me.type = 3 
                               and lda.domainaccountid is null then 0 
                          else 1 
                        end )) w 
    on (p.managedaccountid = w.managedaccountid) 
    when matched then 
      update set maxreleaseduration = 2, 
                 defaultreleaseduration = 2; 
    

    Why are you joining dbo$asset, dbo$managedentity and dbo$asset again?

  • Ray Winkelman
    Ray Winkelman Member Posts: 75 Green Ribbon
    edited Jul 27, 2016 1:23PM

    Why?

    Because this merge is being called in a stored procedure. I removed the conditions that assessed parameter values - replacing the parameters with literals inside the conditions.  Ex: '%Managed%'

    However, the posted version of the merge statement is also taking forever and those columns ARE required.

    Ex:

               and ( p_systemname is null

                      or nvl(adomain.assetname, alocal.assetname) = p_systemname )

    Another example of a condition I removed for the sake of a minimal example that still produces the problem for all you good folks.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,014 Red Diamond
    edited Jul 27, 2016 1:26PM

    What's the purpose of all the left joins (except LDA) if you take data from left table only? Your MERGE, unless I missed something could be simplified:

    merge

      into pmm$pmmmanagedaccount p

      using (

             select  ma.accountname,

                     ma.managedaccountid

               from      pmm$pmmmanagedaccount ma

                     inner join

                         dbo$managedentity me

                       on ma.managedsystemid = me.managedentityid

                     left join

                         pmm$pmmmanagedsystem_do_285jzc lda

                       on ma.managedaccountid = lda.domainaccountid

               where ma.accountname like '%Managed%'

                 and ma.managedaccountid = 1

                 and 1 = case

                           when me.type = 3 and lda.domainaccountid is null then 0

                           else 1

                         end

            ) w

      on (

          p.managedaccountid = w.managedaccountid

         )

      when matched

        then

          update set maxreleaseduration = 2,

                 defaultreleaseduration = 2

    /

    SY.

  • Ray Winkelman
    Ray Winkelman Member Posts: 75 Green Ribbon
    edited Jul 27, 2016 1:35PM

    This STILL takes forever?

    I have no clue what's going on. All the source being posted here looks great. Maybe it's my environment.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 27, 2016 1:38PM
  • CarlosDLG
    CarlosDLG Member Posts: 1,361 Silver Trophy
    edited Jul 27, 2016 1:40PM

    You need to look at the execution plan.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 27, 2016 1:41PM
    it literally takes forever

    Unless I missed something, and we're at the end of time, it doesn't LITERALLY take forever

    Looks like a migrated SQL Server database.

    Have you checked to see if there's any blocking locks? maybe someone has a record in pmm$pmmmanagedaccount locked, and you're trying to update that record and waiting (not literally forever, but literally until the lock is released)

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 27, 2016 2:00PM

    We can make guesses that will lead us all over the place, or you could provide the execution plan and the what the session executing the statement is really waiting on.

    If you don't know how to provide that then you could see the faq post (which has already been linked to) or google around.

    Once you provide this information, we actually have a decent chance of KNOWING what is going wrong and perhaps even why and how to fix it.

    CarlosDLG
This discussion has been closed.