A Couple Of Interesting Jonathan Lewis Articles
October 10th, 2004 by Mark Rittman
A couple of good articles by Jonathan
Lewis. "How much
does an update cost ?" looks at what the true cost of an update is when it
just sets a column to the same value. This often happens when a generic bit of
application code updates all of the columns in a set of tables, rather than just
updating those that have new or changed data. The answer is that these ‘across
the board’ updates can generate a significant amount of overhead, and in many
cases it’s worth taking the time to alter your code to only apply updates to the
columns that have changed.
"Oracle 10g fills a
trouble-shooting gap" looks at changes to the way CREATE TABLE … AS SELECT
statements are handled with Oracle 10g. Earlier versions of Oracle didn’t
consider the SQL used to build tables or indexes shareable (unless they were
built in parallel) and therefore the SQL used to build these never showed up in
the V$SQL view. With 10g, however, it is considered shareable, which means all
of your CREATE TABLE AS … SELECT statements can now turn up in your ‘Top Ten
SQL’ reports. Jonathan speculates that this is down to the introduction of AWR
and ADDM, which needs statistics on these types of statements in order to
accurately diagnose and tune the database.
