Optimal Rollback Segment Question

[Tracy]

Hi all,

I am having trouble with the rollback segments in my 8.1.6 db going back down to their optimal value. I ran out of rollback segment space with something that I was doing and expanded a datafile from 200m to 500m. I ran my process again and the same thing happened. I thought maybe it was because the % increase on RBS was set to 0. I set it to 1, but they are still not going down to the optimal size. Does anyone have any brilliant insight on this problem (how's that for sucking up?).

I'd appreciate any opinions on this matter ~

Thanks for reading!

Tracy

[Sureshy]

I didn't think you could set pctincrease for a rolback segment it is always 0. Rollback segment is only decreased to optimal when there are no active transactions in rollback segment. Check xacts column in V$rollstat for this

What is your initial, next, min extents set to ?
optimal should ideally be at least:

Initial + Next * (minextents -1)

[sazzadur]

Rollback segments should not have a pct increase, they should all be the same size as they are used in a circular manner.

[mrvajrala]

Tracy,
It would be nice if you can post the definition of the rollback segment . You might be knowing that the parameter OPTIMAL is for automatic shrinking of the rollback segments.

Have a look at v$rollstat view ..


sazzadur,
Rollback segments are not used in circular fashion, to my understanding the EXTENTS in the rollback segments will be used in circular fashion.

[Sureshy]

You should also bear in mind that your rollback segments should not be extending as this is costly.

If your rbs's are set correctly they will wrap and not exetnd, thus optimal should never be used.

[sazzadur]

mrvajrala,

You are correct, I meant to say that extents are used in a circular manner.

Tracy,

All rollback segments should also be the same size. Unless you have a large segment that you bring on-line for the purpose of running large batch jobs.

[Tracy]

Thanks for getting back to me!

create rollback segment rb_06
tablespace rbs
storage (initial 1000k next 1000k minextents 2 maxextents 2000 );

[mrvajrala]

Tracy,

create rollback segment rb_06
tablespace rbs
storage (initial 1000k next 1000k minextents 2 maxextents 2000 );

To my understanding ....
You may wish to alter this rollback segment...As size of the initila extent, better you have 1024 instead of 1000.. and next extent ( = inital extent ) also the same ...

Also, Oracle suggests to keep MINEXTENTS to 20...
( Metalink : 69464.1 )

Include the OPTIMAL parameter, the value of this parameter depends on the kind of transactions in your DB, if they are small in size and many then SMALL OPTMAL SIZE , if they are LARGE, then BIG value for the OPTIMAL parameter.

Some FOLKS out there may advise not to have OPTIMAL parameter , but in this case you have to manually shrink the rollnack segment.[[ ALTER ROLLBACK SEGMENT SHRINK [TO ]; ]] I think this is what you are missing .


Hope this helps you, And there are very good documents on METALINK regarding sizing the RBS. Have a look at them.

[julian]

Originally posted by mrvajrala

Also, Oracle suggests to keep MINEXTENTS to 20.

That is true and you should strongly follow this advice. BTW, do you know why 20 (why not 15 or 22) :-)

[mrvajrala]

The following document talk about it,,,

http://metalink.oracle.com/metalink/...T&p_id=69464.1

But i am not very clear about this ... may be you can help me out in understanding it better.

Thanks in advance