Re: Freelists

This used to be a big problem with long
columns in v7 - I think the setting was
about 3 blocks dropped off the free list for each attempted insert - then the HWM was advanced for 5 - even if this meant
a new extent was allocated.

You can still see the settings in the init.ora parameters with name like

    _walk_free_list

--
Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




Mark D Powell wrote in message
<178d2795.0108100454.5fa06d0f_at_posting.google.com>...
>"Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message
 news:<QTOc7.9766$zs.55688_at_news11-gui.server.ntli.net>...


>> "Mark D Powell" <mark.powell_at_eds.com> wrote in message

>> news:178d2795.0108080539.3bf61f2e_at_posting.google.com...

>> > Mick, I can not claim to 100% sure but I believe that when Oracle goes

>> > to insert a row into a block it checks to see if the block will have

>> > less than pctfree free space remaining after the insert.  If so, the

>> > block is removed from the free list at that time, and the rdbms looks

>> > to see if the row will fit in the next block on the free list.  There

>> > may be some variation to this when long/LOB columns are involved.

>> >

>> > -- Mark D Powell --

>>


>> Does this imply that if I insert an unusually large row I could

 effectively


>> obliterate the free list leading to a large amount of wasted space?

>
>I believe that after so many attempts that Oracle graps a free block
>from above the HWM.  That is Oracle will only read so many blocks from
>the free list to find room; once the rdbms has tried x times and the
>row has not been inserted then the rdbms gets an unused block moving
>the HWM.  So you should not exaust the free list but yes unusually
>long rows in a table of normally shorter rows can have a disruptive
>influence on space utilization.
>
>-- Mark D Powell --