Oracle Scratchpad

December 28, 2006

Missing the Point

Filed under: Infrastructure,Performance — Jonathan Lewis @ 8:32 pm GMT Dec 28,2006

One of the little features of WordPress is that you can see where incoming readers came from (their search criteria, or a site that redirected them). Yesterday I was quite interested to see a few viewers coming in from a Burleson Consulting article [ed: no longer available] which referenced a page I had written about getting some help from Statspack for a recent trouble-shooting exercise.

Naturally I had to see what Mr. Burleson had to say. Unsurprisingly he was fairly selective in what he said, picking the bits that best suited the purpose of advertising one of his books and closing with an advert for a website that he has helped develop and which he claims to be an “expert system” to help you learn how to spot system-wide performance problems.

Most significantly, Mr. Burleson managed to miss the fact that when I fixed the most “obvious” and resource-intensive problem reported by Statspack, the batch response time did not improve. He also missed the summarising statement:

The moral of the story: Statspack can help you spot the heavy hitters but sometimes you really need to know which items are in the critical path.

He even managed to claim that someone commenting on my note had used a “similar approach” (which, by this point in his screed, implied the methods he had quoted from his book) to get massive performance improvements although the commentator had explicitly been following up my points about “the critical path”, and “only doing the work that needed to be done”.

Despite the general misdirection of this article it does highlight one interesting point. In a section headed:

Superfluous updates – Oracle flaw, or Oracle feature ?

Mr. Burleson says, regarding my observation about the resources wasted by a “no-change” update:

We would have expected (at least, I did, anyway) that Oracle would check to see if a SQL update statement failed to result in any row changes.

Personally I wouldn’t expect anything, I would start by asking the question “I wonder what happens when …” and then test – because it’s very easy to test (and, of course, it’s a test I did many years ago and repeat from time to time).

But there is an important strategic discussion embedded in the approach that Oracle takes – is it sensible to update values which aren’t going to change or should Oracle check for this condition and avoid generating the undo and redo ?

