Re: ASSM vs. non-ASSM

From: joel garry <>
Date: Wed, 1 Feb 2012 09:26:41 -0800 (PST)
Message-ID: <>

On Jan 31, 4:59 am, Mladen Gogala <> wrote:
> On Mon, 30 Jan 2012 18:37:04 -0800, Noons wrote:
> > Actually, he specifically refers in the posts mentined here to it being
> > partly a result of auto allocation.  Which quite frankly is a bad idea
> > at best of times as it leads to fragmentation of free space if the
> > tablespace is volatile.
> Auto allocation is a bad idea? I don't see why? The extent sizes are
> standardized on "power of 2" sizes, so whenever an object is dropped, the
> relinquished extents will be usable by other objects. That seems like a
> rather sound argument but I maybe missing something. Basically, it's much
> easier to lump everything related to a single application in one
> tablespace with auto allocation and ASSM then to carefully store tables
> and indexes into their own tablespaces, based on projected object size.
> --

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. Jonathan weighed in with the answer, which was something like the objects have to be created on a boundary of the segment size, so the power of 2 can burn you.

Since I have an ERP with various sizes, I use auto allocate, and haven't had any issues like this. As bad as some of the old 4GL I'm using is, at least it doesn't ordinarily do the strange DDL to see this type of problem. I would segregate based on segment size, but have never gotten around to it, the rare times it would make a difference I just handle with moves or reloads anyways. I do have some volatile tables, those are in their own ts, simply because they have been since 8.0.

If I had Noon's data, I probably would do it his way. More generically, autoallocate is fine, I wouldn't go as far as saying uniform is evidence of compulsive tuning, though in some cases I can't help but think it.


Received on Wed Feb 01 2012 - 11:26:41 CST

Original text of this message