Re: In praise of auto space management

"Howard J. Rogers" wrote:

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

I discovered ORDER BY in views the same way. It does not leave a feeling one quickly forgets.

"Well it used to not work ... really"

Daniel Morgan Received on Thu Jul 11 2002 - 16:01:08 CDT