Surprisingly to many people, the SELECT will have generated redo. Not only that, but it will also have “dirtied” these modified blocks, causing DBWn to write them again. This is due to the block cleanout. Next, I’ll run the SELECT to visit every block once again and see that no redo is generated. This is expected, as the blocks are all “clean” at this point. We’ll start by creating our table:

$ sqlplus eoda/foo@PDB1
SQL> create table t( id number primary key, x char(2000), y char(2000), z char(2000));Table created.
SQL> exec dbms_stats.set_table_stats( user, ‘T’, numrows=>10000, numblks=>10000 );
PL/SQL procedure successfully completed.

I used DBMS_STATS to set table statistics so as to avoid any side effects from hard parsing later (Oracle tends to scan objects that have no statistics during a hard parse, and this side effect would interfere with my example!). So, this is my table with one row per block (in my 8KB block size database). Next, we’ll inspect the block of code we’ll be executing against this table:

SQL> declarel_rec t%rowtype;begin for i in 1 .. 10000 loopselect * into l_rec from t where id=i; end loop;end;/
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

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:

SQL> insert into tselect rownum, ‘x’, ‘y’, ‘z’ from all_objects where rownum <= 10000;10000 rows created. SQL> commit;Commit complete.
And, finally, I’m ready to measure the amount of redo generated during the first read of the data:
SQL> variable redo number
SQL> exec :redo := get_stat_val( ‘redo size’ );
PL/SQL procedure successfully completed.
SQL> declare l_rec t%rowtype;begin for i in 1 .. 10000 loopselect * into l_rec from t where id=i; end loop;end;/
PL/SQL procedure successfully completed.
SQL> 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

SQL> exec :redo := get_stat_val( ‘redo size’ ); PL/SQL procedure successfully completed.
SQL> declare l_rec t%rowtype;begin for i in 1 .. 10000 loopselect * into l_rec from t where id=i; end loop;end;/
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( (get_stat_val(‘redo size’)-:redo)
|| ‘ bytes of redo generated…’);0 bytes of redo generated…
PL/SQL procedure successfully completed.
I see that no redo is generated—the blocks are all clean.

If we were to rerun the preceding example with the buffer cache set to hold a little more than 100,000 blocks, we’d find that we generate little to no redo on any of the SELECTs—we will not have to clean dirty blocks during either of our SELECT statements. This is because the10,000-plus (remember the index was modified as well) blocks we modified fit comfortably into ten percent of our buffer cache, and we are the only users. There is no one else mucking around with the data, and no one else is causing our data to be flushed to disk or accessing those blocks. In a live system, it would be normal for at least some of the blocks to not be cleaned out sometimes.