Database Reference
In-Depth Information
To see this, we will create many blocks in a table that need to be cleaned out. We will then open a cursor on that
table and allow many small transactions to take place against some other table—not the table we just updated and
opened the cursor on. Finally, we will attempt to fetch the data for the cursor. Now, we
know
that the data required by
the cursor will be “OK”—we should be able to see all of it since the modifications to the table would have taken place
and been committed
before
we open the cursor. When we get an
ORA-01555
error this time, it will be because of the
previously described issue with delayed block cleanout. To set up for this example, we'll use
•
The 4MB
UNDO_SMALL
undo tablespace.
A 16MB buffer cache, which is enough to hold about 2,000 blocks. This is so we can get some
•
dirty blocks flushed to disk to observe this phenomenon.
Before we start, we'll create the undo tablespace and the “big” table we'll be querying:
EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/tmp/undo.dbf' size 4m
3 autoextend off
4 /
Tablespace created.
EODA@ORA12CR1> create table big
2 as
3 select a.*, rpad('*',1000,'*') data
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.
