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

Howard J. Rogers <howardjr2000_at_yahoo.com.au> wrote: [inserts favour ASSM]
>Now for the selects:

[22% impact for ASSM]

>Repeat runs without clearing the cache in between yielded this:

[17% impact for ASSM]

>How about computing statistics (which induces a full scan, amongst other
>things)?

[5% impact for ASSM]

>One more point for freelists.
>
>And the results of that analyzing?
>
>TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_SPACE
>------------------------------ ---------- ------------ ----------
>ASSMTBL 25921 703 897
>NOASSMTBL 25734 889 857
Less than 1% more blocks

Curious - why the huge performance impact for such a small space impact?

>The ASSM blocks are emptier, and there are more of them (hence the longer
>scan times, I guess). ASSMTBL only has 64 rows more than NOASSMTBL, so that
>can't explain the extra 187 blocks (this is an 8K system, so that's an
>additional 1.5Mb).
>
>Based on these results, the differential is only likely to get worse when
>there are multi-tens of millions of rows.
>
>I'd be interested if you (and others) could run exactly the same tests and
>report back. This is 9i R2 on Windows 2000, but I'll do a Solaris test
>tomorrow.
>
>But I'll shut up in the meantime, to the extent of saying that ASSM for
>small tables (anything up to half a million rows, say) probably makes no
>discernible performance difference than non-ASSM.

Thanks for doing the tests. I need to do more of these. No time right now of course, but I hope to be receiving a shipment of round tuits at some point.

Few notes on things I want to test:

The manuals talk about ASSM being particularly good for updates where the row size changes radically.

Comparison of large insert/delete cycles on freelist and ASSM tables. ASSMs shouldn't suffer from the impact of all blocks being returned to one freelist.

Impact (if any) on index range scan.

Storage overhead on very large segments.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/