Re: In praise of auto space management

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.

>