> this work generates a "start redo, redo, end redo" sort of message.

So the overhead is about 240 bytes:
(diff in redo)/(num rows) = 7760736 / 31731 = 244.5

Is the message you are speaking of the one put in the log buffer - hence in your example we get the redo copy latch 31731 times for the 31731 single-row inserts, and only one (or probably one for each inserted block) for the bulk insert ?

Thank you Tom for your answer! Just for curiosity I did a similar test and used logminer to see what's in the redo, but I couldn't see much difference.

wzhg@9iR2> create table tredo(c1 number);

Table created.

wzhg@9iR2> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

wzhg@9iR2> select sysdate from dual;

SYSDATE
-------------------
2003-09-24 16:09:23

wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2> insert into tredo select rownum from all_objects where rownum<10;

9 rows created.

wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2> begin
2 for x in (select rownum from all_objects where rownum<10) loop
3 insert into tredo values X;
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2> commit;

Commit complete.

wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2>
wzhg@9iR2> select sysdate from dual;

SYSDATE
-------------------
2003-09-24 16:09:25


wzhg@9iR2> exec sys.dbms_logmnr.start_logmnr(-
> dictfilename=>'C: mpdictionary.ora',-
> starttime=>to_date('2003-09-24 16:09:23','yyyy-mm-dd hh24:mi:ss'),-
> endtime=>to_date('2003-09-24 16:09:25','yyyy-mm-dd hh24:mi:ss'));


wzhg@9iR2>
wzhg@9iR2> SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
2 USERNAME AS USR,
3 SQL_REDO AS SQL_REDO
4 FROM V$LOGMNR_CONTENTS
5 /
XID USR SQL_REDO
---------- ---------- ------------------------------
4.24.58514 WZHANG set transaction read write;
4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('1');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('2');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('3');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('4');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('5');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('6');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('7');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('8');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('9');

4.37.58513 WZHANG set transaction read write;
4.37.58513 WZHANG insert into "SYS"."IDL_SB4$"("
OBJ#","PART","VERSION","PIECE#
","LENGTH","PIECE") values ('3
3244','0','153092096','0','14'
,Unsupported Type);

4.37.58513 WZHANG
4.37.58513 WZHANG insert into "SYS"."IDL_SB4$"("
OBJ#","PART","VERSION","PIECE#
","LENGTH","PIECE") values ('3
3244','0','153092096','1','7',
Unsupported Type);

4.37.58513 WZHANG
4.37.58513 WZHANG insert into "SYS"."IDL_UB1$"("
OBJ#","PART","VERSION","PIECE#
","LENGTH","PIECE") values ('3
3244','0','153092096','2','3',
HEXTORAW('000000'));

4.37.58513 WZHANG
4.37.58513 WZHANG insert into "SYS"."IDL_CHAR$"(
"OBJ#","PART","VERSION","PIECE
#","LENGTH","PIECE") values ('
33244','0','153092096','3','43
','"DEFINE"TREDO"C1"NUMBER"ORG
ANIZATION"HEAP""');

4.37.58513 WZHANG
4.37.58513 WZHANG insert into "SYS"."IDL_UB2$"("
OBJ#","PART","VERSION","PIECE#
","LENGTH","PIECE") values ('3
3244','0','153092096','4','143
',Unsupported Type);

4.37.58513 WZHANG
4.37.58513 WZHANG insert into "SYS"."IDL_UB2$"("
OBJ#","PART","VERSION","PIECE#
","LENGTH","PIECE") values ('3
3244','0','153092096','5','14'
,Unsupported Type);

4.37.58513 WZHANG
4.37.58513 WZHANG update "SYS"."OBJ$" set "OBJ#"
= '33244', "DATAOBJ#" = '3324
4', "TYPE#" = '2', "CTIME" = T
O_DATE('2003-09-24 16:39:53',
'yyyy-mm-dd hh24:mi:ss'), "MTI
ME" = TO_DATE('2003-09-24 16:3
9:53', 'yyyy-mm-dd hh24:mi:ss'
), "STIME" = TO_DATE('2003-09-
24 16:39:53', 'yyyy-mm-dd hh24
:mi:ss'), "STATUS" = '1', "FLA
GS" = '0', "OID$" = NULL, "SPA
RE1" = '6', "SPARE2" = '1' whe
re "OBJ#" = '33244' and "DATAO
BJ#" = '33244' and "TYPE#" = '
2' and "CTIME" = TO_DATE('2003
-09-24 16:39:53', 'yyyy-mm-dd
hh24:mi:ss') and "MTIME" = TO_
DATE('2003-09-24 16:39:53', 'y
yyy-mm-dd hh24:mi:ss') and "ST
IME" = TO_DATE('2003-09-24 16:
39:53', 'yyyy-mm-dd hh24:mi:ss
') and "STATUS" = '1' and "FLA
GS" = '0' and "OID$" IS NULL a
nd "SPARE1" = '6' and "SPARE2"
= '1' and ROWID = 'AAAAASAABA
AAHYFAA9';

4.37.58513 WZHANG commit;
4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('1');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('2');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('3');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('4');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('5');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('6');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('7');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('8');

4.24.58514 WZHANG insert into "WZHANG"."TREDO"("
C1") values ('9');

4.24.58514 WZHANG commit;

35 rows selected.

Dear Tom
<Asktom>
"you would have to actually DUMP the redo to trace files and inspect that".
</Asktom>

How do I do that? Could you please tell me the command or way to do it?

Hey Tom

We could not thank you enough for the most useful site on planet. I had a couple of queries

We are trying to implement an out of the box replication from Oracle to some other custom databases. We tried Logminer to achieve that however As we cannot differentiate the REDO of UNDO from normal data to be applied, we apply it; this accounts for ALL differences of data in Oracle. Somehow we some 'extras' in SQL_REDO which shouldnt be there, is this a bug, intentional...or we are missing something?

2- Does Logminer in 9i Rel-1 and Rel-2 also has same chained rows problem as in 8i (it didnt use to show redo/undo for chained rows)

3- Are streams available only for Rel-2 9i or previous releases too

Thanks for yor answers Tom, One question was still left..I saw that 8i had a problems where REDO/UNDO for single chained row was not visible in Logminer, is that fixed in (9i Rel-2 & Rel-1) ?