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.
