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 www.statspackanalyzer.com)
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
SET FLAG=0
WHERE
CLASS = 'X' and (FLAG != 0 or FLAG IS NULL);
one hundred rows updated.
