Friday 18 May 2012

How to find the redo genrated by DML,DDL



Sometime want to know redo generated by at any transaction  in oracle . We can simply use "  set autotrace on statistics"  



SQL> set autotrace on statistics
SQL> insert into  object1  select * from all_objects;
49951 rows created.
Statistics
----------------------------------------------------------
       6693  recursive calls
       3866  db block gets
      38572  consistent gets
        263  physical reads
    5600712  redo size
        675  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
      49951  rows processed


But in few cases create like create Index we can find redo using below quires 


SQL> set verify off
SQL> column value new_val V
SQL> select a.name ,b.value value from v$statname a, v$mystat b  where a.statistic#=b.statistic# and lower(a.name) like '%redo size%' ;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 185164

SQL> create table object3 as select * from dba_objects;

Table created.


SQL> select a.name , b.value ,b.value-&V redogenerated   from v$statname a, v$mystat b where a.statistic#=b.statistic#  and lower(a.name) like '%redo size%';

NAME                         VALUE                     REDOGENERATED  
--------                        ---------                       --------------------
redo size                       239880                        54716

SQL> set verify off
SQL> column value new_val V
SQL> select a.name ,b.value value from v$statname a, v$mystat b
2 where a.statistic#=b.statistic# and lower(a.name) like '%redo size%' ;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 2323044

SQL> create index obind1 on object3(object_type);

Index created.

SQL> select a.name , b.value ,b.value-&V redogenerated
2 from v$statname a, v$mystat b where a.statistic#=b.statistic#
3 and lower(a.name) like '%redo size%';

NAME                  VALUE                   REDOGENERATED    
--------                  ---------                  ------------
   redo size             3503840                   1180796


No comments: