RE: Multiple Updates statements Vs. Single large update statement

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 14 May 2015 23:51:08 -0400
Message-ID: <085a01d08ec2$616eb7f0$244c27d0$_at_rsiz.com>

Almost all the data is in. You didn’t comment on constraints. I *theorize* that certain combinations of constraints might cause a single update involving columns involved in a constraint support index could require extra locks or something compared to two disjoint updates constructed to avoid that problem.  

Another wildcard is communications buffers, in the case that some of these values being passed are large.

Another wildcard is if the updates are carefully arranged such that shrinking updates happen before lengthening updates possibly avoiding row migration.

Another wildcard is if there is row migration or chaining due to the update and it somehow is handled more efficiently piecemeal.

I suppose there are cases where undo in redo takes place in the piecemeal updates and some size limit kicks in to do regular undo in the single update.  

While possible in my mind, I’ve never experienced any of these issues erupting in the wild or my “lab.”  

One other thing: Is this a single row at a time, or are there sets of tuples for a given where = predicate? (If big sets, Lothar’s point below is a leading candidate explanation).  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lothar Flatz Sent: Thursday, May 14, 2015 10:07 PM
To: oracle-l_at_freelists.org; niktek2005_at_gmail.com Subject: Re: Multiple Updates statements Vs. Single large update statement  

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> 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] On Behalf Of raza siddiqui Sent: Thursday, May 14, 2015 8:29 PM
To: 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  

--

 <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> Green OracleOracle 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 - 05:51:08 CEST