Home » SQL & PL/SQL » SQL & PL/SQL » Merge - performance issue (oracle 10.2.0.2)
Merge - performance issue [message #330161] Fri, 27 June 2008 14:10 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
This merge statement when we ran yesterday didnt finish at all. earlier it used to finish by 18 minutes.

merge  into np c
      using  br_temp_np e
      on (c.i_sid = e.i_sid
         and c.t_sid = e.t_sid
         and c.p_sid = e.p_sid
	     and c.m_eff_month = e.m_eff_month
         and c.np_month = e.np_month)
      when matched then
        update set
        c.np = e.np
      when not matched then
       insert 
     (i_sid, t_sid, p_sid, m_eff_month, np_month,
       np       )
       values
     (e.i_sid, e.t_sid, e.p_sid, e.m_eff_month, e.np_month,
      e.np)
   ;

EXPLAIN PLAN OUTPUT


MERGE STATEMENT ()	[NULL]
 MERGE ()	NP
  VIEW ()	[NULL]
   HASH JOIN (OUTER)	[NULL]
    TABLE ACCESS (FULL)	BR_TEMP_NP
    PARTITION RANGE (ALL)	[NULL]
     TABLE ACCESS (FULL)	NP


NP table has about 10 million records. BR_TEMP_NP has about 1.5 million records.


Anu

Re: Merge - performance issue [message #330163 is a reply to message #330161] Fri, 27 June 2008 14:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you post the following output running from the sqlplus prompt.
select * from v$version;
explain plan
for
<query>;

set lines 133
select * from table(dbms_xplan.display());

Also check this link for what it is worth.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

Regards

Raj
Re: Merge - performance issue [message #330165 is a reply to message #330161] Fri, 27 June 2008 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>This merge statement when we ran yesterday didnt finish at all. earlier it used to finish by 18 minutes.
What changed?
Re: Merge - performance issue [message #330166 is a reply to message #330161] Fri, 27 June 2008 14:44 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member




Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE\t10.2.0.2.0\tProduction
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Plan hash value: 579918951
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |                   |  1573K|   237M|       | 62029   (3)| 00:12:25 |       |       |
|   1 |  MERGE                 | NP		   |       |       |       |            |          |       |       |
|   2 |   VIEW                 |                   |       |       |       |            |          |       |       |
|*  3 |    HASH JOIN OUTER     |                   |  1573K|   160M|    90M| 62029   (3)| 00:12:25 |       |       |
|   4 |     TABLE ACCESS FULL  | BR_TEMP_NP        |  1573K|    72M|       |  3234   (5)| 00:00:39 |       |       |
|   5 |     PARTITION RANGE ALL|                   |  9830K|   553M|       | 20872   (4)| 00:04:11 |     1 |     6 |
|   6 |      TABLE ACCESS FULL | NP                |  9830K|   553M|       | 20872   (4)| 00:04:11 |     1 |     6 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."NP_MONTH"(+)="E"."NP_MONTH" AND "C"."M_EFF_MONTH"(+)="E"."M_EFF_MONTH" AND 
              "C"."P_SID"(+)="E"."P_SID" AND "C"."T_SID"(+)="E"."T_SID" AND 
              "C"."I_SID"(+)="E"."I_SID")

Re: Merge - performance issue [message #330171 is a reply to message #330161] Fri, 27 June 2008 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Which columns have indexes?


Are statistics current on all involved objects?
Re: Merge - performance issue [message #330172 is a reply to message #330166] Fri, 27 June 2008 15:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Considering the size of the table I will be inclined to use the approach mentioned in this link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

As @Ana already mentioned are you aware of any changes that has happened recently since the last merge completed successfully?

Regards

Raj
Re: Merge - performance issue [message #330467 is a reply to message #330172] Mon, 30 June 2008 04:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If it was running in 18 minutes, then it surely was not updating millions of rows. Perhaps - even though the volumes have changed, there are more rows to update. Updates take heaps longer than inserts.

Could it be that it has already run once before and inserted heaps of rows, and this is a second run that is updating them?

Ross Leishman
Previous Topic: Generic SELECT procedure.
Next Topic: call an function in a package with select statement?
Goto Forum:
  


Current Time: Thu Dec 08 23:44:33 CST 2016

Total time taken to generate the page: 0.11765 seconds