Rollback segment esoterica

[JDoyle]

I answered someone's question regarding rollback segment tablespace fragmentation last night and it got me to reviewing some of the details of my own database. This led to questions for your ponderment
1.) Highwater mark for RBS. I can imagine that this would have some meaning where optimal is not set. However, does it have any practical implications where I use optimal?
2.) Oracle recommends that all extents in a rollback segment be the same size. Yet in their database creation scripts they create RBS with initial 100K, next 250K. Wassupwidat?
3.)Optimal. I have set my optimal to 500 K (512,000 bytes)(initial 100K, next 250K, and currently extent count is 3), yet the RB segments are being autoshrunk to 612352 bytes as shown in DBA_ROLLBACK_SEGS. Not that it really matters, but I wonder why they're not being shrunk to 500K?
Any ideas?

Thanks.

[marist89]

1. Highwater mark for an RBS that has an OPTIMAL clause is irrelevent. The RBS will extend to a certain point and then shrink back down to optimal.

2. I have found oracle defaults are a case study in how NOT to setup your database.

3. Your RBS will never shrink back down to OPTIMAL, as 500K does not fit in 100K+250K+250K. (Hence 612352 - 600K +/- overhead).

[JDoyle]

Hey Jeff:
Thanks for your reply. You obviously live, breathe, eat and sleep this stuff. (Intended as a compliment)
Regarding your third answer. .. It seems then that Oracle will deallocate (shrink) only by whole extents, whereas if I issue alter rollback segment shrink to size, I can shrink right into the middle of an extent.
Also one other question. I used tablespace manager to look at my RB tablespace and noticed that almost none of my twenty odd RB segments had adjoining extents shown on the map. Is that something to be concerned about?

Thanks again.
Johnny D.

[sreddy]

" You obviously live, breathe, eat and sleep this stuff "

:) I do feel its right compliment for Jeff.

[marist89]

[QUOTE][i]Originally posted by JDoyle [/i]
[B].. It seems then that Oracle will deallocate (shrink) only by whole extents, whereas if I issue alter rollback segment shrink to size, I can shrink right into the middle of an extent.
[/B][/QUOTE]

AFIK, you can not shrink to the middle of an extent. You can issue an "alter rollback segment shrink to 500K", but I think it will still end up as 600K.


[QUOTE][B]Also one other question. I used tablespace manager to look at my RB tablespace and noticed that almost none of my twenty odd RB segments had adjoining extents shown on the map. Is that something to be concerned about?
[/B][/QUOTE]
I don't use OEM to manage my storage, so this may be irrelevent. However, if your RBS are all over the place in the tablespace, it might or might not be a big deal. If your RBS have a uniform next extent, then no, it's not a big deal. The space that is not being used is in multiples of NEXT_EXTENT size. If your RBS DO have different NEXT_EXTENT sizes, you may have a need to be concerned.

In general, I like to have a couple different tablespaces for my Rollback Segments. I have at least 2 tablespaces that I split my rollback segments evenly into (RBS1 & RBS2). That way, I limit the disk contention on any one transaction. These tablespaces have the initial extent and next extent parameters equal. The optimal size for these rollback segments is MINEXTENTS * EXTENT_SIZE. I usually also create a tablespace that has large initial and next extents and create a rollback segment that is offline. When I need to do a big transaction, I place that RBS online, use it, and then place it offline again.

[JDoyle]

Regarding shrinking into the middle of an extent ... I stand corrected.
I tried to do this and it does not work. I had been led astray by the following comment in Loney and Theriaults Oracle 8i DBA Handbook pg. 285
"You can use the shrink clause of the alter rollback segment command to shrink rollback segments to any size you want."
Not so. Only to the nearest whole extent.
I was also confused by the ability of "deallocate unused" to deallocate into the middle of an extent.
Thanks Jeff
Johnny D.

[sambavan]

You might want to be careful on using the shrink clause in 8.1.6.0, if I could remember correctly, it might lead you to some kind of corruption, again I'm not sure exactly, but have seen some where in the metalink.