Measuring the amount of log generated by a statement

If you want to measure the amount of log generated by an SQL statement in Oracle and DB2, you can use the following commands:

In Oracle, you can:
SQL>set autotrace on:
SQL>Run your insert/update/delete statement

Check for the entry "redo size" in the autotrace output. For example:
Statistics
----------------------------------------------------------
12975 recursive calls
1798143 db block gets
56538 consistent gets
9882 physical reads
125409912 redo size
1015 bytes sent via SQL*Net to client
874 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
297585 rows processed

That tells you how many bytes of redo were generated by the DML. As an aside, sometimes you can get non-zero redo for a SELECT also due to delayed block cleanout.

In DB2, you need to first update the monitor switch for UOW to ON;
db2=> update monitor switches using UOW on
db2=> Run your DML statements
db2=> get snapshot for applications on
You will get a detailed output, look for an entry saying "UOW log space used (bytes) ". If you want to repeat this exercise several times, you can either use the RESET MONITOR SWITCHES command between invocations or can just disconnect and reconnect because the snapshot information is for the application setting the switch.