Update Query Performance [message #669614] |
Wed, 02 May 2018 19:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a5cf3ca70963a7e720faac4f13697753?s=64&d=mm&r=g) |
kangula17
Messages: 1 Registered: May 2018
|
Junior Member |
|
|
Hi Team,
Have below update statment which takes 60 mins on average to update data, no.of records updated approx are 38K-40K.
Including query and explain plan. Have tried replacing exists with IN but the plan and processing time is same.
Query
***************************************************************
update ordlne ordl SET sf_uid = null,
div_uid = null,
ter_uid = null,
disch_uid = null,
last_upd_user='HHHH',
last_upd_timestamp=sysdate
where ordl.lstatus NOT IN
('SHIPPED','CANCEL','DEL','RELEASE','ALLO')
and exists (select 1 from prod prd, respon res
where prd.prd_uid=ordl.prd_uid
and prd.res_uid = res.res_uid
and res.respon_code = 'MOON')
AND (ordl.lstatus = DECODE('R-10', ---- Is passed as variable through package
'R-MS',
'MS',
'R-1',
'RUSH',
'R-2',
'TODAY',
'R-4',
ordl.lstatus) OR ordl.lstatus IN ('BACKORDERED','HOLD'));
***********************************************************************************************
Explain Plan
1 Plan hash value: 1222190533
2
3 ---------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ---------------------------------------------------------------------------------------------------------
6 | 0 | UPDATE STATEMENT | | 25459 | 13M| 33792 (2)| 00:00:06 |
7 | 1 | UPDATE | ordlne | | | | |
8 | 2 | CONCATENATION | | | | | |
9 |* 3 | HASH JOIN RIGHT SEMI | | 3940 | 2081K| 2020 (2)| 00:00:01 |
10 | 4 | VIEW | VW_SQ_1 | 11280 | 143K| 1128 (2)| 00:00:01 |
11 | 5 | NESTED LOOPS | | 11280 | 264K| 1128 (2)| 00:00:01 |
12 | 6 | TABLE ACCESS BY INDEX ROWID| respon | 1 | 10 | 1 (0)| 00:00:01 |
13 |* 7 | INDEX UNIQUE SCAN | res_respon_CODE_UK | 1 | | 0 (0)| 00:00:01 |
14 | 8 | TABLE ACCESS BY INDEX ROWID| prod | 11280 | 154K| 1127 (2)| 00:00:01 |
15 |* 9 | INDEX RANGE SCAN | prd_res_FK_I | 11280 | | 45 (3)| 00:00:01 |
16 | 10 | TABLE ACCESS BY INDEX ROWID | ordlne | 3940 | 2031K| 890 (2)| 00:00:01 |
17 |* 11 | INDEX RANGE SCAN | ORDL_lstatus_IDX | 4035 | | 150 (2)| 00:00:01 |
18 |* 12 | HASH JOIN RIGHT SEMI | | 21519 | 11M| 31772 (2)| 00:00:06 |
19 | 13 | VIEW | VW_SQ_1 | 11280 | 143K| 1128 (2)| 00:00:01 |
20 | 14 | NESTED LOOPS | | 11280 | 264K| 1128 (2)| 00:00:01 |
21 | 15 | TABLE ACCESS BY INDEX ROWID| respon | 1 | 10 | 1 (0)| 00:00:01 |
22 |* 16 | INDEX UNIQUE SCAN | res_respon_CODE_UK | 1 | | 0 (0)| 00:00:01 |
23 | 17 | TABLE ACCESS BY INDEX ROWID| prod | 11280 | 154K| 1127 (2)| 00:00:01 |
24 |* 18 | INDEX RANGE SCAN | prd_res_FK_I | 11280 | | 45 (3)| 00:00:01 |
25 | 19 | INLIST ITERATOR | | | | | |
26 | 20 | TABLE ACCESS BY INDEX ROWID | ordlne | 136K| 68M| 30637 (2)| 00:00:05 |
27 |* 21 | INDEX RANGE SCAN | ORDL_lstatus_IDX | 139K| | 5001 (2)| 00:00:01 |
28 ---------------------------------------------------------------------------------------------------------
29
30 Predicate Information (identified by operation id):
31 ---------------------------------------------------
32
33 3 - access("prod_1"="ORDL"."prd_UID")
34 7 - access("res"."respon_CODE"='MOON')
35 9 - access("prd"."res_UID"="res"."res_UID")
36 11 - access("ORDL"."lstatus"='RUSH')
37 filter("ORDL"."lstatus"<>'SHIPPED' AND "ORDL"."lstatus"<>'CANCEL' AND
38 "ORDL"."lstatus"<>'RELEASE' AND "ORDL"."lstatus"<>'DEL' AND
39 "ORDL"."lstatus"<>'ALLO')
40 12 - access("prod_1"="ORDL"."prd_UID")
41 16 - access("res"."respon_CODE"='MOON')
42 18 - access("prd"."res_UID"="res"."res_UID")
43 21 - access("ORDL"."lstatus"='BACKORDERED' OR "ORDL"."lstatus"='HOLD')
44 filter("ORDL"."lstatus"<>'SHIPPED' AND "ORDL"."lstatus"<>'CANCEL' AND
45 "ORDL"."lstatus"<>'RELEASE' AND "ORDL"."lstatus"<>'DEL' AND
46 "ORDL"."lstatus"<>'ALLO' AND LNNVL("ORDL"."lstatus"='RUSH'))
|
|
|
|
Re: Update Query Performance [message #669617 is a reply to message #669614] |
Thu, 03 May 2018 00:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Most of the cost is from the indexed access path in operations 20 and 21, please can you get an EXPLAIN PLAN and then try running the query with this hint,update /*+ full(ordl) */ ordlne ordl SET sf_uid = null,
How many rows are there in ORDLNE?
|
|
|