Re: Autoextend for rollback

On Fri, 18 Jun 1999 09:55:37 -0700, you wrote:

>Don't know whether this counts as thoughts, dark corners, nooks and crannies
>or conventional wisdom, but I've always advocated not using auto extend,
>particularly for rollback segment tablespaces and temporary tablespaces.
>The reasoning is pretty obvious. Rogue processes can really blow things
>away if you do use it. At least without autoextend they only fill the
>tablespace.
>
>HTH.
>
>Pete
>

Just to muddy the waters a bit.

I love to use autoextend on temp and rollback. Love it.

I have my temp on a disk that could go upto 2gig. I don't want to back up 2gig. I created temp at 25meg, autoextend on, next 25meg maxsize 2000meg. If I didn't have autoextend -- i would have preallocated 2gig to temp (and i would back it up). Now, I preallocate 25meg, let it grow as needed upto 2gig and before I backup -- i resize it back down (the file, i physically make the file smaller).

Same with rollback. I use the autoextend sort of like 'optimal'. My optimal size for rollback is 25meg. I put each rollback into its own tablespace and each tablespace has its own datafile. I let the next on the the autoextend match the next on the rollback segment. I maxed out the rollback segments as some logical maxsize (100meg on my particular system). Before I backup, i go around and drop each rollback segment that has actually grown and resize the datafile and recreate the rollback segment to fill the datafile to 25meg again. I backup less data.

I always use MAXSIZE on the datafiles to prevent rogue processes from filling up everything.

The main benefit (to me, a frequently absent dba due to travel) is that if someone has a large transaction -- we can usually satisfy it by letting their rollback segment grow on disk. They don't have to pick a rollback segment big enough for them -- all of them have the POTENTIAL to grow (as long as we don't run out of disk space). They don't have to find a rollback segment in a tablespace with enough room to grow rather, any rollback segment potentially has the ability to grow to some maxsize. If they exceed maxsize -- they don't get to finish (no rogues)... I don't have enough space for all of my rollbacks to get to maxsize but I have enough space for many of them too. this lets them go that size as needed. I just periodically run a script (dbms_job would be great for this) to resize them back down...

There is a flip side to every coin I guess...

>Kenneth C Stahl wrote:
>
>> Has anyone ever used auto extend for tablespaces that only contain
>> rollback segments?
>>
>> For example, let's say I create a tablespace called RBS which will only
>> hold rollback segments and give it a size of 15M. I then create ten
>> rollback segments with INITIAL 100K NEXT 100K OPTIMAL 200K MINEXTENTS 2
>> MAXEXTENTS 249. Quite obviously, the first time that I have a
>> transaction which causes a rollback segment to grow towards 249 extents
>> I'll get the "snapshot too old" error message and my transaction will
>> rollback. However, if I give the tablespace the ability to auto extend
>> then the tablespace will grow as needed. I realize the risk here is that
>> if I have multiple transactions that are all causing the rollback
>> segments to grow to many extents that I'll end up with a collossal
>> tablespace but that is something I could always monitor and adjust as
>> needed - but at least I wouldn't be beeped in the middle of the night
>> when batch processing is going on and some job aborts because I ran out
>> of space in the RBS tablespace.
>>
>> Thoughts? Dark corners? Nooks and crannies? Convential wisdom?
>>
>> Ken

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 18 1999 - 12:52:58 CDT