A Couple Of Interesting Jonathan Lewis Articles

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.