Oracle 10g : amount of redo generated for the first insert statement in a transaction

Question

Hi,

I am trying to see the amount of redo generated by different insert statements. I see that for the first insert in the transaction , the redo size is being shown as zero. The very next insert generates a redo of 2664 bytes (probably for the last two inserts). All subsequent inserts generate the expected number of redo.

The database I am using is 10.2.0.4

create table temp (
  x int, y char(1000), z date);

Table created.

set autotrace traceonly statistics;

sql> insert into temp values (1, user, sysdate );

1 row created.


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

--Showing only redo size for subsequent inserts...

sql> insert into temp values (2, user, sysdate);

1 row created.


Statistics
------------
2664  redo size

sql> insert into temp values (3, user, sysdate);

1 row created.


Statistics
----------------------------------------------------------
1300  redo size

sql> insert into temp values (4, user, sysdate);

1 row created.


Statistics
----------------------------------------------------------
1368  redo size

{code}

Can someone please explain why this happens?

Thanks,

Rajesh.

Answer 1

Reproduced on XE.

If you Google "In Memory Undo" and "Private Redo Threads" there is some discussion which might be relevant. EG this

"For "small" transactions, 10g generates private redo and doesn't apply the changes to the blocks until the commit. However the flag (x$bh.flag) has bit 3 set to 1 to show that private redo exists for the block.

When the commit occurs, the redo is applied to the block, at which point the block is marked as dirty, the private redo is then copied to the public redo buffer and LGWR is posted to write the redo to disc. (The treatment of the related undo blocks is similar)."

Answer 2

This beaviour is in fact due to the private redo mechanism as Gary pointed out. However, the changes are pushed to the public strands after a considerable amount of red is generated and not after commit.

This question has been answered on the Oracle technology forum. Please read the comments by Jonathan Lewis and Tanel Poder in the following thread. http://forums.oracle.com/forums/thread.jspa?messageID=3915905&#3915905