Database Reference
In-Depth Information
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.
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...');
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 SELECT s—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 10 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.
Search WWH ::




Custom Search