Re: empty block vs no. freelist blocks

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:c76622thuhj2oeeec5lqf0nlrq6fokl8a2_at_4ax.com...
>>
>>What would cause such a high number of blocks on the freelist? Large
>>deletes?
>
>
> 4 Yes. However, likely they are not going to be re-used EVER, as
> Oracle doesn't keep searching for the most 'empty' block.
> This is resolved with ASSM.
>

If they are on the free list they can be used eventually if the space is needed. Oracle HAS to start at the top of the free list when searching for space. You may be thinking of cases where space is wasted because the block has been taken off the free list when it could be used for further inserts.

There are a couple of oddities that might explain this, but the most likely is simply a massive delete followed by a usage pattern that never needs that much space ever again.

The oddities include

    a bug in a much earlier version

    having multiple freelist groups - and managing to     get all the deleted blocks into one freelist group     and always doing the inserts in another.

As far as ASSM fixing things - one of the issues with ASSM is (was) that it would keep searching blocks pointlessly when they should have been marked as unavailable (full), with the effect that a single insert could do literally hundreds of gets on bitmap blocks and associated data blocks looking for a block that was empty enough.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html