I’ll leave this question, temporarily, as an exercise for the reader – and address it in a couple of days’ time.


  1. Nice question!

    Let see…I’m lazy so I think Oracle is lazy too: it does the job and doesn’t check nothing.

    It doesn’t avoid nothing: it “blindly” updates.

    I think that the check operation can be expensive…so it’s omitted.

    Do I get it? :)

    Comment by Antonio — December 29, 2006 @ 8:16 am GMT Dec 29,2006 | Reply

  2. No, I do not expect that is the answer. I am lazy and oracle is lazy. At the moment of update, the original and the new value are both available. Comparing the two is not expensive. Saving redo etc. is a big win. If the question is on a per row basis, one should check.
    But in a wel designed system, 99% or more of the updates are real updates. Checking all of them to avoid the occasional superfluous is redo etc. is expensive.

    And I can not calculate the consequences of not doing an update (and thus not producing the audittrail of redo etc. that comes with it) if oracle changes this behavior. And what about triggers that are supposed to fire when a update occurs?

    When I (the developer) says: Update, oracle should update. I do not like systems that know better what is good for me then I do myself. I am not convinced either way what is best, but changing it should be done very carefully.

    It is not like not giving rows back in an unsorted order after a group by where the order by is missing. There I (the developer) did not say what I wanted, so oracle is not responsible for not meeting my expectations.

    It makes lazy developers, and I am lazy enough as it is.

    Comment by Maarten Vinkhuyzen — December 29, 2006 @ 10:26 am GMT Dec 29,2006 | Reply

  3. Maarten mine was a joke…

    But…well…we are saying the same more or less! :)

    Think about this:

    update table
    set col1=val1, col2=val2 (where…)

    When can we say “it’s not a real update!”?
    When current col1 equal val1? or col2=val2? or both?

    I don’t understand your phrase about group by and order: when you make a group by you “always” make an order by (please note the ” around always).

    I believe that it’s better to think to Oracle in the simplest possible way!
    If we think to the hypothetical algorithm used by Oracle, it would be something like:

    1) get the row
    2) get the columns in the “set-part” or the update
    3) for every column int he set-part check
    4) if current_value!=new_value then
    5) generate undo/redo (generate FGA, audit…what else?)
    6) change the value
    7) end if
    8) end for

    hum…it seems quite expensive!
    Not only! you say that “99% are real update”…so statistically 99% of the time this algorithm is a wast of time! (Sure we can provide the 1% example…but if you are designing your product for speed…you’ll look at 99% don’t you?)
    And in the end…who is making some real test about this “talk”?
    Me not…you know I’m lazy! :)

    Comment by Antonio — December 29, 2006 @ 1:26 pm GMT Dec 29,2006 | Reply

  4. Antonio,

    The most part of you hypothetical algorithm must always be done.
    Oracle must know the current values before doing an update
    Only check if the old/new values differ would be new

    And shouldn’t the auditing/fga be done regardless of the update was a “real” update or not?



    Comment by Freek — December 29, 2006 @ 2:14 pm GMT Dec 29,2006 | Reply

  5. The most part of your hypothetical algorithm…

    That’s the point Freek! :)

    So the question: if most part of the algorithm must always be done, why should I add extra check only to cope with the 1% of “real life”?
    It’s only work that I add with no benefit!

    You are right about the FGA etc…

    I want to point out one thing: the complexity of the algorithm is its number of compares.
    if you eliminate step 4, the algorithm is “lighter” and everything works! Additionally you don’t need the loop.
    So why check?

    hum…It seems to me that we are “Missing the Point” of the post! :)
    Shame on me! It’s my fault! 8)

    And yes! I love smiley! 8)

    Comment by Antonio — December 29, 2006 @ 3:07 pm GMT Dec 29,2006 | Reply

  6. Offhand, it should be easy to make the optimization unless table triggers are involved–that’s the only case where I can see anybody wanting to actually write identical information to a table.

    Of course, adding “where (x is null or x != 0)” might allow other access paths to get at the data in question, possibly speeding things up tremendously. Though there are FGA concerns there, as noted earlier.

    Comment by Jason Bucata — December 29, 2006 @ 7:16 pm GMT Dec 29,2006 | Reply

      T       NUMBER                                    NULL,
      MUTDAT  DATE                                      NULL
       :NEW.mutDat := SYSDATE;
    END t_mut_trg;
       FOR r IN 1 .. 100 LOOP
          INSERT INTO rob.t
               VALUES (10
       END LOOP;

    Now we have table T with 100 values of 10.

    UPDATE t SET t = 10;

    Check value of mutdat: let’s say this it time T1.

    Wait 1 minute, then:
    Again update:
    UPDATE t SET t = 10;

    Check value of mutdat : See, it’s different from T1 in all rows.

    (Very carefull) conclusion: Blind update of every row, no checking if it was necessary. (oracle 10GR2)


    Rob Zijlstra

    Comment by Rob Zijlstra — December 29, 2006 @ 7:17 pm GMT Dec 29,2006 | Reply

  8. — On an otherwise idle system

    SQL>create table rjq as (select 1 val from all_objects);

    Table created.

    SQL>select sum(used_ublk)/8 "Undo In Use (Kbs)" from v$transaction;

    Undo In Use (Kbs)

    SQL>select distinct val from rjq;


    SQL>select sum(used_ublk)/8 "Undo In Use (Kbs)" from v$transaction;

    Undo In Use (Kbs)

    SQL>update rjq set val=1;

    63396 rows updated.

    SQL>select sum(used_ublk)/8 "Undo In Use (Kbs)" from v$transaction;

    Undo In Use (Kbs)


    Rollback complete.

    SQL>select sum(used_ublk)/8 "Undo In Use (Kbs)" from v$transaction;

    Undo In Use (Kbs)

    SQL>update ops$oracle.rjq set val=2;

    63396 rows updated.

    SQL>select sum(used_ublk)/8 "Undo In Use (Kbs)" from v$transaction;

    Undo In Use (Kbs)

    Comment by richardjq — December 29, 2006 @ 8:38 pm GMT Dec 29,2006 | Reply

  9. So Oracle is lazy as I am! :)

    What a nice life! LOL

    A bit more seriously: for me it’s clear that Oracle don’t check nothing. It’s too expensive and too dangerous!
    How many examples can we made of an update statement?
    I don’t feel comfortable with the idea of “introduce a check that is fired only in some cases”. Not a good idea.

    Thanks to Rob Zijlstra and richardjq for giving a real example. Mine was only “smoke”! :)

    Comment by Antonio — December 29, 2006 @ 9:05 pm GMT Dec 29,2006 | Reply

  10. Anybody consider that the answer could depend on if the change is being done to a table vs. an index? Of course, I should test this before commmenting, but how much fun would that be?

    Comment by Roderick — December 29, 2006 @ 11:26 pm GMT Dec 29,2006 | Reply

  11. If I’ve told Oracle to do the update irrespective of whether the data changes, then it should. I want to see evidence of that in SQL%ROWCOUNT (I might be checking that against the number of records I expect to be updating), in the triggers firing and everywhere else. Most of all I don’t want them being ‘intelligent’ in one version and not generating redo, and then having to dumb it down in a later version because they’ve added asynchronous change data capture or some other new feature.

    Comment by Gary — January 1, 2007 @ 3:47 am GMT Jan 1,2007 | Reply

  12. […] Filed under: Infrastructure, Performance — Jonathan Lewis @ 9:50 pm UTC Jan 2,2007 In a recent article (which has prompted several excellent comments) I highlighted the suggestion raised by Don Burleson […]

    Pingback by Superfluous Updates ? « Oracle Scratchpad — January 2, 2007 @ 10:20 pm GMT Jan 2,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by