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

That block failed, but that's OK—we knew it would since there is no data in the table yet. I ran that block simply
to get the hard parse of the SQL and PL/SQL performed so when we run it later, we won't have to worry about side
effects from hard parsing being counted. Now we are ready to load the data into our table and commit:
EODA@ORA12CR1> insert into t
2 select rownum, 'x', 'y', 'z'
3 from all_objects
4 where rownum <= 10000;
10000 rows created.
EODA@ORA12CR1> commit;
Commit complete.
And, finally, I'm ready to measure the amount of redo generated during the first read of the data:
EODA@ORA12CR1> variable redo number
EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> declare
2 l_rec t%rowtype;
3 begin
4 for i in 1 .. 10000
5 loop
6 select * into l_rec from t where id=i;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo)
|| ' bytes of redo generated...');
802632 bytes of redo generated...
PL/SQL procedure successfully completed.
So, this SELECT generated about 802KB of redo during its processing. This represents the block headers it
modified during the index read of the primary key index and the subsequent table read of T. DBWn will be writing
these modified blocks back out to disk at some point in the future (actually, since the table doesn't fit into the cache,
we know that DBWn has already written out at least some of them). Now, if I run the query again
EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.