Locally Managed Tablespaces

[dbbyleo]

Ok...so I'm reading a lot about Locally Managed Tablespaces.

I have a question....

Why would anyone ever choose to use UNIFORMED sized extents rather than AUTOALLOCATE?? (cuz no of the docs really say the practical side of things)

Unless there's an awful huge overhead with using AUTOALLOCATE, I don't see why I would not use it. Even if there was, I would have to have a very good idea of the growth rate of the tables in that tablespace to set it to UNIFORM...

And what other real benefit to UNIFORM? For a novice like me, I would immediate say...well, so that all your extents are the same size which would reduce frag. BUT LMT says it keeps track of all adjacent free extents...so really there's no need to do coalescing.

So may be one of you seniors out there can explain...

[slimdave]

...I would have to have a very good idea of the growth rate of the tables in that tablespace to set it to UNIFORM...

There you go.

It's not an uncommon situation though, especially where you are loading a data warehouse or other reporting system that uses partitioning.

Another advantage is that if you need to spread i/o across multiple devices then uniform extent allocation gives pretty even distribution. Just make sure that the number of datafiles is an integer multiple of the number of devices, and you might like to make the minimum extent size for the objects equal to the uniform extent size multiplied by the number of data files.

If you're more comfortable with auto allocation though, go ahead with that. Be aware that when you combine auto allocation with auto segment space management on tablespaces of 16kb block size or more, you get a minimum extent size of 1Mb, not 64kb.

[bazza]

There you go.
Be aware that when you combine auto allocation with auto segment space management on tablespaces of 16kb block size or more, you get a minimum extent size of 1Mb, not 64kb.

But remember you can have multiple block sizes for your tablespaces with 9i. So smaller tables can go in a tablespace with either a 2k,4k or 8k block size so that their extent sizes aren't overly large.

Bazza

[dbbyleo]

The more I look into it, the more it makes sense to me to setup (at least) 3 tablespaces using UNIFORM extent sizes (each with a different size to accomodate large, medium, and small segments).

But before I go done that path, I want to validate somethings with you gurus...

1: Basic Concept Understanding...
In basic terms, it seems to that UNIFORM really addresses tablespace fragmentation, while AUTOALLOCATE addresses over extention. Am I right, so far? In UNIFORM, you have the risk of segments growing to too many extents (but how many is too many???... I asked this again below). In AUTOALLOCATE, you reduce the change of being overly extended, but you run the risk of fragmentation - BUT correct me if I'm wrong...Fragmentation will (only) occur if there's a lot of deletes happening to the segment, right?.

2: The Magic Number...
One person said that if I were to use the above idea - using UNIFORM - segments may/will eventually reach a high number of events. And if so, he suggest using 1000 as the magic number. In other words, if a segment has more than 1000 extents, then it time to move it to the tablespace with the next higher UNIFORM size. This makes sense. But here's my question....
What is the magic number?? I know some say that number of extents doesn't degrade performance. But I am sure should it get into the thousands, there's got to be some effects. Is 1000 a good rule of thumb...or should it even be lower ot higher??

For simplicity sake....lets keep the scope to only heap-tables and indexes (no partitions or clusters).

Thanks.

[davey23uk]

virtually impossible to get tablespace fragmentation with any type of LMT's - uniform or autoallocate (there is a paper somewhere proving this)

[bazza]

1: Basic Concept Understanding...
In basic terms, it seems to that UNIFORM really addresses tablespace fragmentation, while AUTOALLOCATE addresses over extention. Am I right, so far?

I'm with Davey on this one. Autoallocate addresses fragmentation as well. Have a look at ask tom, there's a few threads on there and by the end of it, hopefully you'll be leaning back towards autoallocate, let the database handle space management. Also, if using RAC, autoallocate is recommended by oracle.

Bazza

[John Spencer]

It is possible to fragment an Autoallocate tablespace, but it is a pathological situation. Someone posted a sample on the Dizwell forumn (I believe), and it goes kind of like this:

Create a small table with one 64K extent
Create and populate big table that will grow to a much larger extent size
Repeat until tablespace is full.
Drop the small tables
Try to extend one of the big tables.

Clearly not normal practice. In the real world, autoallocate is probably the way to go, particularly if you do not have a good feel for how the tables will grow. If you know how big the tables are likely to get, uniform extents seem cleaner to me, but I do have databases using autoallocate as well.

As far as a "magic number" of extents, I don't think there is one. I would probably start feeling a little uncomfortable when the number of extents got much over 1000, and might decide to do something about it the net time I had a chance, but I certainly would not schedule a downtime to move the table just because I hit 1000 extents.

John

[dbbyleo]

If I have my LMT in autoallocate...
All the segments in that tablespace will start out extending at the 64K size extents. Whatever the algorithm is, it appears that Oracle extends using this size about 16 times before it starts to extend the segment at 1024K.

Say you had 100 segments...all growing at a different rate....while some are still extending at 64K, others are already at 1024K, and till others are already at 8182K. So you can imagine 64K extents jammed in between 1024K and 8192K extents.

At some point, some of those 64K are freed up.

How can you say that it has to be a "pathological situation" before fragmentation can occur?? It seems to with non-Uniform extent sizes, fragmentation is the risk (even without a pathological situation).

Now I know that in LMT, the tablespace know about adjacent free extents (which obsoletes the need to go coalescing), but still, the adjacent extents may still NOT be large enough. So it seems inevitable to me that fragmentation will occur, in varying degree, when you don't have uniform sized extents. This was the "rule" during the days of DMT - "thou shall make my extent sizes uniform or at least in multiples of" - and it's hard to imagine that the same consequences of non-Uniform extents doesn't exist today - LMT or no LMT.

Addtionally, AskTom has this article...
http://asktom.oracle.com/pls/ask/f?p...AST_PAGE:8:YES
He basically states to use autoallocate when segment sizes are unknown or hard to predict .. such as in off-the-shelf apps. However, when apps are bred in-house, developers should have a good deal of knowledge of how segments will grow, and so use Uniform - have a tablespaces for segments that will be large, one for medium, and one for small. It was "almost" implied that autoallocate eases the admin pain when the segment growth is unknown - and the draw backs would be fragmentation, or potential of.

Davey...if you could provide a link to that paper that shows how fragmentation is virtually impossible in LMT, that would really help this thread. Thanks.

[bazza]

Sorry, but Ask Tom (and others) have done this to death.

Yes, there is a chance as John said that you can get fragmentation in an assm tablespace. In general, you won't. Can you honestly say you will know exactly how a table will grow? If you can, by all means use uniform. I prefer to expect the unexpected and use assm.

Bazza

[John Spencer]

I said pathological situation because, in a real production database, how often do you actually drop tables? Sure, there may be a few that are used temporarily then dropped (I often create a table to hold the output of a query when I am developing it so I can easily run some tests on it to validate the correctness of the results) however, in the normal course, tables are created when the application is installed, and live forever.

John