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:
Post a Comment