Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: What is wrong with this rollback segment stmt ?

Re: What is wrong with this rollback segment stmt ?

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: 1997/02/09
Message-ID: <995bbCAGab$yEw9J@jimsmith.demon.co.uk>#1/1

In article <aak2.855479226_at_Isis.MsState.Edu>, Atif Ahmad Khan <aak2_at_Ra.MsState.Edu> writes

You haven't done much wrong except possibly misunderstand the use of optimal.

Optimal is effectively a minimum size (you can start smaller, but once it goes above optimal it never goes below). There is no guarantee that a rollback segment will shrink back to optimal at any particular time, so it would be relatively easy for your large transactions each to use a different rollback segment and extend it up to its maximum size (200Mb) giving a total of 800Mb in a 200 Mb tablespace. Good trick if you can manage it! Even if 3 are at optimal and one is at maximum that is 275Mb.

Rollback segments are shrunk back to optimal when another transaction tries to use it. If enough blocks are free it will be shrunk otherwise not. It you run 4 transctions one after the other, they are likely to be allocated to four different segments and therefore not trigger the above behaviour.

>
>I was having rollback segment blues. I wasn't sure what was wrong.
>So I did the following :
>
>// Dropped the rollback segment tablespace after taking rollback segments
>// offline.
>drop tablespace RBS including contents ;
>
>// Then created another one.
>create tablespace RBS datafile 'rbsWG73' size 110M autoextend on next 1M
>maxsize 200M ;
>
>// Created the four rollback segments and brought them back online.
>create public rollback segment r01 tablespace RBS storage
>(initial 500K NEXT 500K OPTIMAL 25M MINEXTENTS 50 MAXEXTENTS 400);
>alter rollback segment r01 online ;
>.
>.
>
>After a run few large transactions, the tablespace gets full and I start
>getting errors related to space problems on the system tablespace.
>I thought I would never run into problems with the above setup. The 4
>rollback segments have an optimal size of 25M. The tablespace should
>never be more than 55% full. I checked available space on tablespace RBS
>then and it had .14% available. Now, after several hours it has about
>50.2% available. Could anybody please point to what I did wrong ?
>
>Thanks.
>
>P.S. My system is Solaris X86 2.5.1. Oracle 7.3.2.2.0.
>
>Atif Khan
>aak2_at_ra.msstate.edu
 

-- 
Jim Smith
Received on Sun Feb 09 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US