newbie DBA - rollback segments

[Mark Perrault]

I'm new to the oracle dba roll, so please be gentle.

I load several large tables every night. My database is only about
20gB and is 24x7. I have 10 rollback segments of 100 mB each. I've
noticed that the I have a rather high amount of that space getting
used up (especially after I ran an adhoc UPDATE query on one of those
large tables). How do I clear the RBS (beyond stopping and restarting
the instance)? Is there a danger in having 75% or more utilization of
those RBS's?

Thanks,
Mark

[Sybrand Bakker]

"Mark Perrault" <mperrault@ingdirect.com> wrote in message
news:ee979b3c.0307021149.7d707a35@posting.google.c om...

> I'm new to the oracle dba roll, so please be gentle.
>
> I load several large tables every night. My database is only about
> 20gB and is 24x7. I have 10 rollback segments of 100 mB each. I've
> noticed that the I have a rather high amount of that space getting
> used up (especially after I ran an adhoc UPDATE query on one of those
> large tables). How do I clear the RBS (beyond stopping and restarting
> the instance)? Is there a danger in having 75% or more utilization of
> those RBS's?
>
> Thanks,
> Mark

You don't clear them up. If you really are afraid you are using up too much
space, or one of them is really much bigger than the others, shrink them
(alter rollback segment shrink to ...)
You don't clear them up because you need to have multiple extents
accomodating for multiple transactions.
Also, in dictionary managed tablespaces, extent allocation is expensive.
You're going to shoot yourself in your foot also because they will grow
again, when a larger transaction use the rollback segment. Once transaction
commits you shrink the segment and the whole story starts over again.
Configure them properly and leave them alone as much as possible.


--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

[Daniel Morgan]

Mark Perrault wrote:

> I'm new to the oracle dba roll, so please be gentle.
>
> I load several large tables every night. My database is only about
> 20gB and is 24x7. I have 10 rollback segments of 100 mB each. I've
> noticed that the I have a rather high amount of that space getting
> used up (especially after I ran an adhoc UPDATE query on one of those
> large tables). How do I clear the RBS (beyond stopping and restarting
> the instance)? Is there a danger in having 75% or more utilization of
> those RBS's?
>
> Thanks,
> Mark

If it ain't broke don't fix it. From what you've written everything is
fine.

You don't clear rollback segments ... you size them large enough to handle
the largest transactions (which appears to be the case) and then you leave
them alone.

In the future be sure to post hardware, O/S, version and edition. For
example if your database is 9.2 you should be using UNDO rather than
Rollback.
--
Daniel Morgan
[url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
[email]damorgan@x.washington.edu[/email]
(replace 'x' with a 'u' to reply)

[Dave Hau]

Mark Perrault wrote:

> I'm new to the oracle dba roll, so please be gentle.
>
> I load several large tables every night. My database is only about
> 20gB and is 24x7. I have 10 rollback segments of 100 mB each. I've
> noticed that the I have a rather high amount of that space getting
> used up (especially after I ran an adhoc UPDATE query on one of those
> large tables). How do I clear the RBS (beyond stopping and restarting
> the instance)? Is there a danger in having 75% or more utilization of
> those RBS's?

The rollback segment is a circular buffer. So it'll automatically
re-use its space. There is no need to "clear" it. The segment grows
usually when you have long running transactions (preventing the rollback
segment from re-using certain extents because the transaction has not
completed). But when the transaction commits, the space is available
for re-use again.

Having a large RBS is not a concern except that for optimal performance,
you would want the RBS to be just small enough to support the largest
transaction for your apps. This way, the RBS can be cached in the
buffer cache and you save disk I/O. To achieve this, set the OPTIMAL
parameter in the storage clause of the RBS. This will cause Oracle to
attempt to periodically shrink the RBS back to the optimal size you set.
If you do this, be sure to check V$ROLLSTAT regularly and adjust your
OPTIMAL parameter so that OPTSIZE is close to AVEACTIVE, and SHRINKS is
a low value.

Cheers,
Dave

>
> Thanks,
> Mark