Re: Rollback Segment

From: Noah Monsey <noah_at_indirect.com>
Date: 1995/05/20
Message-ID: <D8vr0I.H8s_at_indirect.com>#1/1

mah_at_reaper.mitre.org (Michael Huneycutt) wrote:

>Phuong Quach (quach_at_ext423.artecon.com) wrote:
>: Hi all,
>: Thanks in advance for all answers for the below questions:
>:
>: When do I need to create rollback segment? (using CREATE ROLLBACK..)
>: When do I need to add more datafiles to the rollback tablespace (using ALTER TABLESPACE)?
> You would only need to add a datafile if when users running large updates
> receive the stating that oracle failed to allocate extent of size #### in
> tablespace {rollback tablespace name}. This could be either because your
> extent sizes do not fit evenly in the tablespace or you truely have run
> out of space. You can query v$rollstat to see how you rollback segments
> are doing.
>: In what circumstances the rollback segment can slow down the performance?
> Doing the query above can help in tracking performance. Basically for
> large update and such, the rollback segment and extent size should be large.
> An example I had a customer who was doing massive update to a 120GB table
> and the process was taking 3 hours. Their initial and next extent size
> on their large rollback segment was 1MB or less. I changed it to 10MB and
> all of their process were 45% faster. That above process started finishing
> in 50 minutes. Figure that the more times the rollback segment has to
> extent itself the slower the process.
>

I wouldn't recomend waiting for an error before doing something. Estimate how many segments you need and how big they should be before you get an error.

Some of the other things that I recommend:

Try and make the "initial extent" large enough so that you don't end up with a highly fragmented tablespace.

Another thing that you should do is create a seperate tablespace for rollback segments,

When your tablespace gets fragmented, you can just drop it and create a new one. I used to use a script to drop the rollback segments, drop the tablespace, and create a new tablespace then create new rollback segments.

Another example of when a rollback segment will affect performance, is when you do not have enough segments for all of your users. From the v6 DBA guide "Multiple rollback segments are required when: the database accessed by many simultaneous users."

You asked: When do I need to create a rollback segment? In version 6 of the rdbms you had to create additional rollback segments before you could use other tablespaces. I don't have much experience with Oracle7 but I think it has the same restriction.

Whenever I used to create new databases, I would immediately create tablespace for temporary objects, and rollback segments, and user data. That way the system tablespace had less chance of becoming corrupted or fragmented. The next thing I would do is create 8 rollback segments just to be safe.
========= ========= ========= ========= =========

Noah Monsey                       noah_at_indirect.com

========= ========= ========= ========= ========= The only dumb question is the one that you don't ask. ========= ========= ========= ========= ========= Oracle Master Database Administration April 1992 Oracle Master Application Development April 1992 ========= ========= ========= ========= ========= Received on Sat May 20 1995 - 00:00:00 CEST