Re: NUM_FREELIST_BLOCKS

Hi Chuck,

The NUM_FREELIST_BLOCKS shows the number of all blocks on all the available freelists.

If this number is high, it means there are plenty of blocks available for insert. This could be because there has been delete activity or if you have vast numbers of freelists, each one having several free blocks available to them. A large number may also signify that the HWM is unnecessarily high (but then again it might not, depends on future growth of table).

I'm not entirely sure what you mean by walking the entire free list. If it's what I think you mean, it only needs to do so if the row you're trying to insert doesn't fit into the free portion of the initial blocks on the particular freelist you've been assigned to. This can occur for large rows or if the pctused value is set too high in relation to the pctfree value.

In terms of contention, this is related to how you set up your freelist groups and freelists. Low numbers of groups can lead to contention for the freelist block (each group is assigned it's own set of freelists in its own block). Low numbers of freelists can lead to contention for "hot blocks" that concurrent inserts all want access to (buffer busy waits are an indication of all this).

Note that 9i automatic segment space management facility is designed to reduce all these issues (and eliminate the need to specify freelist groups, freelists and pctused). The cost though is the "wasted" space occupied by the bitmap blocks that point to the blocks with levels of free space.

Cheers

Richard
"Chuck Hennet" <hennetc_at_ptd.net> wrote in message news:16cd5346.0207291131.37d31fa7_at_posting.google.com...
> Does NUM_FREELIST_BLOCKS from DBA_TABLES show the number of blocks on
> the segment's master free list? What if this number is quite high in
> the hundreds or thousands? As each user maintains their own process
> free list, is there ever a time when you must walk the entire master
> free list? These tables all have LONG columns and PCT_FREE=10 and
> PCT_USED=40. Could a high number here lead to contention for the
> segment header?
>
> Thanks.
Received on Tue Jul 30 2002 - 02:04:13 CDT