Re: Space wasted because of automatic undo management

Comments embedded.
Vsevolod Afanassiev wrote:
> Let's say I am using traditional rollback segments.

Okay, 'You're using traditdional rollback segments.' Whatever causes you to do such is a mystery to me given Automatic Undo Management, to which you have access.

> For a small
> database I would create 4 segments, each 100 MB in size, and set
> OPTIMAL to 100 MB.

Bad idea, as OPTIMAL can result in ORA-01555 errors, many of them. Search the newsgroup archives for discussions on why setting OPTIMAL is a really poor choice of rollback segment management.

> 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.

If you're worried aboud disk space, at roughly $1.00 per Gigabyte for a server-quality drive, you need to reconsider your priorities. Oh, and get management so spring for sufficient disk for your application.

>
> 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.
>

Why would you saddle your application, and yourself, with manual rollback segment management? Oh, I see, you're too cheap to buy enough disk space to properly run your database ...  

> Any thoughts?
>
> Thanks
>
> Sev

David Fitzjarrell Received on Fri Apr 01 2005 - 11:16:09 CST