Re: Update statement performance

From: Milo <xueyuan.luo_at_gmail.com>
Date: Wed, 26 Feb 2014 12:16:28 +0800
Message-ID: <201402261216238625511_at_gmail.com>

Hi, Ramanan
Could you get the sql report on this update sql? May be some statistics of the sql can explain why. Thanks very much.

Best Regards,
Milo

From: Praveen Ramanan
Date: 2014-02-26 12:00
To: oracle-l
Subject: Update statement performance
Hello All,

I am using oracle 11gr2 on windows server 2008.

This is a test system.Stats are current and archivelog is turned off temporarily.

I am trying to update a table that has over 1.1 million rows.

I have a simple update statement that does the following. (basically updates mycolumn when it is null, to somevalue)

update mytable a set a.mycolumn = (select b.mycolumn from

                                                                  mytable b where

                                                                  a.joincolumn = b.joincolumn and

                                                                 b.anothercol='someval')

where a.mycolumn is null;

The number of rows where mycolumn is null is around 1/5th of the total. (225000 rows)

The above update statement seems to be running forever.On checking the explain plan this is what i see.  

---

| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                          |   224K|  2630K|  6454M  (1)|127:19:43 |
|   1 |  UPDATE                      | mytable            |       |       |            |          |


|*  2 |   TABLE ACCESS FULL          |mytable            |   224K|  2630K|  6644   (1)| 00:00:01 |


|*  3 |   TABLE ACCESS BY INDEX ROWID| mytable           |     1 |    21 | 28752   (1)| 00:00:03 |


|*  4 |    INDEX RANGE SCAN          | mytable_someval_INDEX |   874K|       |  3318   (1)| 00:00:01 |


---------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):

---

 

   2 - filter("a"."mycolumn" IS NULL)
   3 - filter("b"."joincolumn"=:B1)
   4 - access("b"."anothercol"='somecol')

I know i can use CTAS to achieve my task.However i am not sure why updating a .225 million rows would take hours.

Can my update statement be written more efficiently? (perhaps using merge update?)

Maybe there is some other bottleneck that I need to look?

Any thoughts would be very well appreciated. Thanks!!!  

--

Thousands of candles can be lighted from a single candle, and the life of the single candle will not be shortened. Happiness never decreases by being shared.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 26 2014 - 05:16:28 CET