Multiple Updates statements Vs. Single large update statement

From: Nik Tek <niktek2005_at_gmail.com>
Date: Thu, 14 May 2015 16:48:52 -0700
Message-ID: <CAHySzWV1416a7f4pVX2wWp6pRHWEu9_HT2vzEp3Df+ovduov+w_at_mail.gmail.com>

Hi Gurus,

I have a table with 73 columns in it, with data_type count as below  data_type | count

------------------+-------
 Date             |     3
 NCLOB            |    12
 INTEGER          |    25
 NVARCHAR2(255)   |    20
 NUMBER           |    13

In the application, code had multiple update statements (something like 14 update
statement) with few columns

I recommended to consolidate all the multiple update statements into a single
large update statement, and commit once

Now, with this change, performance team is reporting regressions for all the
operations that are happening on the table.

Question:
1) Is this bad solution (more curious to know why sql execution is taking longer)?
2) Is there a way to measure why sql execution is taking longer?

e.g:
== BEFORE ==

UPDATE T1 set c2 = $1, c3=$2 WHERE c1=$3; UPDATE T1 set c5 = $1, c7=$2, c8=$3, c11=$4 WHERE c1=$5; UPDATE T1 set c22 = $1, c32=$2 WHERE c1=$3; UPDATE T1 SET C10 = $1 , C12 = $2 , C21 = $3 , C31 = $4 , C41 = $5 , C43 = $6 ,
C44 = $7 , C45 = $8 , C46 = $9 , C47 = $10 , C48 = $11 , C49 = $12 WHERE C1 = $13

• AFTER == UPDATE T1 set c2 = $1, c3=$2, c5 = $3, c7=$4, c8=$5, c11=$6, c22 = $7, c32=$8, C10 = $9 , C12 = $10 , C21 = $11 , C31 = $12 , C41 = $13 , C43 = $14 , C44 = $15 , C45 = $16 , C46 = $17 , C47 = $18 , C48 = $19 , C49 = $20 WHERE C1 = $21;

Could you please provide some light, so I can investigate and explain it to my
perf team for why this is happening?

-- 
Thank you
NikTeki

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

Received on Fri May 15 2015 - 01:48:52 CEST