Home » RDBMS Server » Performance Tuning » query taking long time (10.2.0.4)
query taking long time [message #523322] Fri, 16 September 2011 13:47 Go to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member

Hi Experts,
Following query is taking 33 mins to execute.
Could you please help me out in figuring the
reason for this to take so long.
MERGE INTO ITM_INTFC_EV a USING
  (SELECT 1 ITM_EV_SEQ_NBR,
            'EAGAN' ITM_EV_SERVR_SITE_CD,
                    'IDA' EV_RCVR_CD,
                          'MSG' MSG_TYPE_CD
   FROM DUAL) b ON (a.ITM_EV_SEQ_NBR= b.ITM_EV_SEQ_NBR
                    AND a.ITM_EV_SERVR_SITE_CD= b.ITM_EV_SERVR_SITE_CD
                    AND a.EV_RCVR_CD= b.EV_RCVR_CD
                    AND a.MSG_TYPE_CD= b.MSG_TYPE_CD) WHEN MATCHED THEN
UPDATE
SET a.INTFC_EXPRT_IND='N' ,
                      a.SYNCH_STS_CD='1' ,
                                     a.UPDT_USER_ID='1' ,
                                                    a.UPDT_USER_SITE_ID='1' ,
                                                                        a.LAST_UPDT_LCL_DTM=sysdate ,
                                                                        a.LAST_UPDT_UTC_DTM=sysdate
WHERE a.LAST_UPDT_UTC_DTM<=sysdate WHEN NOT MATCHED THEN
  INSERT (a.ITM_EV_SEQ_NBR,
          a.ITM_EV_SERVR_SITE_CD,
          a.EV_RCVR_CD,
          a.MSG_TYPE_CD,
          a.INTFC_EXPRT_IND,
          a.SYNCH_STS_CD,
          a.UPDT_USER_ID,
          a.UPDT_USER_SITE_ID,
          a.LAST_UPDT_LCL_DTM,
          a.LAST_UPDT_UTC_DTM)
  VALUES (1 ,
          'EAGAN' ,
          'IDA' ,
          'MSG' ,
          'N' ,
          '1' ,
          '1' ,
          '1' ,
          sysdate ,
          sysdate);


Please find the explain plan:-
----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT           |              |     1 |    90 |   157   (2)| 00:00:03 |       |       |
|   1 |  MERGE                    | ITM_INTFC_EV |       |       |            |          |       |       |
|   2 |   VIEW                    |              |       |       |            |          |       |       |
|   3 |    NESTED LOOPS OUTER     |              |     1 |    77 |   157   (2)| 00:00:03 |       |       |
|   4 |     FAST DUAL             |              |     1 |       |     3   (0)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE ALL   |              |     1 |    77 |   154   (2)| 00:00:03 |     1 |   290 |
|   6 |      PARTITION LIST SINGLE|              |     1 |    77 |   154   (2)| 00:00:03 |   KEY |   KEY |
|*  7 |       TABLE ACCESS FULL   | ITM_INTFC_EV |     1 |    77 |   154   (2)| 00:00:03 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter("A"."ITM_EV_SEQ_NBR"(+)=1 AND "A"."MSG_TYPE_CD"(+)='MSG' AND
              "A"."EV_RCVR_CD"(+)='IDA' AND "A"."ITM_EV_SERVR_SITE_CD"(+)='EAGAN')
Re: query taking long time [message #523323 is a reply to message #523322] Fri, 16 September 2011 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Following query is taking 33 mins to execute.
why does the EXPLAIN PLAN show 3 seconds?

How many rows really get UPDATED & INSERTED?
Re: query taking long time [message #523324 is a reply to message #523322] Fri, 16 September 2011 13:57 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
What indexes do you have on the ITM_INTFC_EV table?
Re: query taking long time [message #523326 is a reply to message #523324] Fri, 16 September 2011 14:07 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Please find the index and column names.


INDEX_NAME	COLUMN_NAME
ITM_INTFC_EV_MSG_IDX	MSG_UTC_DTM
ITM_INTFC_EV_MV_IDX	INTFC_EXPRT_IND
ITM_INTFC_EV_MV_IDX	LAST_UPDT_UTC_DTM
ITM_INTFC_EV_SYNCH_IDX	SYNCH_STS_CD


Thanks,
Varun
Re: query taking long time [message #523327 is a reply to message #523326] Fri, 16 September 2011 14:13 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
You don't have an index on the columns you are using to match the row, so your query has to scan the entire table to find it. So either create an index on the columns in your ON clause, or live with the 33 minutes.

Only my opinion - other people might advise differently.
Re: query taking long time [message #523330 is a reply to message #523327] Fri, 16 September 2011 14:26 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Thanks John I will create index on those 4 columns and  will
try again.
I am just surprised that it is showing 3 secs execution time
in execution plan

Thanks,
Varun
Previous Topic: session_cached_cursors
Next Topic: POWER function slows query way down?
Goto Forum:
  


Current Time: Wed Apr 24 06:36:14 CDT 2024