Home » SQL & PL/SQL » SQL & PL/SQL » performance tuning of merge
performance tuning of merge [message #243730] Fri, 08 June 2007 12:14 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
MERGE
  INTO NP D
  USING
  (
    SELECT ITEM,
           IND,
           TREE,
           NP_IRM,
           NP,
           NP_SOURCE
      FROM TEMP_NP
  ) S
  ON
  (
        D.ITEM = S.ITEM
    AND D.IND = S.IND
    AND D.TREE = S.TREE
    AND D.NP_IRM = S.NP_IRM
  )
  WHEN MATCHED THEN
    UPDATE
      SET D.NP_SOURCE = S.NP_SOURCE,
          D.NP = S.NP,
          D.UPDATE_DATETIME = SYSDATE
  WHEN NOT MATCHED THEN
    INSERT
    (
      D.ITEM,
      D.IND,
      D.TREE,
      D.NP_IRM,
      D.NP,
      D.NP_SOURCE,
      D.UPDATE_DATETIME
    )
    VALUES
    (
      S.ITEM,
      S.IND,
      S.TREE,
      S.NP_IRM,
      S.NP,
      S.NP_SOURCE,
      SYSDATE
    );

Explain plan

MERGE STATEMENT ()	[NULL]
 MERGE ()	NP
  VIEW ()	[NULL]
   HASH JOIN (OUTER)	[NULL]
    TABLE ACCESS (FULL)	TEMP_NP
    TABLE ACCESS (FULL)	NP


Both NP & temp_NP were analyzed just before executing the merge statement.

NP has about 5 million rows.

temp_NP has about 500,000 rows.

It takes about 9 minutes to merge. Is there a way to improve this.

Anu
Re: performance tuning of merge [message #243732 is a reply to message #243730] Fri, 08 June 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
PLEASE stop opening new threads for same problem.
http://www.orafaq.com/forum/m/243557/74940/#msg_243557
Please answer ALL previously asked questions.
Re: performance tuning of merge [message #243733 is a reply to message #243730] Fri, 08 June 2007 12:29 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
yes.. i posted yesterday assuming i could use update. But i may have new rows in temp_np, so i need to insert the new rows in NP table. So i decided to use the merge.

I am using oracle 9.2.0.7

Following indexes are present

CREATE INDEX   IDX1_NET_PRICE
    ON   NET_PRICE(ITEM_SID)
TABLESPACE TEST_DATA
/

CREATE INDEX   IDX2_NET_PRICE
    ON   NET_PRICE(MAP_EFF_IRM)
TABLESPACE TEST_DATA
/

CREATE INDEX   IDX3_NET_PRICE
    ON   NET_PRICE(EST_METHOD_CODE)
TABLESPACE TEST_DATA
/

CREATE INDEX   IDX4_NET_PRICE
    ON   NET_PRICE(TREE_VERSION_SID)
TABLESPACE TEST_DATA
/

CREATE INDEX   IDX5_NET_PRICE
    ON   NET_PRICE(PPI_INDEX_SID)
TABLESPACE TEST_DATA
/


NO INDEXES ARE THERE FOR TEMP_NET_PRICE.

Re: performance tuning of merge [message #243741 is a reply to message #243730] Fri, 08 June 2007 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Looks good for me.
Try to enlarge your hash area.

Regards
Michel
Re: performance tuning of merge [message #243765 is a reply to message #243730] Fri, 08 June 2007 16:30 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
We increased the hash_area_size.. but that didnt seem to help.

here is the tkprof output.
MERGE INTO NET_PRICE D USING ( SELECT ITEM_SID, PPI_INDEX_SID, 
  TREE_VERSION_SID, NET_PRICE_IRM, NET_PRICE, NET_PRICE_SOURCE FROM 
  TEMP_NET_PRICE ) S ON ( D.ITEM_SID = S.ITEM_SID AND D.PPI_INDEX_SID = 
  S.PPI_INDEX_SID AND D.TREE_VERSION_SID = S.TREE_VERSION_SID AND 
  D.NET_PRICE_IRM = S.NET_PRICE_IRM ) WHEN MATCHED THEN UPDATE SET 
  D.NET_PRICE_SOURCE = S.NET_PRICE_SOURCE, D.NET_PRICE = S.NET_PRICE, 
  D.UPDATE_DATETIME = SYSDATE WHEN NOT MATCHED THEN INSERT ( D.ITEM_SID, 
  D.PPI_INDEX_SID, D.TREE_VERSION_SID, D.NET_PRICE_IRM, D.NET_PRICE, 
  D.NET_PRICE_SOURCE, D.UPDATE_DATETIME ) VALUES ( S.ITEM_SID, 
  S.PPI_INDEX_SID, S.TREE_VERSION_SID, S.NET_PRICE_IRM, S.NET_PRICE, 
  S.NET_PRICE_SOURCE, SYSDATE )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     73.68     314.72     125768      40831    2044595      506757
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     73.68     314.72     125768      40831    2044595      506757

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  MERGE  
 506757   VIEW  
 506757    HASH JOIN OUTER 
 506757     TABLE ACCESS FULL TEMP_NET_PRICE 
4639556     TABLE ACCESS FULL NET_PRICE 



Ananthi
Re: performance tuning of merge [message #243767 is a reply to message #243730] Fri, 08 June 2007 16:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you are desperate you could try adding indexes onto the "temp" columns (S.*) below:
        D.ITEM   = S.ITEM
    AND D.IND    = S.IND
    AND D.TREE   = S.TREE
    AND D.NP_IRM = S.NP_IRM

[Updated on: Fri, 08 June 2007 16:41] by Moderator

Report message to a moderator

Re: performance tuning of merge [message #243777 is a reply to message #243765] Fri, 08 June 2007 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You increased it from what to what?
Do you use PGA_AGGREGATE_TARGET?

Regards
Michel
Re: performance tuning of merge [message #243783 is a reply to message #243777] Sat, 09 June 2007 00:17 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could try indexing the four join columns from NP in a single index.

But I don't think that's your problem. I think it's taking much less time to perform the join (probably less than a minute: 5M rows is not many) and all the time is being spent performing the update and insert.

Updates tend to be a lot slower than inserts. Even more so if you add the /*+APPEND*/ hint, which will result in the inserted rows using a direct-path insert (this will only help if there are a lot of inserts).

If you are performing mostly inserts (>90%) then definitely consider the /*+APPEND*/ hint to the MERGE. It will probably do the trick for you. It will still be useful if you have fewer inserts, but it probably won't make a critical difference.

Considering you are merging around 10% of the table, if there are lots of updates it would almost certainly be faster to use one of the techniques described here, such as truncate/replace and dropping/invalidating indexes.

Ross Leishman
Previous Topic: Doubts abt various data objects...
Next Topic: Query for Table & Index Growth
Goto Forum:
  


Current Time: Sat Dec 10 03:10:49 CST 2016

Total time taken to generate the page: 0.12863 seconds