Re: Multiple Updates statements Vs. Single large update statement

From: raza siddiqui <raza.siddiqui_at_oracle.com>
Date: Thu, 14 May 2015 17:29:06 -0700
Message-ID: <55553DD2.6040201_at_oracle.com>

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

-- 
Oracle <http://www.oracle.com>
Raza Siddiqui | Senior Principal Curriculum Product Manager (DB Server)
<tel:+1%20408%20387%204928>
Oracle Oracle University - Products & Services
500 Oracle Parkway, MS 4IP3 | Redwood Shores, CA 94065
Green Oracle <http://www.oracle.com/commitment> Oracle is committed to 
developing practices and products that help protect the environment

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

Received on Fri May 15 2015 - 02:29:06 CEST