Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Automatic fix for Superfluous overhead in Oracle?

Oracle Database Tips by Donald Burleson

In  a recent news tip on SQL tuning with STATSPACK, I noted a potential performance enhancement within Oracle where Oracle fails to check to see if an update is superfluous.

An automated fix for superfluous updates?

A fix for this problem was add a WHERE clause predicate, and this suggests the obvious question of whether Oracle could be enhanced to perform automatic DML re-write, just as Oracle has been doing automatic SQL re-write (i.e. MV, VPD). 

Some of his blog commentators agreed that a fix  to check and bypass superfluous updates would be good, while others opposed such an enhancement.  The comments also suggests that some of his readers did not fully understand his argument:
As you say, this check is dangerous staff [sic]
And yes, an ?off-switch? would be a very good idea should Oracle introduce such a smart update ?feature?.
All this seems like the check was not introduced (years ago?) because CPU was not so powerful and now it's not introduced because DISK are so fast?
David Aldridge also noted that the superfluous update overhead is even worse with compressed tables, "in the case of superfluous updates the effects really are severe in proportion to the 'superfluousness", and he notes his own superfluous update test script and results to show the overhead of Oracle superfluous updates on compressed tables.

Everyone seems to dislike the overhead of superfluous updates, but there is disagreement about how this "feature" might be fixed and whether or not a automated fix might be dangerous.  There are two approaches to remedying superfluous updates, automatic DML re-write or a block-level software solution.  Let's take a closer look at this fascinating issue.

  • An optimizer-based fix for superfluous updates - The Oracle optimizer already performs query rewrite for materialized views,  adds WHERE clause predicates for Virtual Private Database (VPD) queries, and re-writes some subqueries.  The Oracle SQL optimizer could be enhanced to protect against the unnecessary overhead of superfluous updates by negating the SET clause in the WHERE clause to bypass rows that do not need to be updated. 
  • A database-level fix for superfluous updates - I originally noted that the Oracle software might do a update validation and bypass blocks that don't require updates.  However, Lewis argues that bypassing those data blocks that don't need changing might somehow corrupt Oracle replication (Streams).

To any remote system, the supplemental logging files should look exactly the same with either solution (changing the UPDATE syntax vs. an Oracle Software change).  In sum, we need to show that suppressing superfluous updates, (either manually or via software enhancement) cannot corrupt any replication mechanism, including Streams, Multi-master replication or Data Guard. 

Remember, just one test case is all that is needed to show that a DML re-write approach would not always work.  Any software-side solution must be 100% safe, in all cases, on all releases, for each and every obscure feature . . . .

Simple logic tells us that the supplemental logging cannot hurt anything if Lewis' procedural solution (adding FLAG != 0 ) produces the exact same redo as my proposed solutions;

1 - DML re-write - This would the exact same result as a manual change to the update syntax.

2 - Change the Oracle executables - If the kernel software is enhanced to bypass redo and block update when there are no changes to the data blocks, we also get the exact same result as a manual change to the update statement syntax. 

However, just because the logging matches in this case does not mean that Lewis' argument is without merit. I know how challenging it is to create generalized decision rules for STATSPACK, (which I'm doing as part of the team on and I know that even the most brilliant Oracle expert can misinterpret the huge volumes of information within a STATSPACK report, especially when using system-wide statistics to tune an individual job. 

Laurent Schneider responds: (7/13/2007)

Take care that rows with FLAG is null will not be updated in the second case (we do not know if there is a not null constraint on FLAG).  I would write
update HISTORY
CLASS = 'X' and (FLAG != 0 or FLAG IS NULL);
one hundred rows updated.


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.