Re: ASSM vs. non-ASSM
Date: Wed, 1 Feb 2012 13:27:47 -0800 (PST)
On Feb 2, 4:26 am, joel garry <joel-ga..._at_home.com> wrote:
> A while back Niall posted a script on his site, and a question about
> this on asktom. I tried a variant of it, basically, creating some
> dozens of tables to fill a tablespace, dropping every other one, and
> adding them back, in short order there was an out of space error.
I do recall that. But that is not really the issue. We don't daily
create/drop tables. Data on existing tables is not static. We create
tables once, then populate them, and in many cases daily and with more
data than before. As they grow, they'll grab increasingly larger
extents with ASSM. And if they all reside in a single tablespace, it
is a given those extents won't be contiguous: they can't be if there
is more than one growing table.
Now, if one or more of those tables gets a truncate - or lots of deletes - and we want to shrink it either via reorg or offload/load, we'll end up with a tablespace that has "holes" of different range of sizes all over.
The problem is not the holes themselves, that is not an issue. The problem is that the number of holes of each size may not necessarily match the requirements for expansion of other tables already large in that tablespace.
With uniform allocation, the holes are always the same size and all free space can be used at any time with no restrictions. It has nothing to do with tuning, it has all to do with not creating a nightmare of maintenance,*IF* data is volatile. Although of course if one's job description is to create issues to resolve later, then auto-allocate is a great idea! :-) Received on Wed Feb 01 2012 - 15:27:47 CST