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

hansdegit_at_hotmail.com wrote:
> hansdegit_at_hotmail.com schreef:
>
> Well guys (and girls), strange as it is: now that the IO system has
> been configured properly, the same batch ( I repeat: the exact same
> batch) runs in 50 minutes and consumes less than 4 gigs of undo space.
>
> My theory is: Oracle will use not reuse undo space when there is space
> available. Also, when IO due to cleaning up of undo space doesn't get
> through or simply is not performed fast enough, Oracle will postpone
> these activities until some time in the future.
>
> While I write this down, I'm having more difficulties believing this.
>
> Open invitation on the real guru's: please explain how this mechanism
> works.

It has been awhile since I dove under the covers of AUM, but I'll see what I can remember and what I can theorize.

The space management algorithm for AUM is to use available unallocated space before reusing space from it's own segment or another segment (extent stealing). This is one reason why autoextend on UNDO tablespace datafiles and a high undo_retention setting can be 'dangerous'.

SMON is responsible for cleaning up the UNDO tablespace, offlining segments that are not needed, updating expiry tables (internal structures), etc. It has been the contention of several reasonably smart people that on some systems SMON gets bogged down with all of it's chores and can't keep up. I've personally never seen an I/O subsystem issue cause this, but it is a possibility.

I'm not sure I would use the term postpone, though. I think that SMON does part of the work, then has to do some other work. Eventually it gets back to the original work and completes it. This probably happens within seconds or minutes. Unfortunately, more work keeps piling up...

I think there is also a bug where SMON is cleaning up a segment at the same time a user session is assigned to it. At this point, it goes 'pear shaped' to quote a friend, and may crash the instance. I can't recall the exact bug, though.

AUM introduces additional work and complexity for SMON. It seems when SMON is stressed with other operations, AUM can become very problematic.

Cheers,
Dan Received on Wed Aug 17 2005 - 15:23:23 CDT