Home » RDBMS Server » Performance Tuning » Please help me merge query is taking more time (Oracle 11g)
Please help me merge query is taking more time [message #604853] Sat, 04 January 2014 12:40 Go to next message
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.
Re: Please help me merge query is taking more time [message #604855 is a reply to message #604853] Sat, 04 January 2014 13:17 Go to previous messageGo to next message
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
Re: Please help me merge query is taking more time [message #604863 is a reply to message #604855] Sat, 04 January 2014 22:47 Go to previous messageGo to next message
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.
Re: Please help me merge query is taking more time [message #604864 is a reply to message #604863] Sat, 04 January 2014 23:37 Go to previous messageGo to next message
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.

Re: Please help me merge query is taking more time [message #604865 is a reply to message #604864] Sun, 05 January 2014 00:03 Go to previous messageGo to next message
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.
Re: Please help me merge query is taking more time [message #604866 is a reply to message #604865] Sun, 05 January 2014 00:08 Go to previous messageGo to next message
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.
Re: Please help me merge query is taking more time [message #604867 is a reply to message #604865] Sun, 05 January 2014 00:09 Go to previous messageGo to next message
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?
Re: Please help me merge query is taking more time [message #604868 is a reply to message #604867] Sun, 05 January 2014 00:18 Go to previous messageGo to next message
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.
Re: Please help me merge query is taking more time [message #604869 is a reply to message #604868] Sun, 05 January 2014 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If OK for you, then OK for me.
Re: Please help me merge query is taking more time [message #604874 is a reply to message #604869] Sun, 05 January 2014 01:44 Go to previous messageGo to next message
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.
Re: Please help me merge query is taking more time [message #604915 is a reply to message #604868] Mon, 06 January 2014 02:44 Go to previous message
cookiemonster
Messages: 13917
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.
Previous Topic: Query is taking huge time to display the record
Next Topic: Performance issue
Goto Forum:
  


Current Time: Thu Mar 28 03:47:12 CDT 2024