Re: In praise of auto space management

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news: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.

(Hugely simplified scenario coming up). Say you have a table of 10 useable blocks. Freelists 2. Essentially, one freelist handles the first 5 blocks, the second handles blocks 6 to 10.

Do an insert. You get assigned to one of the free lists, and Lo! your new record is inserted into block 6 -because you got assigned to freelist 2. Where is the High Water Mark for this segment? -in block 6 (actually, since it's moved in steps of 5 blocks, it's in block 10, but I said this was a bit of a simplification). Therefore, to read this table with a full scan, you have to read 6 times the blocks you would have read if there had been only 1 freelist (where your insert would have been sent to block 1).

In between the segment header, and the actual table data, there are 5 completely empty blocks. Which all get scanned during Full Table scans. Hence freelists > 1 wastes space, and slows down FTS.

ASSM would suffer from the same issue, except that each bitmap tracks a Low High Water Mark, so we would know not to have to scan the empty 5 blocks. We'd still have to consult the bitmap to find out where the LHWM is, though, so even in ASSM, full scans do more work than they would have to do in single-freelist-managed segments.

My usual advice is: if you've got really bad freelist contention, ASSM is a wonderful cure. Under any other circumstances, it's a really bad idea.

Regards
HJR
>
> 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:34:57 CDT