Re: ASSM vs. non-ASSM
Date: Wed, 1 Feb 2012 13:27:47 -0800 (PST)
Message-ID: <df910db2-c9fc-4019-9150-699db3582cc3_at_iu7g2000pbc.googlegroups.com>
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