Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespace best practices

Re: Tablespace best practices

From: Howard J. Rogers <>
Date: Tue, 28 Dec 2004 08:29:08 +1100
Message-ID: <41d07ea5$0$5112$>

Mladen Gogala wrote:
> On Mon, 27 Dec 2004 17:26:07 +1100, Howard J. Rogers wrote:

>>However, it has potentially ENORMOUS costs: full tablescans potentially 
>>about 15% longer to complete. Buffer cache, potentially up to 30% full 
>>of Oracle administrative overhead.

> Howard, can you explain this in little more detail?

ASSM uses bitmap blocks to record the fullness/emptiness of other, normal, data blocks. Those bitmap blocks take up space within extents (thus making the segments bigger than a non-ASSM equivalent, and thus making more blocks to read during a FTS. Previous tests showed segment sizes up to 17% larger than non-ASSM equivalents). And in a heavy insert-oriented environment, the bitmap blocks will also have to be read into the buffer cache and stay there, thus taking up far more valuable memory space. I believe Jonathan has a test result showing 20%+ of his buffer cache full of nothing but bitmap blocks. I think it was something like 28% -hence my rounding to 30%. Tests I did back when ASSM was being enthusiastically adopted left, right and centre showed 14% of my buffer cache full of bitmap blocks. But Jonathan tests more thoroughly!

To minimise these ASSM costs is fairly easy: don't allow a segment to acquire too many extents. But that means we're back in the land of having to worry about extent sizes (and hence the number of extents). Get it wrong and your disk space and buffer cache impacts will be considerable.

That is why, incidentally, if you are going to use ASSM, I think it is fairly important (though it's not syntactically necessary, unfortunately) to use AUTOALLOCATE for your LMTs. ASSM plus AUTOALLOCATE together really do make extent management the nearly-brainless task it should be. So I'm not anti-ASSM completely: I just think it needs to be used appropriately, and that there are seriously wrong ways to implement it, too.

Don't forget a cost I hadn't thought about until Nuno put me onto it, and which I discussed a while back on the Dizwell blog: ASSM means an 'insert into table X select * from table Y order by column1' doesn't actually result in table X having data loaded in a known order. Which means a 'create index blah on X(column1) nosort' fails. Could be a bit of a bummer in a warehouse-type environment too, that one.

HJR Received on Mon Dec 27 2004 - 15:29:08 CST

Original text of this message