undo tablespace keeps growing, why can't I reclaim space?

Hi,

ora 9.2.06, AIX 5.2 ML06, IBM Power5, 1 CPU, EMC storage

We have a transaction which consumes about 2-3 gigs undo space in our test environment. When we run the batch in production, the transaction was pretty hungry for undo space; it needed more than 40 gigs...Strange indeed. I checked v$rollstat during the transaction, and the largest undo segment never grew beyond 3 gigagytes, just as I expected. Why the continuing claims for more undo??

The job consists of the following building blocks: (sort of a destructive load)
truncate target_table
insert into target_table (select * from source_join /* (without append hints)*/
commit;
update target_table set column_x where column_y = some_value; commit;

I noticed that the IO operations were very slow on the machine (the infamous EAGAIN message "performance degradation may be seen" showed up in lgwr and dbw0 traces).

Now, here's my question: could the slow i/o have something to do with the continuing claims for new undo space in the undo tablespace? Could it be that, due to the slow io, SMON just could not find the time to tidy up a bit?

Now that the IO issue is fixed (we set up Concurrent IO), I have to be sure that this problem doesn't show up again.

BTW: undo_retention = 1200. I'm pretty sure this is irrelevant,though.

Any thoughts on this would be very much appreciated.

I know the tricks and techniques to limit the amount of undo and/or redo generation. The goal of this question is to gather more wisdom about transaction processing and the decisions made by Oracle in the process of freeing undo space.

Thanks in advance. Received on Sat Aug 13 2005 - 15:40:39 CDT