.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, July 19, 2005

Round-robin Segment Allocation Issues

As part of a discussion on tablespaces Noons mentions a problem that he found when creating partitions in a uniform extent size tablespace -- long story short, the partitions are all created in the first datafile rather than in a round-robin fashion.

However multiple extents within each partition are still allocated to data files in a round-robin fashion. So it seems to be appropriate to advise that where you are relying on the undocumented round-robin extent allocation technique for balancing i/o across multiple data files it is best to either have a great many extents per partition, or to make the number of extents equal to an integer multiple of the number of data files. Also, to avoid future problems with i/o imbalance when adding new data files later on, you might err on the side of caution by creating a larger number of smaller data files initially, so that you have plenty of room to expand their sizes to whatever upper limit you and your o/s are comfortable with.

Thanks for the heads-up on that one Noons.

1 Comments:

At 7:26 PM, Blogger Noons said...

No worries. I wonder how much of all this will be relevant in future with extent auto allocation, ASM and other "dumb-down-the-dba" developments?

While it's true that with modern SAN and such hardware there is not much point in this "db round robbin", I still feel it's important that the technique is workable and usable and the knowledge to use it is out there: not all of us can afford multi-M$ hardware...

 

Post a Comment

<< Home