Thursday, January 15, 2009

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.

3 comments:

  1. Alex,

    It's not the write to disk that matters, it's the read from disk to buffer.

    The full block is copied into the redo buffer on the first change after the block has been read from disk. (If you update just one row, you'll see you get two change records - the first is the block, the second is the normal redo record for the update).

    If you check x$bh.flag you can see that bit 25 (redo_since_read) gets set on this first change, and the flag can't persist if the block gets flushed from memory.

    Regards
    Jonathan Lewis

    ReplyDelete
  2. Jonathan,

    thanks for the correction... you're right.

    What you say can be confirmed by ding an "alter system checkpoint" (and making sure blocks are no longer dirty using v$bh/x$bh) as well.

    I'll make an update to the post and reference your comment, thanks again.

    ReplyDelete
  3. hello, thanks for the help, the truth to my schedule I have trouble in databases, told me that there is programming style called extreme programing, I believe that each person develops his style, but I wonder if this exists? thanks ..

    ReplyDelete