Performance Issue: Merge Update Insert

Rama_G

Hi Team,

In my existing code we are using Merge, Update when match and Insert when not matched. However, it is making performance issue. Below is the query.

I am from SAS and hence requesting experts here to show some light on this issue. I am connecting to Oracle through SAS and the query works just as it is like SQL query(you may please ignore Proc sql and disconnect SQL statements).

proc sql;

   connect to ORACLE

   (

       DBEXT=XXXX BUFF=XXXXX PATH=XXXXX DOMAIN="XXXX"

   );

   execute

   (

      MERGE INTO

         Lib1.Target

      USING

         Lib1.Source

      ON

         (

            Source.A_KEY = Target.A_key AND

            Source.P_KEY = Target.P_KEY AND

            Source.R_KEY = Target.R_KEY    )

      WHEN MATCHED THEN UPDATE

         set

            Target.col1= Source.col1,

            Target.col2= Source.col2,

            Target.col3= Source.col3,

       WHEN NOT MATCHED THEN INSERT

         (

            Target.A_KEY,

            Target.P_KEY,

            Target.R_KEY,

         )

         values

         (

            Source.A_KEY,

            Source.P_KEY,

            Source.R_KEY,

         )

   ) by ORACLE;

disconnect from ORACLE;

quit;

PROCEDURE SQL used (Total process time):
real time 30:45.15

I Need to fix the performance.

Can anyone please suggest on this.

Regards

Rama Goteti.

Jonathan Lewis

Since you're using both the update and insert options Oracle has to  do an outer join between the two tables - if the optimizer has picked the wrong join method then it is possible to hint the statement to change the join method.

There is also a feature / defect of the merge command that it doesn't handle column projection at all well. To minimise the work load (especially if a HASH JOIN or MERGE JOIN is the best option) you should rewrite the statemen tto reference only the columns that are needed, so:

merge into (select minimum list of columns from lib1.target) target

using (select minimum list of columns from lib1,source) source

... etc. as you currently have it.

See: https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/

Making this change as the first step may be sufficient to get your the performance benefit you need.

If not, the next step is to check (and show us) the execution plan.

Regards

Jonathan Lewis

Stew Ashton

Your query cannot execute as written: there are extra commas in three places:

Target.col3= Source.col3, -- extra commaWHEN NOT MATCHED THEN INSERT...    Target.R_KEY, -- extra comma ) values...    Source.R_KEY, -- extra comma )

Also, you are updating every matching row, even if the data is already good. Consider:

create table source(a_key, p_key, r_key, col1, col2, col3) asselect 1,2,3,4,5,6 from dual;create table target as select * from source;

The two table have exactly the same data. Now run your MERGE:

MERGE INTO TargetUSING SourceON (  Source.A_KEY = Target.A_key AND  Source.P_KEY = Target.P_KEY AND  Source.R_KEY = Target.R_KEY)WHEN MATCHED THEN UPDATE set  Target.col1= Source.col1,  Target.col2= Source.col2,  Target.col3= Source.col3WHEN NOT MATCHED THEN INSERT(  Target.A_KEY,  Target.P_KEY,  Target.R_KEY)values (Source.A_KEY,Source.P_KEY,Source.R_KEY);1 row merged.

To avoid unnecessary updates, use the WHERE subclause of the UPDATE clause (see lines 12 through 16):

MERGE INTO TargetUSING SourceON (  Source.A_KEY = Target.A_key AND  Source.P_KEY = Target.P_KEY AND  Source.R_KEY = Target.R_KEY)WHEN MATCHED THEN UPDATE set  Target.col1= Source.col1,  Target.col2= Source.col2,  Target.col3= Source.col3  where 1 in (    decode(Target.col1,Source.col1,0,1),     decode(Target.col2,Source.col2,0,1),     decode(Target.col3,Source.col3,0,1)  )WHEN NOT MATCHED THEN INSERT(  Target.A_KEY,  Target.P_KEY,  Target.R_KEY)values (Source.A_KEY,Source.P_KEY,Source.R_KEY);

Finally, you are inserting the KEY columns but not the COL* columns, so next time the target row will be updated. That doesn't make sense to me. Why not do all six columns in the INSERT?

Best regards,

Stew Ashton

jaramill

Per the forum guidelines -->

on #5, it helps to post this information which could determine the type of answer you get:

5) Database Version and IDE Version
Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.If you're not sure what it is you can do the following:select * from v$version;

in an SQL*Plus session and paste the results.

Rama_G

Hi,

A quick question.

Do I need to mention the columns from ON clause in the where statements, to increase the performance ?

ON ( 

Source.A_KEY = Target.A_key AND 

  Source.P_KEY = Target.P_KEY AND 

  Source.R_KEY = Target.R_KEY 

)

into the below mentioned listed columns ?

where 1 in ( 

    decode(Target.col1,Source.col1,0,1),  

    decode(Target.col2,Source.col2,0,1),  

    decode(Target.col3,Source.col3,0,1) 

  ) 

AndrewSayer

4066099 wrote:Hi,A quick question. Do I need to mention the columns from ON clause in the where statements, to increase the performance ?ON (  Source.A_KEY = Target.A_key AND  Source.P_KEY = Target.P_KEY AND  Source.R_KEY = Target.R_KEY )into the below mentioned listed columns ? where 1 in (  decode(Target.col1,Source.col1,0,1),  decode(Target.col2,Source.col2,0,1),  decode(Target.col3,Source.col3,0,1)  ) 

No. Oracle will already be comparing them.

Please share the execution plan being used. How many rows in source? Is there an appropriate index on your target table to be used to do the join?

Rama_G

Hi Andrew,

The reason behind my question is, I could not see much performance improvement with this where statement.

Is there an appropriate index on your target table to be used to do the join?- Yes, I did check, it has Indexes on the columns being used in the ON clause.

Thanks

Rama_G

Hi Jonathan,

When I am using the suggested select statement next to the MERGE AND INTO, it is showing some error by saying that ON clause is missing or similar to the below mentioned one.

ERROR: ORACLE execute error: ORA-02012: missing USING keyword.

Regards

Rama

AndrewSayer

4066099 wrote:Hi Andrew, The reason behind my question is, I could not see much performance improvement with this where statement. Is there an appropriate index on your target table to be used to do the join?- Yes, I did check, it has Indexes on the columns being used in the ON clause.Thanks

I would expect most of the work is done in joining the two tables rather than actually doing the update so I’m not surprised that the filter didn’t improve things significantly (although is still a sensible addition).

Rather than just checking, please make sure you show us too, misinterpretations happen all the time and you don’t want to waste time when the problem is usually simple.

Again, how many rows in source? please share the execution plan so we can see how Oracle has decided to execute the merge (there are several ways and we cannot just guess). The execution plan is probably the single thing that will tell us almost everything we need to know - (most of) what it did and (most of) why it did it.

AndrewSayer

4066099 wrote:Hi Jonathan, When I am using the suggested select statement next to the MERGE AND INTO, it is showing some error by saying that ON clause is missing or similar to the below mentioned one.ERROR: ORACLE execute error: ORA-02012: missing USING keyword.RegardsRama 

Then you’ve written it with a syntax error. If you want help resolving that then you’ll need to share the code you‘ve now written.

Rama_G

Hi Andrew,

How many rows in source ? -- I am using my test data with about 1000 records. In the actual data it has 30k records.

May I know what exactly are you referring to "ececution plan".

Thanks

Rama