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

Home -> Community -> Usenet -> c.d.o.server -> Re: Best/Only way to change RBS initial/minextents

Re: Best/Only way to change RBS initial/minextents

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 3 Oct 2002 09:39:22 +1000
Message-ID: <%kLm9.45026$g9.127873@newsfeeds.bigpond.com>


Sorry: I meant to add:

Question 6: Can I pressurize management for some more disk space? (answer: yes)
Therefore: create rollback segement RBS01 storage (initial 8000K next 8000K minextents 6) tablespace RBS;

Regards
HJR "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:miLm9.45024$g9.128459_at_newsfeeds.bigpond.com...
>
> "Don Seiler" <don_at_seiler.us> wrote in message
> news:94e86aed.0210021514.10b083eb_at_posting.google.com...
> > Hullo. Like many out there I'm plagued by the "snapshot too old"
> > errors at the worst times. Other than creating a dedicated rollback
> > segment for large transactions, the recommended course of action seems
> > to be:
> >
> > 1. Increase the initial extent on rollback segments to a much larger
> > value so the allocated rollback is there without having to extend.
>
> No, the cure for 1555s is to make the ENTIRE rollback segment bigger. And
> since INITIAL should equal NEXT, that means the entire set of storage
> parameters needs to be increased, not just INITIAL.
>
> > 2. Increase the minextents parameter on the rollback segment to
> > achieve the same effect (probably better than 1 for concurrency)
>
> Lord help us. MINEXTENTS just means your segments acquire extra extents at
> creation. As such, they end up being bigger than they might otherwise be,
> and since size is the key to stopping 1555s, so far so good. But since
> MINEXTENTS only takes effect at intial segment creation time, it doesn't
> necessarily solve on-going size issues.
>
> > OK I stole those from a Thomas Kyte post.
> >
>
> Mmmmm. I would have hoped that what Tom was saying was : "Make your
segments
> bigger". These are two ways of doing it, but it isn't rocket science.
>
> > Anyway to my dismay it appears that neither of those can be done
> > through ALTER ROLLBACK SEGMENT, and that I need to drop and re-create
> > them.
>
> It's always a bit tricky to change the size of the initial extent once
> you've acquired it! ;-)
> It's likewise a bit tricky to demand a minimum of 6 extents at creation
time
> after you've already created the segment.
>
> So yes, it's drop and re-create time. Is that so much of a drama?
>
> >
> > Here are the stats on all the rollback segments:
> >
> > Initial size: 512 KBytes
>
> Forget it. I don't know your database, and I could be persuaded otherwise.
> But to hell with it. I don't create rollback segments with extent sizes
less
> than 8Mb.
>
> > Next size: 512 KBytes
> > Optimal size: 1024 KBytes
> > Minimum num: 2
> > Maximum num: 249
>
> Don't set optimal. Please. It's crap for performance, and 1Mb is in any
case
> extremely small.
>
> >
> > I have 25 rollback segments (R01-R25) that are all like this. They
> > reside on the RBS tablespace which is 4 GB (2 2GB datafiles). The
> > db_block_size is 4096.
> >
>
> Oh well, I assume this is Linux, otherwise 4Kb is the wrong block size.
>
> > Basically I planned to do this for each one:
> >
> > ALTER ROLLBACK SEGMENT R01 OFFLINE;
> > DROP ROLLBACK SEGMENT R01;
> > CREATE ROLLBACK SEGMENT R01
> > TABLESPACE RBS
> > STORAGE (
> > INITIAL $A
> > NEXT $B
> > MINEXTENTS $C
> > MAXEXTENTS $D
> > );
> > ALTER ROLLBACK SEGMENT R01 ONLINE;
> >
> > I need to know what the best values for A, B, C, and D would be for my
> > database. I read that OPTIMAL is bad so I left it out of my example
> > code here. Or just let me know if I'm crazy and doing things the
> > incredibly hard way.
> >
>
> You're doing it hard. What you want to do is to avoid segment growth or
> shrinkage. Optimal encourages shrinkage (hence, don't set it). And trying
to
> create pretty, perfectly-formed, small rollback segments is likewise an
> encouragement to growth. If you can spare the disk space, create them all
> quite large: say, 6 extents of 8Mb each. If you want your OCP, then 20
> extents is the right number -and I'd still say they should be each 8Mb
big.
>
> Things get more interesting if you have to be careful about disk space.
But
> the one thing you can't skimp on, ever, is rollback segment space.
>
> You want to know how I do it?
>
> Question 1: How much space do I have for rollback segments? (answer: 60Gb)
> Question 2: How many concurrent transactions do I have to cope with?
> (answer: select * from v$transaction at various times, and pick the
> maximum.... say, 240)
> Question 3: What's 240/4 (ie, 1 segment per 4 concurrent transactions)
> (answer 60)
> Question 4: What's 60Gb divided by 60. (answer 1Gb)
> Question 5: What's 1Gb divided by 6 extents? Er, er, er... damn I wished I
> passed A level Maths!... er, 180K. Or thereabouts.
>
> Therefore: create rollback segement RBS01 storage (initial 180K next 180K
> minextents 6) tablespace RBS;
>
> Regards
> HJR
>
>
>
>
>
> > Any help you guys can provide would be great. I inherited this system
> > so please be gentle.
>
>
Received on Wed Oct 02 2002 - 18:39:22 CDT

Original text of this message

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