Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: In praise of auto space management

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 10 Jul 2002 22:32:32 +0100
Message-ID: <1026336688.6681.0.nnrp-10.9e984b29@news.demon.co.uk>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US