Hot backup mode and a little known fact

Most people are aware of the implication hot backup puts on your redo stream. They'll tell you that on first modification Oracle will dump the entire block image into a redo stream and subsequent writes of the block will go as usual.

This is only half of the story.

The example

Let's start by creating a simple table I'll use as an example:

SQL> create table t pctfree 99 pctused 1 as
  2     select level n
  3             from dual
  4             connect by level <= 100;

Table created.

Now, let's measure the amount of redo generated if I'm going to update all rows in the table:

SQL> set autot traceonly stat
SQL> update t set n=n;

100 rows updated.


Statistics
----------------------------------------------------------
         30  recursive calls
         20  db block gets
         44  consistent gets
         18  physical reads
      12152  redo size
        826  bytes sent via SQL*Net to client
        769  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed

That's almost 12K of redo. Let's see what happens if I put tablespace into a hot backup mode:

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> update t set n=n;

100 rows updated.


Statistics
----------------------------------------------------------
          0  recursive calls
         20  db block gets
         20  consistent gets
          0  physical reads
     152708  redo size
        830  bytes sent via SQL*Net to client
        769  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed

The amount of redo grew up to almost 150K. Oracle has to dump the entire block image into a redo stream due to fractured blocks problem. If we do the same update second time:

SQL> update t set n=n;

100 rows updated.


Statistics
----------------------------------------------------------
          0  recursive calls
         17  db block gets
         20  consistent gets
          0  physical reads
      11980  redo size
        830  bytes sent via SQL*Net to client
        769  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed

It goes as usual. In fact, and this is kind of amazing, this is what most of the sources available on the internet will tell you about the behavior: the full image of the block gets written only on first modification in order to prevent fractured blocks problem and subsequent modifications will go as usuals.

Only half of the story

Let me continue with my example:

SQL> alter system flush buffer_cache;

System altered.

SQL> update t set n=n;

100 rows updated.


Statistics
----------------------------------------------------------
          0  recursive calls
         20  db block gets
         20  consistent gets
         20  physical reads
     151916  redo size
        831  bytes sent via SQL*Net to client
        769  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed

What you see here is the entire image of the blocks written into a redo stream again.

The true story

Corrected 27-Jan-2009: initially I thought that it is a write to disk what matters, however, thanks to Jonathan Lewis for correcting me -- it's a read from disk to buffer. Please see his first comment.

Don't confuse correlation with causation.