Re: ASSM vs. non-ASSM

From: onedbguru <>
Date: Thu, 26 Jan 2012 15:35:41 -0800 (PST)
Message-ID: <>

On Jan 26, 5:11 pm, "Gerard H. Pille" <> wrote:
> Mladen Gogala wrote:
> > On Thu, 26 Jan 2012 20:02:19 +0100, Gerard H. Pille wrote:
> >> Yes, like 100 times faster, which I would call significant.   Do you
> >> know what a "soufflé" is,
> >> and how it can fail?   That's like our indexes.
> > So, your indexes are made with egg whites and chocolate? No wonder they
> > fail. They must be delicious?
> I must admit I never had a soufflé with chocolate.
> But after a week of extreme maltreatment, our indexes are full of air.

I like "soufflé".

Yes, but the next time you need "more space" for that index it is already allocated and will be used. If you need the space more than you need the performance boost by not having to allocate more data blocks to said index, go ahead and shrink it or rebuild it.

I really like the idea not not having to worry about where my data is stored. Use ASM, ASSM, BIGFILE tablespaces and Fewer tablespaces. I found that with ASSM, I do get used allocated space for indexes, but shrinking/rebuilding them only last a few days, then it is back almost exactly the same size prior to the operation. We found that if we left it alone, it did what it was supposed to do and reuse that allocated space for that index as necessary. So, we just added more disks for other objects and left these big indexes alone. Performance did not change before or after rebuild/shrink operations.

Performance going from filesystem to ASM almost doubled on one system. But typically I see 10-20% improvement. YMMV. We started tracking overall usage and stopped worrying about an individual device filling up. With small-file TS, you still need to add more datafiles if they become full, but you should be monitoring tablespace usage anyway. If you move to BIGFILE, that too goes away. For an 8K block size, a BF datafile can grow up to 32TB.

Adding datafiles to SMALLFILE TS is also much more simple. alter tablespace foo add datafile '+DATA1' size 1G autoextend enable; Received on Thu Jan 26 2012 - 17:35:41 CST

Original text of this message