Home » RDBMS Server » Performance Tuning » Update Query Performance (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0)
Update Query Performance [message #669614] Wed, 02 May 2018 19:36 Go to next message
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'))

Report message to a moderator

Re: Update Query Performance [message #669616 is a reply to message #669614] Wed, 02 May 2018 19:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Let us start with the basics.
1) you claim it takes 60 minutes to complete UPDATE, but offer ZERO proof this is correct.
2) you post EXPLAIN PLAN which reports the statement should take only 6 seconds to complete.
3) you state "no.of records updated approx are 38K-40K"
4) PLAN shows 25,459 rows

I observe a major disconnect between posted statements & observable facts.
Somehow & some way above does not reflect the truth, the whole truth, & nothing but the truth.
Do all the tables & indexes have current & accurate statistics?
Was EXPLAIN PLAN produced against same tables & indexes where 60 minute run time occurs?

we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/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

I don't know what is fact & what is fiction in original post above.

Report message to a moderator

Re: Update Query Performance [message #669617 is a reply to message #669614] Thu, 03 May 2018 00:49 Go to previous message
John Watson
Messages: 8963
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?

Report message to a moderator

Previous Topic: A case need to help clear
Next Topic: Stored procedure taking long time to run
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 23 15:05:20 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.