FREELISTS PCTFREE PCTUSED

FREELISTS
The FREELIST is where Oracle keeps tracks of blocks under the high water mark for
objects that have free space on them. Each object will have at least one FREELIST
associated with it and as blocks are used, they will be placed on or taken off of the
FREELIST as needed. It is important to note that only blocks under the high water mark of
an object will be found on the FREELIST. The blocks that remain above the high water
mark, will be used only when the FREELISTs are empty, at which point Oracle advances
the high water mark and adds these blocks to the FREELIST. In this fashion, Oracle
postpones increasing the high water mark for an object until it has to.
An object may have more than one FREELIST. If you anticipate heavy INSERT or
UPDATE activity on an object by many concurrent users, configuring more then one
FREELIST can make a major positive impact on performance (at the cost of possible
additional storage). As we will see later, having sufficient FREELISTs for your needs is
crucial.
Freelists can be a huge positive performance influence (or inhibitor) in an environment
with many concurrent inserts and updates. An extremely simple test can show the benefits
of setting this correctly. Take the simplest table in the world:
tkyte@TKYTE816> create table t ( x int );
and using two sessions, start inserting into it like wild. If you measure the system‐wide
wait events for block related waits before and after, you will find huge waits, especially on
data blocks (trying to insert data). This is frequently caused by insufficient FREELISTs on
tables (and on indexes but weʹll cover that again in Chapter 7, Indexes). For example, I set
up a temporary table:
tkyte@TKYTE816> create global temporary table waitstat_before
2 on commit preserve rows
3 as
4 select * from v$waitstat
5 where 1=0
6 /
Expert one-on-one Oracle
256
Table created.
to hold the before picture of waits on blocks. Then, in two sessions, I simultaneously ran:
tkyte@TKYTE816> truncate table waitstat_before;
Table truncated.
tkyte@TKYTE816> insert into waitstat_before
2 select * from v$waitstat
3 /
14 rows created.
tkyte@TKYTE816> begin
2 for i in 1 .. 100000
3 loop
4 insert into t values ( i );
5 commit;
6 end loop;
7 end;
8/
PL/SQL procedure successfully completed.
Now, this is a very simple block of code, and we are the only users in the database here.
We should get as good performance as you can get. Iʹve plenty of buffer cache configured,
my redo logs are sized appropriately, indexes wonʹt be slowing things down; this should
run fast. What I discover afterwards however, is that:
tkyte@TKYTE816> select a.class, b.count‐a.count count, b.time‐a.time time
2 from waitstat_before a, v$waitstat b
3 where a.class = b.class
4 /
CLASS COUNT TIME
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐
bitmap block 0 0
bitmap index block 0 0
data block 4226 3239
extent map 0 0
free list 0 0
save undo block 0 0
Expert one-on-one Oracle
257
save undo header 0 0
segment header 2 0
sort block 0 0
system undo block 0 0
system undo header 0 0
undo block 0 0
undo header 649 36
unused 0 0
I waited over 32 seconds during these concurrent runs. This is entirely due to not having
enough FREELISTs configured on my tables for the type of concurrent activity I am
expecting to do. I can remove all of that wait time easily, just by creating the table with
multiple FREELISTs:
tkyte@TKYTE816> create table t ( x int ) storage ( FREELISTS 2 );
Table created.
or by altering the object:
tkyte@TKYTE816> alter table t storage ( FREELISTS 2 );
Table altered.
You will find that both wait events above go to zero; it is that easy. What you want to do
for a table is try to determine the maximum number of concurrent (truly concurrent)
inserts or updates that will require more space. What I mean by truly concurrent, is how
often do you expect two people at exactly the same instant, to request a free block for that
table. This is not a measure of overlapping transactions, it is a measure of sessions doing
an insert at the same time, regardless of transaction boundaries. You want to have about as
many FREELISTs as concurrent inserts into the table to increase concurrency.
You should just set FREELISTs really high and then not worry about it, right? Wrong ‐ of
course, that would be too easy. Each process will use a single FREELIST. It will not go
from FREELIST to FREELIST to find space. What this means is that if you have ten
FREELISTs on a table and the one your process is using exhausts the free buffers on its list,
it will not go to another list for space. It will cause the table to advance the high water
mark, or if the tables high water cannot be advanced (all space is used), to extend, to get
another extent. It will then continue to use the space on its FREELIST only (which is empty
now). There is a tradeoff to be made with multiple FREELISTs. On one hand, multiple
FREELISTs is a huge performance booster. On the other hand, it will probably cause the
table to use more disk space then absolutely necessary. You will have to decide which is
less bothersome in your environment.
Expert one-on-one Oracle
258
Do not underestimate the usefulness of this parameter, especially since we can alter it up
and down at will with Oracle 8.1.6 and up. What you might do is alter it to a large number
to perform some load of data in parallel with the conventional path mode of SQLLDR.
You will achieve a high degree of concurrency for the load with minimum waits. After the
load, you can alter the FREELISTs back down to some, more day‐to‐day, reasonable
number, the blocks on the many existing FREELISTs will be merged into the one master
FREELIST when you alter the space down.

PCTFREE and PCTUSED
These two settings control when blocks will be put on and taken off the FREELISTs. When
used with a table (but not an Index Organized Table as weʹll see), PCTFREE tells Oracle
how much space should be reserved on a block for future updates. By default, this is 10
percent. What this means is that if we use an 8 KB block size, as soon as the addition of a
new row onto a block would cause the free space on the block to drop below about 800
bytes, Oracle will use a new block instead of the existing block. This 10 percent of the data
space on the block is set aside for updates to the rows on that block. If we were to update
them ‐ the block would still be able to hold the updated row.
Now, whereas PCTFREE tells Oracle when to take a block off the FREELIST making it no
longer a candidate for insertion, PCTUSED tells Oracle when to put a block on the
FREELIST again. If the PCTUSED is set to 40 percent (the default), and the block hit the
PCTFREE level (it is not on the FREELIST currently), then 61 percent of the block must be
free space before Oracle will put the block back on the FREELIST. If we are using the
default values for PCTFREE (10) and PCTUSED (40) then a block will remain on the
FREELIST until it is 90 percent full (10 percent free space). Once it hits 90 percent, it will be
taken off of the FREELIST and remain off the FREELIST, until the free space on the block
exceeds 60 percent of the block.
Pctfree and PCTUSED are implemented differently for different table types as will be
noted below when we discuss each type. Some table types employ both, others only use
PCTFREE and even then only when the object is created.
There are three settings for PCTFREE, too high, too low, and just about right. If you set
PCTFREE for blocks too high, you will waste space. If you set PCTFREE to 50 percent and
you never update the data, you have just wasted 50 percent of every block. On another
table however, 50 percent may be very reasonable. If the rows start out small and tend to
double in size, a large setting for PCTFREE will avoid row migration.

注明：摘自Expert One on One Oracle