Re: In praise of auto space management

You've crossed wires on the FREELIST/INITRANS I think. initrans became modifiable in 8.0, I'm pretty sure that freelist wasn't until somewhere in 8.1

More particularly - freelist simply re-balances the number of freelist slots in the segment header (and freelist group blocks) between process free lists and transaction free lists. The comments about old blocks/new blocks are irrelevant.

The reason why it could waste space to have large numbers of free lists is that each free list could request 5 blocks of free space when the master free list was empty - so worst case is that the last (5 x freelists x freelist groups) blocks in the table could contain virtually no data.

If you reload a table (imp, ctas etc) you probably used just one free list, so haven't had a chance to see the effect.

Mind you, on large tables, 5N extra blocks need not be a significant problem, it's just one of those little extras to consider.

There is also the problem that multiple freelist GROUPS, when mixed with big deletes, can result in lots of free blocks in the table, which can only be used by the GROUP that freed it.

And of course, one of the fringe effects of multiple freelists is that it can affect the perceived efficiency of special cases of sequence-based indexes (in pretty much the way I described for ASSM).

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

Nuno Souto wrote in message
<3d2d719e$0$14696$afc38c87_at_news.optusnet.com.au>...


>In article <1026384738.693.0.nnrp-01.9e984b29_at_news.demon.co.uk>, you said

>(and I quote):

>>

>> One thought, have you noticed that in a fairly

>> recent version of Oracle, (possibly 8.1.7) you

>> can modify freelists online without having to

>> rebuild the table ?

>>

>

>8.0, I think.  Of course it only applies to new blocks, won't do a thing

>for already existing ones.

>

>One thing: how come FREELISTS > 1 wastes disk space?  I thought that was

>the case only for INITRANS, using up some additional block header space.

>

>By how much?  I just changed freelists on quite a large number of tables

>that get a lot of inserts in one of my RAAF databases.  Re-loaded the

>tables and there was no increase in disk space that I could detect.   Did

>I miss something obvious?  8.1.7, this one.

>

>

>--

>Cheers

>Nuno Souto

>nsouto_at_optushome.com.au.nospam