Re: db_block_size = 16K inflates tables

"ivan vasquez" <ivan_at_itos.uga.edu> wrote in message news:aqu0mi$iiv$1_at_cronkite.cc.uga.edu...
> You are right! I was specifying segment space management auto, and that
> causes the inflation. I would expect that to be the default, but according
> to the documentation it is not. I imagine the growth will slow down after
it
> reaches certain point (??) It is still not clear to me why auto is not
> recommended in this case...
>

You have a death-wish, or something? Anyone who fondly wishes ASSM should be the default must live dangerously and like driving fast cars off high cliffs on alternate weekends. Something like that, anyway.

ASSM is nasty. ASSM is bad. ASSM slows things down. ASSM is not a cure-all. ASSM has serious side-effects.

Now, having said all of the above, ASSM does have one use: when you are suffering from free-list contention, which will likely be true if you are running a Real Application Cluster (RAC), then ASSM will cure that contention issue for you, at a stroke. Free list contention, when you get it, is extremely painful, and ASSM's undoubted side-effects are probably going to be worth enduring to get rid of it. And they are infinitely less trouble to full table scans that multiple free lists, which is the alternative treatment for free list contention.

Unless you have free list contention, however, and as you discovered, ASSM takes up an awful lot of space within the segment -which means it takes up an awful lot of space in the buffer cache. Slow down factor number 1. It causes full table scans to have to duck and weave through the segment, reading the bitmap blocks to work out which data blocks it should scan and which it should skip: full table scans therefore take a lot longer in ASSM tablespace than in non-ASSM tablespace. Slowdown factor 2.

So why on Earth would you want to take on board such side effects if you aren't suffering from contention issues in the first place?

Toning down the rhetoric a bit: ASSM is a tool. It should be used where the tool is appropriate. That is almost never going to be 'always'... and hence, quite sensibly, it isn't the default (and I pray it never will be in 10i, 11-and-a-half-i or whatever product is next on the conveyor belt).

Regards
HJR
> Thank you!
> Ivan.
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:zkgA9.75515$g9.212480_at_newsfeeds.bigpond.com...
> > Hi Ivan,
> >
> > I think your "problem" is not associated block size but with the fact
you
> > are using ASSM. It doesn't insert data in the traditional 'from left to
> > right' method but will 'select' an appropriate block from the bitmap
> matrix
> > and insert the row. As such, these gaps are an issue if you wish to
insert
> > an initial small number of rows.
> >
> > Try without ASSM and see how many blocks Oracle uses. You will be
pleased
> > with the results.
> >
> > Cheers
> >
> > Richard
> > "ivan vasquez" <ivan_at_itos.uga.edu> wrote in message
> > news:aqs1sg$4q3$1_at_cronkite.cc.uga.edu...
> > > Hi,
> > >
> > > I just installed a 9.2.0 instance for GIS applications (ESRI ArcSDE).
> The
> > > manufacturer suggests using db_block_size = 16K. After a first
> > installation
> > > using block size 8K, I reinstalled the database using 16K to see if
the
> > > performance improved. However, a more important problem appeared:
Tables
> > are
> > > growing way too fast.
> > >
> > > (All tablespaces have been created locally managed, auto segment space
> > > mgmt.)
> > > In a 8K blocksize tablespace, a table with only 160 rows, 46 bytes per
> > row,
> > > uses 5 data blocks.
> > > In a 16K blocksize tablespace, the exact same table uses 61 data
blocks!
> > >
> > > The tables were created with no storage statements, so defaults should
> > > apply. The data was retrieved from user_tables after analyzing them.
> > >
> > > What could be "wrong"?
> > > Thank you,
> > > Ivan.
> > >
> > > Table description:
> > >
> > > SQL> desc ga_cnt00
> > > Name Null? Type
> > >
> >
>
> ----------------------------------------- -------- -----------------------
> > -
> > >
> > > OBJECTID NOT NULL NUMBER(10)
> > >
> > > AREA NUMBER(24,5)
> > >
> > > COUNTY VARCHAR2(5)
> > >
> > > NAME VARCHAR2(13)
> > >
> > > LR_ID NUMBER(16)
> > >
> > > POP00 NUMBER(16)
> > >
> > > ID VARCHAR2(5)
> > >
> > > SHAPE NUMBER(10)
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Wed Nov 13 2002 - 12:40:40 CST