Re: Rollback Segments !!!

Answers embedded in your previous post
Hth,

Sybrand Bakker, Oracle DBA

jricardo_at_ipb.pt wrote:

> Hi there...
>
> I have a admin problem under NT 4.0 and Oracle Server 7.3.1 When running a
> plsql script to update a table that have almost 500 thousand lines, i get a
> rollback segment "explosion" !!! I can change the size of the rollback
> segment, the max segments, etc... BUT i just want to know if there's no
> performance change by havong RB Segment with a lot of extents... Can anyone
> clarify me the use of RBS and the OPTIMAL size of RBS and OPTIMAL number of
> segments !!!

The rollback segment is being used to be able to restore a (set of ) table(s) to their previous state.
The rollback segment is also used to guarantee read consistency. This will use space in a rollback segment, however this space is NOT marked as an active transaction.

Not necessarily, however most likely, every new transaction will start in a different extent of the rollback segment. As soon as an extent is filled a new extent will be allocated. A transaction will never overwrite read consistency data,as soon as this happen the process issuing the select (read consistency information is only generated with selects) will receive the famous ora-1555 snapshot too old error message. This means your rollback segment is too small.

Optimal number of segments: roughly the number of concurrent transactions divided by 4 rounded up to the next multiple of 4, minimal 4, maximum 50. (Oracle will allow you to run with less than 4 but you will run into problems) NB don't forget to leave that second rollback segment in the system tablespace you created when you started to add tablespaces (if you have a pre-seeded database it won't be there, create one). Leave it offline, it will come in handy during maintenance work.

OPTIMAL size of rollback segment: this is far more difficult to tell. Frequent extent allocation and deallocation should be avoided at all cost, as this is a very resource consuming operation. You should avoid having the database shrink the rollback segment frequently, in that case you can better leave the optimal parameter alone for a while and shrink it manually (alter rollback segment shrink).

> Can the ROLLBACK segment be AUTOmatically shrinked if there's no
> space allocated by and sql or pl instance ??? For example if a RBS has 5 MB
> and it growns to 25 MB under a script execution, can it be always shrinked to
> 5MB after the execution ???
>

> If you have an optimal parameter the rollback segment will shrink
> automatically, as soon as there are no outstanding transactions. This is the
> very purpose of the optimal parameter.

Many people have large rollback segments for transactions they know to be big. It is possible to explicitly assign a transaction to a rollback segment (set transaction use rollback segment rbsbig) and doing so, avoid to explode your 'normal' rollback segments.
>From what you describe, it looks like this is a pl/sql procedure. You may want to
split up your transaction into smaller units by committing more frequently.

>
>
> --
> Thankx
> If you know how to help me mail me to jricardo_at_ipb.pt
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Tue Jan 05 1999 - 13:17:18 CST