OraFAQ Forum: Performance Tuning » Please help me merge query is taking more time

Home » RDBMS Server » Performance Tuning » Please help me merge query is taking more time (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me merge query is taking more time [message #604853]

Sat, 04 January 2014 12:40

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi Experts,

This merge statement is taking nearly 2 hours.

If I run the select I am getting the output in 1 minute.

SELECT QI.QI_ID, QI.QI_OFFERNO, QI_BUILDNO, QI.QI_UPDATE_TIME, GHS.TEAM, GHS.TEAM_ID
        FROM QUANTITY_ITEM QI
        INNER JOIN ITEM_MASTER IEM ON QI.QI_ID = IEM.ORG_ID
        INNER JOIN GOODS_DELV GHS ON IEM.REGION_ID = GHS.REGION_ID AND QI.QI_CC_Channel = GHS.Channel
        WHERE QI.QI_INSIDE_SALES_TEAM IS NULL
        AND QI.IS_PROCESSED = 'N'
        AND QI.QI_ID = 888

This merge is updating 75000 records.

 MERGE INTO QUANTITY_ITEM QI
    USING
    (
        SELECT QI.QI_ID, QI.QI_OFFERNO, QI_BUILDNO, QI.QI_UPDATE_TIME, GHS.TEAM, GHS.TEAM_ID
        FROM QUANTITY_ITEM QI
        INNER JOIN ITEM_MASTER IEM ON QI.QI_ID = IEM.ORG_ID
        INNER JOIN GOODS_DELV GHS ON IEM.REGION_ID = GHS.REGION_ID AND QI.QI_CC_Channel = GHS.Channel
        WHERE QI.QI_INSIDE_SALES_TEAM IS NULL
        AND QI.IS_PROCESSED = 'N'
        AND QI.QI_ID = 888
    ) GOOD_INFO
    ON(
            QI.QI_ID = GOOD_INFO.QI_ID
        AND QI.QI_OFFERNO = GOOD_INFO.QI_OFFERNO
        AND QI.QI_BUILDNO = GOOD_INFO.QI_BUILDNO
        AND QI.QI_UPDATE_TIME = GOOD_INFO.QI_UPDATE_TIME
        AND QI.IS_PROCESSED = 'N'
    )
    WHEN MATCHED THEN
    UPDATE SET QI.QI_INSIDE_SALES_TEAM = GOOD_INFO.TEAM,
        QI.QI_GOOD_INFO_ID = GOOD_INFO.TEAM_ID
    WHERE QI.QI_INSIDE_SALES_TEAM IS NULL
    AND QI.IS_PROCESSED = 'N'
    AND QI.QI_ID = 888;

Please help me why the merge statement is taking more time to update 75000 records.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604855 is a reply to message #604853]

Sat, 04 January 2014 13:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Please help me merge query is taking more time [message #604863 is a reply to message #604855]

Sat, 04 January 2014 22:47

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

HI

Please find the explain plan.

Explain plan for merge statement.

--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)|                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
|   0 | MERGE STATEMENT                    |                  |     1 |  2310 |     2   (0)|                                                                                                                                                                                                                
|   1 |  MERGE                             | QUANTITY_ITEM    |       |       |            |                                                                                                                                                                                                                
|   2 |   VIEW                             |                  |       |       |            |                                                                                                                                                                                                                
|   3 |    NESTED LOOPS                    |                  |       |       |            |                                                                                                                                                                                                                
|   4 |     NESTED LOOPS                   |                  |     1 |  2433 |     2   (0)|                                                                                                                                                                                                                
|   5 |      NESTED LOOPS                  |                  |     1 |  2343 |     1   (0)|                                                                                                                                                                                                                
|   6 |       MERGE JOIN CARTESIAN         |                  |     1 |   168 |     1   (0)|                                                                                                                                                                                                                
|   7 |        TABLE ACCESS BY INDEX ROWID | ITEM_MASTER      |     1 |    26 |     1   (0)|                                                                                                                                                                                                                
|*  8 |         INDEX RANGE SCAN           | IDX_ORG_ID       |     1 |       |     1   (0)|                                                                                                                                                                                                                
|   9 |        BUFFER SORT                 |                  |     1 |   142 |     0   (0)|                                                                                                                                                                                                                
|* 10 |         TABLE ACCESS BY INDEX ROWID| QUANTITY_ITEM    |     1 |   142 |     0   (0)|                                                                                                                                                                                                                
|* 11 |          INDEX RANGE SCAN          | IDX_IS_PROCESSED |     1 |       |     0   (0)|                                                                                                                                                                                                                
|* 12 |       TABLE ACCESS BY INDEX ROWID  | QUANTITY_ITEM    |     1 |  2175 |     0   (0)|                                                                                                                                                                                                                
|* 13 |        INDEX RANGE SCAN            | IDX_IS_PROCESSED |     1 |       |     0   (0)|                                                                                                                                                                                                                
|* 14 |      INDEX RANGE SCAN              | SYS_C0039480     |     1 |       |     1   (0)|                                                                                                                                                                                                                
|  15 |     TABLE ACCESS BY INDEX ROWID    | GOODS_DELV       |     1 |    90 |     1   (0)|                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   8 - access("IEM"."ORG_ID"=888)                                                                                                                                                                                                                                                                           
  10 - filter("QI"."QI_INSIDE_SALES_TEAM" IS NULL AND "QI"."QI_ID"=888)                                                                                                                                                                                                                                     
  11 - access("QI"."IS_PROCESSED"='N')                                                                                                                                                                                                                                                                      
  12 - filter("QI"."QI_ID"=888 AND "QI"."QI_OFFERNO"="QI"."QI_OFFERNO" AND                                                                                                                                                                                                                                  
              "QI"."QI_BUILDNO"="QI"."QI_BUILDNO" AND "QI"."QI_LASTUPDATEDTIMESTAMP"="QI"."QI_LAST                                                                                                                                                                                                          
              UPDATEDTIMESTAMP")                                                                                                                                                                                                                                                                            
  13 - access("QI"."IS_PROCESSED"='N')                                                                                                                                                                                                                                                                      
  14 - access("BUM"."REGION_ID"="CHS"."REGION_ID")                                                                                                                                                                                                                                                          
       filter("QI"."QI_CC_CHANNEL"=SYS_OP_C2C("CHS"."CHANNEL"))

Please help me.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604864 is a reply to message #604863]

Sat, 04 January 2014 23:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This merge statement is taking nearly 2 hours.
Forgive me but I seriously doubt that post EXPLAIN PLAN took 2 hours to complete; since only 1 row is returned in EXPLAIN PLAN.
Please post some proof that the posted details are from exact same SQL against exact same database.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604865 is a reply to message #604864]

Sun, 05 January 2014 00:03

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

In the merge statement I have used the table QUANTITY_ITEM out side and inside the join.Is it correct .
Can you please check my merge statement once.

MERGE INTO QUANTITY_ITEM QI
    USING
    (
        SELECT QI.QI_ID, QI.QI_OFFERNO, QI_BUILDNO, QI.QI_LASTUPDATEDTIMESTAMP, GHS.TEAM, GHS.TEAM_ID
        FROM QUANTITY_ITEM QI
        INNER JOIN ITEM_MASTER IEM ON QI.QI_ID = IEM.ORG_ID
        INNER JOIN GOODS_DELV GHS ON IEM.REGION_ID = GHS.REGION_ID AND QI.QI_CC_Channel = GHS.Channel
        WHERE QI.QI_INSIDE_SALES_TEAM IS NULL
        AND QI.IS_PROCESSED = 'N'
        AND QI.QI_ID = 888
    ) INSIDE_TEAM

Please help me.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604866 is a reply to message #604865]

Sun, 05 January 2014 00:08

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

I have gathered the stats on all the tables and providing execution plan.

The records updated were 73349.

------------------------------------------------------------------------------------------------                                                                                                                                                                                                            
| Id  | Operation                       | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)|                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------                                                                                                                                                                                                            
|   0 | MERGE STATEMENT                 |                 | 75675 |    44M|       |  4953   (1)|                                                                                                                                                                                                            
|   1 |  MERGE                          | QUANTITY_ITEM   |       |       |       |            |                                                                                                                                                                                                            
|   2 |   VIEW                          |                 |       |       |       |            |                                                                                                                                                                                                            
|*  3 |    HASH JOIN                    |                 | 75675 |    43M|       |  4953   (1)|                                                                                                                                                                                                            
|   4 |     TABLE ACCESS FULL           | GOODS_DELV      |    24 |   768 |       |     3   (0)|                                                                                                                                                                                                            
|*  5 |     HASH JOIN                   |                 | 73347 |    40M|       |  4949   (1)|                                                                                                                                                                                                            
|   6 |      TABLE ACCESS BY INDEX ROWID| ITEM_MASTER     |     1 |     7 |       |     2   (0)|                                                                                                                                                                                                            
|*  7 |       INDEX RANGE SCAN          | IDX_ORG_ID      |     1 |       |       |     1   (0)|                                                                                                                                                                                                            
|*  8 |      HASH JOIN                  |                 | 73347 |    39M|  7600K|  4946   (1)|                                                                                                                                                                                                            
|*  9 |       TABLE ACCESS FULL         | QUANTITY_ITEM   | 73348 |  6733K|       |  1441   (1)|                                                                                                                                                                                                            
|* 10 |       TABLE ACCESS FULL         | QUANTITY_ITEM   | 73348 |    33M|       |  1441   (1)|                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   3 - access("IEM"."REGION_ID"="CHS"."REGION_ID" AND                                                                                                                                                                                                                                                       
              "QI"."QI_CC_CHANNEL"=SYS_OP_C2C("CHS"."CHANNEL"))                                                                                                                                                                                                                                             
   5 - access("QI"."QI_ID"="IEM"."ORG_ID")                                                                                                                                                                                                                                                                  
   7 - access("IEM"."ORG_ID"=888)                                                                                                                                                                                                                                                                           
   8 - access("QI"."QI_ID"="QI"."QI_ID" AND "QI"."QI_OFFERNO"="QI"."QI_OFFERNO" AND                                                                                                                                                                                                                         
              "QI"."QI_BUILDNO"="QI"."QI_BUILDNO" AND "QI"."QI_LASTUPDATEDTIMESTAMP"="QI"."QI_LASTUPDA                                                                                                                                                                                                      
              TEDTIMESTAMP")                                                                                                                                                                                                                                                                                
   9 - filter("QI"."IS_PROCESSED"='N' AND "QI"."QI_ID"=888 AND                                                                                                                                                                                                                                              
              "QI"."QI_INSIDE_SALES_TEAM" IS NULL)                                                                                                                                                                                                                                                          
  10 - filter("QI"."IS_PROCESSED"='N' AND "QI"."QI_ID"=888)

Please help me.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604867 is a reply to message #604865]

Sun, 05 January 2014 00:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is it correct .
I have no idea what you deem to be correct.
SQL always provides "correct" results; but did you ask the correct question?

Report message to a moderator

Re: Please help me merge query is taking more time [message #604868 is a reply to message #604867]

Sun, 05 January 2014 00:18

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

In the merge statement I have used the table QUANTITY_ITEM out side and inside the join.Is it correct .
Is it really required the QUANTITY_ITEM in the join also.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604869 is a reply to message #604868]

Sun, 05 January 2014 00:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If OK for you, then OK for me.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604874 is a reply to message #604869]

Sun, 05 January 2014 01:44

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

I have posted the latest execution plan.

Can you please help me.

How to improve the performance.

Thanks.

Report message to a moderator

Re: Please help me merge query is taking more time [message #604915 is a reply to message #604868]

Mon, 06 January 2014 02:44

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

ramya_162 wrote on Sun, 05 January 2014 06:18

Hi,

In the merge statement I have used the table QUANTITY_ITEM out side and inside the join.Is it correct .
Is it really required the QUANTITY_ITEM in the join also.

Please help me.

Thanks.

How do you think we're going to know if the sql is correct if you don't tell us what you want it to?
You have to explain the logic you are trying to apply.

Report message to a moderator

Previous Topic:	Query is taking huge time to display the record
Next Topic:	Performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 20:23:57 CST 2024