Re: ASSM vs. non-ASSM
Date: Wed, 1 Feb 2012 09:26:41 -0800 (PST)
Message-ID: <0c9dc400-f12d-452c-87a2-81b7b43f6a0a_at_kg1g2000pbb.googlegroups.com>
On Jan 31, 4:59 am, Mladen Gogala <gogala.mla..._at_gmail.com> 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.
>
> --http://mgogala.byethost5.com
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.
http://asktom.oracle.com/pls/asktom/f?p=100:11:3813628452718745::::P11_QUESTION_ID:5549302357655#44747021484934
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.
jg
-- http://www.maximumpc.com/article/news/oracle_hewlett-packard_strategically_screwed_without_itaniumReceived on Wed Feb 01 2012 - 11:26:41 CST