Investigating Undo - Oracle Database Transactions and Locking Revealed - page 153

4 from all_objects a;
Table created.

EODA@ORA12CR1> alter table big add constraint big_pk
2 primary key(object_id);
Table altered.

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'BIG' );
PL/SQL procedure successfully completed.

■ You might wonder why I didn't use CASCADE=>TRUE on the gather-statistics call to gather statistics on the index
created by default by the primary key constraint. that is because since oracle 10 g , a CREATE INDEX or ALTER INDEX
REBUILD has implicit compute statistics added to it already whenever the table it is indexing is not empty. so, the very
act of creating the index has the side effect of gathering statistics on itself. there's no need to regather the statistics we
already have.

Note

The previous table will have lots of blocks as we get about six or seven rows per block using that big data field, and
my ALL_OBJECTS table has over 70,000 rows. Next, we'll create the small table the many little transactions will modify:

EODA@ORA12CR1> create table small ( x int, y char(500) );
Table created.

EODA@ORA12CR1> insert into small select rownum, 'x' from all_users;
25 rows created.

EODA@ORA12CR1> commit;
Commit complete.

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'SMALL' );
PL/SQL procedure successfully completed.

Now, we'll dirty up that big table. We have a very small undo tablespace, so we'll want to update as many blocks
of this big table as possible, all while generating the least amount of undo possible. We'll use a fancy UPDATE statement
to do that. Basically, the following subquery is finding the “first” rowid of a row on every block. That subquery will
return a rowid for every database block identifying a single row on it. We'll update that row, setting a VARCHAR2(1)
field. This will let us update all of the blocks in the table (some 8,000 plus in the example), flooding the buffer cache
with dirty blocks that will have to be written out (we have room for only 500 right now). We'll make sure we are using
that small undo tablespace as well. To accomplish this and not exceed the capacity of our undo tablespace, we'll craft
an UPDATE statement that will update just the “first row” on each block. The ROW_NUMBER() built-in analytic function is
instrumental in this operation; it assigns the number 1 to the “first row” by database block in the table, which would
be the single row on the block we would update:

EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.

EODA@ORA12CR1> update big
2 set temporary = temporary
3 where rowid in