Re: ASSM vs. non-ASSM
Date: Tue, 31 Jan 2012 14:04:05 -0800 (PST)
Message-ID: <03d5f522-635e-4858-9f19-102dec2c439c_at_kg1g2000pbb.googlegroups.com>
On Jan 31, 11:59 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> Auto allocation is a bad idea? I don't see why? The extent sizes are
For precisely the same reasons auto increment in old dictionary managed tablespaces was a bad idea. Great on paper, totally impractical in use.
> standardized on "power of 2" sizes, so whenever an object is dropped, the
> relinquished extents will be usable by other objects.
Only if the other object's extent size request matches the sizes made available by the dropped object, in sub-multiples. That is not always the case.
With uniform allocation there is *ALWAYS* a match. Without fail, no matter what. I'd much rather have that - a no problem - than a potential problem.
As well, if you want to do a reorg in place where you compact a heavily fragmented table into the free space available at start of tablespace by doing a alter table move with no tablespace target, it simply won't work with ASSM auto in 99% of the cases. While it is a breeze and works everytime, without fail, with uniform allocation.
> 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.
Oh, spreading things into their own tablespaces is not just to manage size. It's also to manage type of usage. And the corollaries: volatility and frequency of reference.
And of course, the uniform size can be adjusted to match each type of use and size of table. I tend to use 1MB as a catch all (except for Peoplesoft and its 25000 tables!!!), then 10MB or even 100MB for much larger tables/indexes. Each type in its own tablespace, by volatility of usage. And particularly where LOBS are concerned I much prefer this type of management than letting ASSM auto manage (badly) all size multiples/sub-multiples.
Essentially and from what I've seen, ASSM with auto allocation requires careful monitoring and frequent maintenance. With uniform allocation I set it once and I don't have to worry about it ever again. Other than overall size management which I have to always do, anyway.
Then again with 11gr2 and sparse initial allocation, 1MB catch-all may not be all that bad for Peoplesoft as most objects won't use any space until something is actually stored there. But I remain to see how that is possible to do when the Peoplesoft install is totally agnostic to 11gr2 and the sparse allocation is not valid for pre-existing tables and won't work on exp/imp - which makes it essentially unusable with PS...
Ah well, things may improve on 12cr2... ;-) Received on Tue Jan 31 2012 - 16:04:05 CST