FreeList

[ckwan]

Dear all,
I am a bit confuse on the freelist explained in various references. Freelists are lists of the free block that are available for insertion of new rows into an object. At least one freelist is maintained in each segment header.

Question: Why we require freelist since we already have data block which using the PCTUSED and PCTFREE to control the space allocation in the block ? So, what is freelist use for ?

When the space available reaches the PCTFREE, new rows are no longer inserted into that block, and the block is removed from the FREELISTS.

Let us analyze this statement - Is this mean that after the PCTFREE is reached, No rows are allowed to insert into this block and have to looks jor the adjacent free block's freelist, right?

Thanks for any feedback on this....

[grjohnson]

Say you have a table with PCTFREE 5, PCTUSED 60.

The block stays on the freelist until 95% full. It is then taken OFF the freelist and only UPDATES are allowed to be made to the block. The block stays OFF the freelist UNTIL the block decrease to UNDER 60% used, then the block is placed back onto the freelist to be used for inserting again.

Hope that's a little clearer.

Cheers

[ckwan]

Thanks for the reply. I have another question regarding this freelist.

If the Freelist is reserves for INSERT, which area is for UPDATE ? PCTFREE, right ? Why the INSERT have a 'special' space whereas not for the UPDATE ? and reason ?


Why not we call the Percentage Free list rather than PCTUSED ?

Sorry for 'stupid' question, but I am really need to get a clear picture about this.

[grjohnson]

Mate, there are no stupid questions.

I suppose, when you are updating a specific row in a block, Oracle knows that the block can be updated, and if it doesn;t have enought room to update into that specific block, chaining occurs.

Basically the are called different thing primarily becasue they have different functions. I mean a PCTUSED determines when to the BLOCK is brought back onto the freelist. The freelist keeps track of available blocks that can accept inserts. If you have concurrent inserts the benefit of having multiple FRESSLISTS is that each process can use a different freelist.

Hope this helps,

Cheers

[julian]

If you do not create your your tables or indexes or clusters with free lists, then you are left with the Master list only. All processes use the Master free list during insert and update operatoions to locate blocks with space for new data. Thes procesess update the Master free list by removing blocks when their free space fells below PCTFREE and adding them back when when their used space fells below PCTUSED. All these updtes are writen to the segment header.

Now, when many processes insert or update at the same time on a given table, they content for the Master free list. You can avoid that by creating more free list when creating the table for example.

Each time the HWM for a segment is encreased 5*(1+number_of free_lists) blocks are transfered. You should not waorry much about that because eventually these blocks will be used.

You can create your table also with FREELIST GROUPS but this will impact on youur DB only when it is multi-instanced.

[Sureshy]

Hi Julian.

So you are saying that freelist groups are only used as part of oracle parallel server ?

Thanks

Suresh

[julian]

Yes, although you may use them if you wish in a single instance DB. But they will be of no help.