query taking long time [message #523322] |
Fri, 16 September 2011 13:47 |
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 #523326 is a reply to message #523324] |
Fri, 16 September 2011 14:07 |
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 |
John Watson
Messages: 8951 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 |
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
|
|
|