REDO AND UNDO in Objective-C

Creator Data Matrix in Objective-C REDO AND UNDO

Surprisingly to many people, the SELECT will have generated redo. Not only that, but it will also have dirtied these modified blocks, causing DBWR 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: ops$tkyte%ORA11GR2> create table t 2 ( id number primary key, 3 x char(2000), 4 y char(2000), 5 z char(2000) 6 ) 7 / Table created. ops$tkyte%ORA11GR2> 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: ops$tkyte%ORA11GR2> 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 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: ops$tkyte%ORA11GR2> insert into t 2 select rownum, 'x', 'y', 'z' 3 from all_objects 4 where rownum <= 10000; 10000 rows created. ops$tkyte%ORA11GR2> commit; Commit complete.

And, finally, I m ready to measure the amount of redo generated during the first read of the data: ops$tkyte%ORA11GR2> variable redo number ops$tkyte%ORA11GR2> exec :redo := get_stat_val( 'redo size' ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> 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. ops$tkyte%ORA11GR2> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated...'); 722048 bytes of redo generated... PL/SQL procedure successfully completed. So, this SELECT generated about 722KB 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. DBWR 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 DBWR has already written out at least some of them!). Now, if I run the query again ops$tkyte%ORA11GR2> exec :redo := get_stat_val( 'redo size' ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> 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. ops$tkyte%ORA11GR2> 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 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