RE: Multiple Updates statements Vs. Single large update statement

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <raza.siddiqui@xxxxxxxxxx>, <niktek2005@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 14 May 2015 21:14:54 -0400

It is the opposite of expected that the change described would make things
slower. (I’m presuming by “the team is reporting regressions for all the
operations happening on the table” you mean they are slower.)



Since there are differences in the symbolic values, it leads to a suspicion
that the code might not be iso-functional. Are $3, $5, and $13 from the
“before” all the same and the same as $21 in the “after?”



Are all the columns updated every time, or only under certain conditions? If
one of the column updates is relatively expensive but is only done some of the
time, for example, in the “before” version, then that would be an explanation.



Are any of the columns being updated parts of indexes or constraints?



Does the commit take place immediately? Is there a single commit for the
“before version?”



Sql trace and/or session statistics regarding waits would be useful ways to
investigate actual performance, and of course if your code is instrumented
finer granularity is available. (see MethodR)



mwf



From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of raza siddiqui
Sent: Thursday, May 14, 2015 8:29 PM
To: niktek2005@xxxxxxxxx; ORACLE-L
Subject: Re: Multiple Updates statements Vs. Single large update statement



Very important to understand concept of a TRANSACTION - the unit of work bound
by COMMIT.

If all the work is part of the same transaction, then you CAN have a single or
multiple DML statements terminated by the COMMIT. If the "work" being done are
for different transaction, then you MUST COMMIT inbetween.

QUESTION:

- Of the 73 columns, how many are being updated as part of this transaction ?
- You haven't mentioned how many rows are in the table and whether INDEXES are
being used.
- Hoping the NCLOB is NOT being updated.

Corrections welcome.
Raza

On 5/14/2015 4:48 PM, Nik Tek wrote:

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.oracle.com> Oracle
Raza Siddiqui | Senior Principal Curriculum Product Manager (DB Server)

Oracle Oracle University - Products & Services
500 Oracle Parkway, MS 4IP3 | Redwood Shores, CA 94065
<http://www.oracle.com/commitment> GreenOracleOracle is committed to
developing practices and products that help protect the environment

GIF image

GIF image

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2015