Re: Blocks in DBA_TABLES and DBA_SEGMENTS

ASSM - automatic segment space management. Each extent of a segment gets a botmap describing which blocks are available for inserts. When you do an insert, you get assigned to a bitmap more or less at random. Which means that your insert could end up being placed anywhere within the segment. Clearly, that's bad news, because if it ended up being placed in (say) the 10th extent, a full tablescan would have to wade through 9 empty extents before bumping into it.

Therefore, in 9i they introduced the idea of a low high watermark and a high high watermark (!). The HHWM is the same as the plain old HWM in earlier versions: it is the point at which full table scans stop. But each extent also has its own HWM (the LHWM) which tells the FTS to skip the remaining blocks in this extent and move to the next extent, because there is no further data in this extent.

Which means that yes, a FTS in 9i does not read all blocks below the HWM (ie, the HHWM), but ducks and dives its way around extents missing out as many blocks as it deems are irrelevant.

It's actually the same problem you get with multiple free lists in earlier versions: you end up scattering the data throughout the segment, instead of concentrating it up the front. At which point FTS have to do an awful lot more than they otherwise would have had to. But ASSM sorts that out, more or less (FTS have to read the bitmap blocks to work out which blocks to skip, so a FTS in ASSM segments still takes more work than non-ASSM ones, but much less work than they would have taken for a multi-freelist one).

Regards
HJR "Sean M" <smckeownNO_at_BACKSIESearthlink.net> wrote in message news:3CD1CC4E.683CAF3D_at_BACKSIESearthlink.net...
> "Howard J. Rogers" wrote:
> >
> > Dba_segments will show the total number of blocks allocated to a
segment,
> > used or unused. Blocks in dba_tables shows the blocks below the High
Water
> > Mark (or, rather more accurately in 9i, blocks which would be read by a
full
> > table scan). Empty_blocks shows you the number of blocks which are above
the
> > high water mark (or, again, in 9i, blocks which won't be read by a full
> > table scan).
>
> Quick question since I confess I haven't had a chance to dig this deep
> into 9i... what's the difference in the full-table scan behavior? (You
> seem to imply that in 9i Oracle won't necessarily have to read every
> block below the HWM as it did in previous releases, unless I read you
> incorrectly?).
>
> Genuinely curious,
> Sean
Received on Thu May 02 2002 - 18:38:32 CDT