How much Redo-Log does a Transaction Generates

Oracle Dictionary is a wonderful Pool of many hidden resorces. In the Oracle System Statistic Table V$SYSSTAT you can find the Number of blocks written to the Redo. This can be found in the Attribute STATISTIC# = 71. If you measure this value before and after the transaction, you can approximately find out how much Redo-Log the transaction generates. Of course this is approximately, because there may exist other transactions at the same time.

SQL> select * from v$sysstat where statistic#=71;

STATISTIC#  NAME

----------  ----------------------------------------

71               physical writes direct

Example

SQL> select value from v$sysstat where statistic#=71;

    VALUE

---------

      436

SQL> create table myhelp as select * from system.help;

Table created.

SQL> select value from v$sysstat where statistic#=71;

    VALUE

---------

      20000

So the bytes of logs generated by the specified query would be

(20000-436)/512 = 38.2109375 BYTES