Re: In praise of auto space management

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>Your results may vary.
>Depending on your LMT extent size, and the
>process id that happens to insert the one
>row:
>
>create small table
>insert one row;
>select * from table;
>
>The first time I tried, the one row
>went into the 13th block in the
>table - so I got a 13 block tablescan
>for a one-row table.
>
>Mind you, I keep telling people that
>there's no such thing as a small
>table - all tables should be indexed etc.
>
>
>Your other point is potentially more
>important though. The number of 1st
>level bitmap blocks is a large percentage
>of the number of table blocks. In the
>BEST case, one BMB seems to be
>allocated for every 256 data blocks.
>In other words, close to 0.4% of your
>database ends up as bitmap blocks.
>That's a pretty ferocious price to pay
>for improving concurrent insert rates.
>Bear in mind that not many large systems
>actually have a buffer that is that fraction
>of the database !
>
>On top of that, the current (at least 9.0)
>implementation of bitmap blocks is
>VERY good at losing space by failing
>to maintain the bitmaps properly.
>
>Try deleting from a block that is also
>getting inserts, and Oracle may not
>update the bit pattern properly to
>allow the deleted space to be re-used.
>
>Trying inserting and rolling back, and
>Oracle may not update the bitmap correctly
>to reflect the rollback.
>
>You could pay a VERY high price in
>wasted space ( = increased I/O) if
>you don't experiment very carefully
>with ASS (automatic segment space)
>management.

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.

Excessive freelists waste disk space anyway, and getting exactly the right number is a lot of hard work, and a moving target as the environment changes. BMB may waste a bit more space, but that will just come out in the normal capacity planning exercise.

Now 36GB spindles are increasingly standard the number of disks we need for I/O bandwidth easily exceeds the number we need for data storage. We need to find *something* to fill all the space. :-) (Is it James Morle's book that talks about this problem? If it was true a few years ago, it's even more true now).

I haven't done extensive tests of insert/delete activity. Thanks for the hint in that direction. Hopefully the inefficiencies and inadequacies in space management are a Simple Matter of Programming and Oracle can do something about them. Until then, we can just use IOTs for everything and do a periodic online re-org ;-).

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/