Re: Space wasted because of automatic undo management

On 31 Mar 2005 18:07:54 -0800, "Vsevolod Afanassiev" <vafanassiev_at_yahoo.com> wrote:

>Let's say I am using traditional rollback segments. For a small
>database I would create 4 segments, each 100 MB in size, and set
>OPTIMAL to 100 MB. All 4 segments will be in the same tablespace and in
>the same datafile, so initial datafile size will be 400 MB.
>It may grow if there is a large transaction, but eventually the
>transaction will complete, rollback segment shrink back to OPTIMAL
>size, and the datafile could be resized back to 400 MB. So the space is
>used efficiently and not wasted.
>
>It appears that with automatic undo management the space is being used
>very inefficiently. Oracle creates additional extents all over the
>place, with total disgregard for disk space. There are huge chunks of
>empty space between individual extents. I am thinking about switching
>back to traditional rollback segments.
>
>Any thoughts?
>
>Thanks
>
>Sev

Sure. setting OPTIMAL on a traditional rollback segment usually is a BIG NO NO, as it increases the chance of hitting ora-1555. As has been demonstrated here many times. With AUM you just set a parameter and be done with it, and even get your ora-1555 in the alert.
Nowadays disks are cheap, and it will be difficult to find a disk smaller than 36G.
Guess, due to your storage tactics from the 80s, your disks are largely unused, and you just keep yourself busy by reorganizing rollback segments.
Switching back to rollback segments would be sticking to antiquated technology forever.

--
Sybrand Bakker, Senior Oracle DBA