Re: In praise of auto space management

I can vouch for the fact the FREELISTS only became dynamically modifiable in 8.1.7, because I was doing one of the first teaches of Performance Tuning after they'd upgraded from 8.1.5.... my usual routine was to explain freelist contention, do a quick 'alter table emp freelists 3' and watch it fall over. The class watched me fall over instead when on this particular occasion, the message 'table altered' popped up.

Of course, I hadn't even tried it on 8.1.6, so I can't be sure it didn't make it to that release. But it definitely wasn't there in 8.1.5.

Regards
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1026389310.15520.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> 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
>
>
Received on Thu Jul 11 2002 - 15:23:54 CDT