Re: ASSM vs. non-ASSM

From: Noons <wizofoz2k_at_gmail.com>
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

Original text of this message