Re: ASSM is evil...

Oradba Linux wrote:
> Howard J. Rogers wrote:
>
>> ...well, not really, of course! But this took me a little by surprise,
>> and I thought it was worth documenting accordingly:
>>
>> http://www.dizwell.com/2004/12/assm-is-evil.html
>>
>> Regards
>> HJR
>
>
> Howard
>
> It looks like a unique situation.

It's certainly a specific situation. But it could be quite a common one: bulk loads happen a lot; it's common to drop indexes before you do them; it's fairly common to load in a known order so the index can be build NOSORT. The last bit of that must now inevitably not happen in ASSM.

>I have read about this on asktom
> website.

Oh well! Maybe I should read around a bit!! It was obvious to me as soon as I thought about it... but I hadn't thought about it before being asked to! And I hadn't seen it discussed anywhere else. I'll have a search.

> Other than this are there reasons to avoid this.

ASSM has overheads, sure: and quite significant ones if you get unlucky. The bitmap blocks take up physical disk space (not so important) and buffer cache when they're read/written (potentially, very important). Full Table Scans in ASSM can take anywhere between 7 to 17% (or thereabouts) longer than an identical table in single freelist tablespace. And the problems of memory consumption and FTS speed get worse the more extents a segment has ...and having embraced locally managed tablespaces in large part because they make worrying about the number of extents irrelevant, it is doubly sad that a new technology comes along which puts that concern back on the radar.

Their one saving grace is that they make freelist groups a thing of the past (they were a real pain to manage correctly in Parallel Server), and abolish freelist contention issues. If you are therefore running in RAC (OPS with knobs on), or are suffering from, or are likely to suffer from, freelist contention (common in a RAC, though only for specific tables), ASSM is a brilliant solution.

> We are not on RAC right now but will be moving to 9iR2 RAC on Linux
> in Q2 2005. So can we build new tablespaces right now with ASSM or
> just go for manual .

You "can" build right now, of course. Whether that's wise, I can't comment: it needs comparative statistics and some testing.

Bear in mind that ASSM is implemented at the *tablespace* level, but the problems it is designed to fix (freelist contention and freelist group management inflexibility) are *table*-specific issues. If you're going to implement ASSM ever, at all, it pays to make sure you house only those tables that benefit from it in the ASSM tablespaces. There is a danger of lumping lots of other tables in with the ASSM treatment, though, and at that point, you incurr all the costs and none of the benefits of ASSM. In other words, *converting* to ASSM should be a rather more thoughtful job than might first be apparent.

Incidentally, I am also sure Richard Foote will want to chip in with a comment about how he already uses ASSM for everything and has no problems whatsoever, and thoroughly recommends it, etc etc. So there are potentially two sides to the story. It's been discussed in this group before now (quite a while ago).

It's also fair to say that *any* automation technology (think automatic undo; thing autoextend for data files) has benefits and costs. ASSM is certainly not unique in this. I happen to think that ASSM's costs are extremely high, and only the worst sort of situations yield benefits that outweigh them. But that's just my view.

Regards
HJR Received on Sat Dec 04 2004 - 13:47:13 CST