minimum extent and rollback segments

[Sureshy]

Hi Friends.

Question 1

I have recently been looking at tablespace default storage and am confused about one aspect.

There is a parameter 'minimum extent' which oracle recommends should always be the the same as initial extent size. My question is, if this is the case, why have this parameter ('minimum extent') there at all ?

Does anyone know of any scenarios where minimum extent would be different from initial extent, from what I understand, they are both the same thing - specifiying default initial storage allocation on a new segment.

Question 2

I have 15 RBS initilaized in init.ora rbs1 -rbs15.
When I Select * from v$rollname, 23 are listed.
Does anybody know where these other 8 have come from ?

Thanks in advance

Suresh

[pando]

minimum extent is to minimize fragmentation in tablespaces.

if you set minimum extent to 128 you are basically settng all extent size in the tablespace to be multiple of 128

examle

you create a tablespace with initial 200K, next 200K and minimum extent 128K, instead of asigning 200K to inital Oracle will create extent size of 256K (multiple of 128). By having extent size multiple of each other you minimize the fragmentation by reusing the free extents

Regarding your second question I am not sure, probably is caused by transactions and transaction_per_rollback (or smth like this) in your parameters

[dknight]

1. In one Oracle book, it recommends that the rollback segments are created with 8 extents all of the same size. In such a case, minimum extents would be 8.

Maybe I am confused, but I think the sizes of extents are determined by INITIAL_EXTENT and NEXT_EXTENT.

2. You can create RBS's without listing them in init.ora. To find out more about these, look at DBA_ROLLBACK_SEGS.

Good luck.

[Sureshy]

Hi..

Regarding minimum extent..

Doesn't the inital and next extent size ensure that fragmentation will be at a minimum?

If I set initial to be 5242880 and next extent to 5242880, this will ensure that extents slot in next to each other with minimum fragmentation. Is minimum extent just a precautionary measure, in case dba's get their maths wrong ?

Thanks

Suresh

[pando]

well if you set initial = next you are avoiding fragmentation, this is in RBS but in DATA and INDEX tablespace where you can have different sizes of extents the clause minimum extent will minimize the fragmentation.

However if you omit this clause when you are creating RBS, even you create RBS with initial and next of 128K (for example) the extent size is really multiple of 5 * block size, if you block size is 8K then 128K is really 120K, since each I/O chunk are usually 64K its recomendable to set the extent sizes to 128K to improve I/O (exactly 128K not 120) and to get this exact size you got to use minimum extent clause

[Sureshy]

Thanks Pando..

Suresh