Re: In praise of auto space management

I'm finding this thread quite interesting, thanks guys.

One thing that hasn't been discussed and which is a possible advantage with auto space management is the "loss" of PCTUSED. Speaking to many DBAs over the years I know this is often one value that is not set (either because of laziness, or because of uncertainly about what it should be set to or about what it really does). In tables that have a significant amount of delete activity in particular, this 40% default often results is much wastage of space below the HWM. There are also the horror performance stories of blocks getting taken off the freelist *below* the PCTUSED value (when DBAs have set this totally inappropriately and too close to PCTFREE).

All these issues (to a degree) have been taken away with ASM. There is the issue with the space "wasted" with these bitmaps but I suggest there were potential issues with space wastage previously as well. ASM is another example of making the default behaviour of the DB more autotunable. I agree that this all needs to be looked at carefully.

Also, your observations suggest that a larger block size would be more efficient in how these bitmaps are used. Interesting.

Regards

Richard

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1026378847.28305.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> Raw disk space is quite cheap, and raw memory
> is quite cheap. Getting material off disc and into
> memory is not cheap, and using Oracle blocks in
> memory is not cheap either. If every block is 75%
> full instead of 95% full, and if you can only use 50%
> of your buffer for data because the other 50% is full of
> bitmap blocks and they stay hotter than data block,
> this is very likely to affect the users perception of
> performance.
>
> The other point I forgot to mention about ASSM
> is that it is likely to affect the clustering factor on
> indexes, and therefore the optimizer's probability
> of using the index.
>
> Take the classic order entry process with meaningless
> sequence numbers for order number. Under standard
> space management, if you and I enter an order at
> the same time (which means we probably get adjacent
> sequence numbers) the rows will go into blocks that
> are very close to each other, probably even the same
> block if we don't have multiple freelists. If we switch
> to ASSM, then your order may end up 255 blocks away
> from my order because we have different process IDs.
> It's likely to affect the optimizer's statistics - without,
> of course, affecting the contention that are going to take
> place on the index blocks.
>
> I'm still testing ASSM - and I would be very cautious
> about using it in production at present.
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK July / Sept
> Australia July / August
> Malaysia September
> USA (MI) November
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Andrew Mobbs wrote in message ...
> >Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> >Hm... thanks for the info. OTOH, disk space and memory keeps getting
> >cheaper, but DBA time doesn't. I'm not that unhappy to see buffer
> >space being wasted if it means that I, our support organisation and
> >our customer's DBAs don't have to worry about the subtle and quick to
> >anger nature of freelists and freelist groups.
> >
>
>
>
Received on Fri Jul 12 2002 - 05:08:27 CDT