| Rollback Segment sizing [message #58280] |
Tue, 12 August 2003 03:08  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi,
I find that there are lots of Shrinks and wraps in my rollback segment.How do I go about sizing the rollback segments so that these can be avoided?
TIA
Mike
|
|
|
|
| Re: Rollback Segment sizing [message #58292 is a reply to message #58280] |
Tue, 12 August 2003 22:52  |
shesha
Messages: 24
Registered: August 2003
|
Junior Member |
|
|
Sizing rollback segments is not a one time process...
You have to keep on changing the parameters based on ur requirements and performance..
try to keep a bigger RBS for bigger transaction and for other transactions keep the normal one.
You have to keep on shrinking the RBS manually by
Alter Rollback segment rbs1 shrink;
don't forget to use optimal parameter.
|
|
|
|
| Re: Rollback Segment sizing [message #58295 is a reply to message #58292] |
Wed, 13 August 2003 00:26 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi,
Thanks for the reply.The rule of thumb says one can start off sizing the RBS with about 10% of the size of the largets table.Supose the size of my largest table is 300 MB what would be the initial,next and optimal size of my Rollback Segment?
TIA
Mike
|
|
|
|
| Re: Rollback Segment sizing [message #58301 is a reply to message #58295] |
Wed, 13 August 2003 07:18 |
shesha
Messages: 24
Registered: August 2003
|
Junior Member |
|
|
Let us say rbs size-10% of 300mb i.e 30mb.
Each rollback segment’s total allocated space should be divided among many equally sized extents.
In general, optimal rollback I/O performance is observed if each rollback segment for an instance has 10 to 20 equally sized extents
Let us say we will have around 20 equally sized extents.
Use the following formulla to calculate the storage parameters
S = T / N
S=30/20
S=1.5 mb
where:
S = calculated size of each extent initially allocated
T = total initial rollback segment size (30 MB)
N = number of extents initially allocated (20)
Keep INITIAL and NEXT value equal to S ie 1.5 MB
Keep MINEXTENTS value to N i.e 20
No PCTINCREASE i.e 0
If the size S of an extent is not an exact multiple of the data block size, it is rounded up to the next multiple
You should carefully assess the kind of transactions the system runs when setting
the OPTIMAL parameter for each rollback segment. For a system that executes
long-running transactions frequently, OPTIMAL should be large so that Oracle does
not have to shrink and allocate extents frequently. Also, for a system that executes
long queries on active data, OPTIMAL should be large to avoid "snapshot too old"
errors. OPTIMAL should be smaller for a system that mainly executes short
transactions and queries so that the rollback segments remain small enough to be
cached in memory, thus improving system performance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
