Thursday, November 17, 2011

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

No comments: