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:
Column | Description |
Owner | The schema this table belongs to |
Table | Name of the table |
AvgRowLen | Average Size of each table row (in bytes) |
PctUsed | Table settings, adjustable via the
ALTER TABLE command |
PctFree |
FreeLists | Available freelists for this table |
AvgFreeSpace | Average size (bytes) available per block |
BlockSize | Block 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.
|