Re: In praise of auto space management

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.

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

Connor McDonald wrote in message <3D2C8855.30E3_at_yahoo.com>...


>

>create table REALLY_SMALL as select * from dual;

>

>then issue

>

>select * from REALLY_SMALL

>

>takes 3 logical IO's in an LMT.

>takes 7 (!!!) logical IO's in an auto-seg-space LMT

>

>Not a criticism on seg-space-mgt really (it only takes 1 IO if the table

>is an IOT)...but one to be wary of.

>

>I've also seem other comments along the lines of the bitmap blocks

>possibly taking up a lot of space in the buffer cache, but I cannot

>comment on that hypothesis.

>