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

On 14 Aug 2005 09:07:16 -0700, "hansdegit_at_hotmail.com" <hansdegit_at_hotmail.com> wrote:

>
>Sybrand Bakker schreef:
>
>> You need to use v$waitstat to monitor what your process is waiting
>> for.
>I have never used v$waitstat to see what is going on with undo
>segments, so please clarify.
>
>> Provided you didn't set up dictionary managed tablespaces your
>> assumption is highly unlikely and probably just a shot in the dark.
>Yes, it was a shot in the dark, therefore I asked for the internals of
>undo segment handling, to get get a better understanding of what was
>going on, an explanation of the difference between the bytes reported
>in v$rollstat and dba_segments.
>
>> Other than that, one could question why you don't merge the insert and
>> update into one statement, assuming the *real* update statement (which
>> you obviously didn't disclose) is as simple as the pseudo statement
>> you provided.
>Thanks for the hint, but I know how to work around this problem.
>Indeed, the *real* update is not as simple.

First of all: I don't agree with your analysis the undo segments are the cause of your problem.
The only way you can tell that is by querying v$waitstat, which will you show you what the session is waiting for. Might be undo segment, but might be not. Basically your claim is unsubstantiated.

Secondly using v$waitstat you would see whether your session is waiting for undo segment headers and/or undo segment blocks.

Thirdly: undo segments were designed to work like temporary segments. Ie they never shrink. This is to be expected as Oracle and several Oracle gurus have been advising on not using the optimal clause of the rollback segment for many years, for the reason extent allocation and deallocation is an expensive operation. As disk is cheap nowadays, the space allocated usually shouldn't be a problem.

Fourthly: your transaction may show an inefficient execution path, or (implicitly) modify many, many indexes. As you are pretty scarce on details, crystall balls would be needed to determine why you transaction is 40G. Oracle however, will online additional undo segments as needed, and those actions are being logged in the alert log.

Your query about the relationship between v$rollstat and dba_segments can, as you didn't provide any specifics, not be answered.

Finally: my impression is you set up undo segments and forget about them. Any attempt to try to be 'smarter' than Oracle is probably likely to succeed in some circumstances, and fail in other.

--
Sybrand Bakker, Senior Oracle DBA