What is that "FreeList Contention" and what effects does it have?

This is best explained using an example. Imagine you are going to insert a large amount of rows into a table which is already frequently used. For this Oracle must find blocks with free space in them. That is done by consulting the Freelists.

Which block comes on a freelist depends on the settings of PCTFREE and PCTUSED for the table in question: Inserts may use all space except PCTFREE in a block, the remaining space is reserved for Updates. So if there's less than PCTFREE space available in a block, this block is removed from the freelist. Now, if an Update finds a block filled less than PCTUSED, this block is moved to a freelist again.

Now back to our example. Let's assume PCTFREE is set to 5, and PCTUSED to 90. Now comes our Insert, finds a lot of blocks that are filled to about 75% - but the remaining 20% are too small to keep an entire row. So in order to not having to inspect this block on the next insert, it is removed from the freelist.

Next comes a large Update. As it goes, it touches a lot of the blocks that just have been removed from the freelist and finds, that the used space is less than PCTUSED, so the block comes back to the freelist. So on the next Insert, they all will be inspected again and probably moved back from the freelist again. All in all, a useless overhead - and unnecessarily making the transaction using more time and resources.

In our example it is very clear that the span between PCTFREE and PCTUSED is too small and the described problem is subject to occur quite often.

Explanation of the table columns

The table in the report lists up the key data for our problem, one row for each possibly affected table. Like already may be obvious by above description, we list only tables where there's more space available than defined by PCTFREE and less space used than permitted by PCTUSED (so the block will be on the freelist for inserts) plus the average row length is to large to fit into the gap.
Following is an explanation of the columns:

ColumnDescription
OwnerThe schema this table belongs to
TableName of the table
AvgRowLenAverage Size of each table row (in bytes)
PctUsedTable settings, adjustable via the ALTER TABLE command
PctFree
FreeListsAvailable freelists for this table
AvgFreeSpaceAverage size (bytes) available per block
BlockSizeBlock size used for the tablespace

The result set

If you get no results within the table, the reasons can be different:

  • Your tables are not analyzed.
    In order to obtain the needed data for the above described evaluation, we need to access the tables statistics in the all_tables system view. As a recommendation, ESTIMATE the statistics at least for possible candidates.
  • Your problem is a different one.
    Since the above described scenario is just one possible reason for buffer busy waits (and other waits that may point to a freelist contention), you may have to search for the cause of your problem at another point.
  • This problem affects almost nobody.
    Although the above scenario (which I found in some tuning book) sounds very reasonable, I didn't find any database where this query had any result (if one of your databases is the exception, please let me know!!!). So possibly, this problem is just of academical nature.