Re: In praise of auto space management

In article <1026378847.28305.0.nnrp-01.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>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

There are definite scenarios that ASS management is very beneficial to. We have one client we used this at that has huge insert rates (up to 16,000 inserts per second) and it worked extremely well. Only problem we ran into was when a less knowledgeable DBA reorged the database without it, so we went back to manual segment space management with freelists and freelist groups = 1. We worked out later that to get the same performance with manual segment space management that we got with ASS management we would have needed 128 freelist groups per table. As you could imagine, performance just plummeted after the reorg.

Pete
>
>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.
>>
>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Thu Jul 11 2002 - 11:07:51 CDT