Re: Segment management auto clause

I'll continue where my wireless keyboard decided to throw in a spare 'return key' press...

... [see other half-done post] ...

> > > 3% extra space utilisation, 17+% extra full scan time.
> >
> > OK, this is all incorrect generalisations.

You can call them that if you want. But (a) they weren't generalisations, but a specific set of results. And (b) they weren't incorrect, but a specific set of repeatable results.

>The extra space utilisation
> > really does depend.

Sorry, if you can't work out when I am recalling the results of a specific set of test results generated nearly a year ago, and when I am stating as fundamental features of ASSM, we're not going to get very far. To the original poster, I said ASSM had a large 'overhead'. That's a generalization. When I replied to you '3%...17%', that's recalling a specific result. I haven't ever suggested that somehow ASSM's overhead is fixed, or does not 'depend'.

>Some first level bitmap blocks may only span 16
> blocks,
> > however some first level bitmap blocks span 64 blocks or 128 blocks or
256
> > blocks, etc depending of the current size of the segment and block size.
> So
> > tiny tables can have an extra space utilisation of somewhat more than
3%,
> > larger tables can have an extra space utlilisation of a lot less than
3%.
> >
> > Extra full time scan time of 17%+, well let's see. I would be really
> > interested to see what results other people have with the following
demo:
> > (WARNING: a touch on the long side !!)
> >

[snip]

> >
> > *** note, approx 0.35% more blocks allocated ***

Once again, you make it sound like I said "3%" as though it were fixed in stone. I got 3% in a specific test, and I don't believe I've ever claimed otherwise (and if I recall, another poster made that calculation for me in any case. Disk space wastage was the least of my concerns).

Your earlier comment is nearer the mark: for big tables, the overhead (of space utilisation) as a proportion reduces. For small tables, the overhead can be significant. Sweeping statement, I realise (so yes, you can call this one a generalisation if you like): but where are you likely to have large tables? In a RAC. So where is the space overhead going to be minimised? Er, in a RAC. And where have I said ASSM is an excellent solution to a particular problem? Stone me, but in a RAC.

> > Again, an obvious big depends.

In life, most things depend.

Right now, there's not a word in any Oracle material about the potential costs of ASSM. It's all presented as an unmitigated blessing. Your test shows blessings, mine have repeatedly shown costs. All I have done since last August (if it was then, and I take your word) is point out here and in the classroom that ASSM is not a straightforward, no-brainer, one-way bet of a decision that Oracle's own material makes it out to be (or that LMT is these days, either). That it definitely has its uses. That I would unhesitatingly recommend it over multiple freelists as a cure for FLC. But that it has potentially enormous costs (disk space not being the major one by a long shot).

There are now two sets of results out there which appear to conflict. That's no bad thing, because people can always repeat published tests for themselves and see how their systems behave. But I don't report results which are one-offs, but only those which are repeatable. And repeatable. And repeatable.

Now, if you find ASSM a good thing, fine by me. But I haven't, and my research on this matter has not been confined to just two test runs and a benchmarking environment.

Regards
HJR Received on Wed May 14 2003 - 14:55:29 CDT