Re: What space of a Oracle block is being used?

Because ASSM works by sticking a bitmap at the beginning of each extent of a segment. That bitmap's job is to describe the fullness/emptiness of blocks, so we know where to stick the next insert.

But each bitmap takes up space in the segment, which means the segment gets bigger. More blocks = more I/O.

A full table scan has to consult the bitmap for each extent to see where, in each extent, it should scan for records. (each extent, in other words, now keeps track of its own 'high water mark', as well as there still being the real high water mark. In the trade, we talk about the 'low high watermarks' and the 'high high watermarks'). If your segment was simply a traditional freelist segment, we'd read the segment header block (1 block) to find out the location of the High high watermark, and scan onwards to that point. In ASSM segments, we read bitmap1, scan to its low highwater mark. Read bitmap2, scan to *it's* LHWM. Read bitmap3, and so on.

That read-scan-read-scan is what makes an ASSM-FTS slower than a Freelists-FTS. The bitmap blocks have to be stored in the buffer cache, too, taking up valuable memory real estate.

However, if you have multiple freelists or freelist groups, the read-scan-read-scan method is a vast improvement: you cut out scanning lots of completely empty blocks that are read in multiple-freelist-managed segments because those sort of segments only have a single (High) high watermark indicating where the scan should stop.

So, in descending order of Full Table Scan speed, we have:

Single freelist segments
ASSM segments
Multiple freelist segments

Therefore, unless you have multiple freelists (which would be the case in Parallel Server/RAC when freelist contention can be a nightmare), you are paying a read penalty on full scans for no real benefit.

Regards
HJR "m. fowler" <mfowler_at_dot.co.pima.az.us> wrote in message news:e9c5a67.0208130549.5c0c8a6c_at_posting.google.com...
> > That's if you want to use ASSM, of course. Which you would be mad to do,
> > unless freelist contention is a big issue in your life.
> >
> > It's not. It's bloody awful for full table scans
> .
> Could you explain why - thanks.
Received on Tue Aug 13 2002 - 14:46:30 CDT