Investigating Redo - Oracle Database Transactions and Locking Revealed - page 138

I set up a small stored procedure to allow me to perform arbitrary SQL and report the amount of redo generated
by that SQL. I'll use this routine to perform INSERT s, UPDATE s, and DELETE s against both the temporary and permanent
tables:
EODA@ORA12CR1> create or replace procedure do_sql( p_sql in varchar2 )
2 as
3 l_start_redo number;
4 l_redo number;
5 begin
6 l_start_redo := get_stat_val( 'redo size' );
7
8 execute immediate p_sql;
9 commit;
10
11 l_redo := get_stat_val( 'redo size' ) - l_start_redo;
12
13 dbms_output.put_line
14 ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' ||
15 substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
16 end;
17 /
Procedure created.
Then I ran equivalent INSERT s, UPDATE s, and DELETE s against both the PERM and TEMP tables:
EODA@ORA12CR1> set serveroutput on format wrapped
EODA@ORA12CR1> begin
2 do_sql( 'insert into perm
3 select 1,1,1
4 from all_objects
5 where rownum <= 500' );
6
7 do_sql( 'insert into temp
8 select 1,1,1
9 from all_objects
10 where rownum <= 500' );
11 dbms_output.new_line;
12
13 do_sql( 'update perm set x = 2' );
14 do_sql( 'update temp set x = 2' );
15 dbms_output.new_line;
16
17 do_sql( 'delete from perm' );
18 do_sql( 'delete from temp' );
19 end;
20 /