Database Reference
In-Depth Information
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
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.
