Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: In praise of auto space management

Re: In praise of auto space management

From: Jonathan Lewis <>
Date: Wed, 10 Jul 2002 22:32:32 +0100
Message-ID: <>

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)

Jonathan Lewis

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November

Connor McDonald wrote in message <>...

>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.
Received on Wed Jul 10 2002 - 16:32:32 CDT

Original text of this message