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.


One thought on “Round-robin Segment Allocation Issues

  1. 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…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s