Re: Multiple Updates statements Vs. Single large update statement

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Fri, 15 May 2015 04:07:09 +0200
Message-ID: <555554CD.7090204_at_bluewin.ch>



Hi Nik,

as Mark already pointed out it is to be expected that one update is faster than many.
As far as I understand you text the updates are currently split into several doing each a substet of the columns (rather than a subset of the rows)?
By my experience developers usually try the version that is less coding time which would be a big update. That makes me think that there is a chance that a developer has already tried the big update and found it sow. Just speculation of course.
What we need is evidence that could be a sql monitor, a trace or probably also a sql statement level ash report would be helpful. You need evidence for both, the small update and the big update. What could have happened in the big update is for example that you are overrunning the PGA writing to temp. Again, just a speculation (don't know how this could happen) , but it should motivate to research proving that is could be worth while checking this out. PGA can be increased.:-)

Thanks

Lothar
On 15.05.2015 03:42, Nik Tek wrote:
> Hi Mark,
>
> Yes, perf team is saying sql executions are taking long (slower).
>
> Yes, $3, $5, $13 all remained the same in before and after case.
>
> Yes, every column is updated everytime (NOT under any condition check).
>
> Yes, few columns have indexes, but why is the single large statement
> is slow (but not in the smaller update statements case).
>
> Commit happens at the end, and there is a single commit in before change.
>
> Nothing has changed on overall update statements. All I did was
> consolidate the update statements (did not change the commit behavior).
>
> Thank you
> Nik
>
>
>
> On Thu, May 14, 2015 at 6:14 PM, Mark W. Farnham <mwf_at_rsiz.com
> <mailto:mwf_at_rsiz.com>> wrote:
>
> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *raza siddiqui
> *Sent:* Thursday, May 14, 2015 8:29 PM
> *To:* niktek2005_at_gmail.com <mailto:niktek2005_at_gmail.com>; 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
>
> --
> Oracle <http://www.oracle.com>
> Raza Siddiqui | Senior Principal Curriculum Product Manager (DB
> Server)
>
> OracleOracle 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
>
>
>
>
> --
> Thank you
> NikTeki

-- 





--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 15 2015 - 04:07:09 CEST

Original text of this message