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

dirty blocks upon commit—most of them will not even be in the buffer cache anymore. I'll measure the amount of
redo I've generated so far, run a SELECT that will visit each block, and then measure the amount of redo that SELECT
generated.

■ In order for this example to be reproducible and predictable, you'll need to disable Sga automatic memory
management. If that is enabled, there is a chance that the database will increase the size of your buffer cache—
defeating the “math” I've worked out.

Note

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:

EODA@ORA12CR1> create table t
2 ( id number primary key,
3 x char(2000),
4 y char(2000),
5 z char(2000)
6 )
7 /

Table created.

EODA@ORA12CR1> 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 blocksize database). Next, we'll inspect the block of code we'll be
executing against this table:

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 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6