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

"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:36:24 CDT