Database Tables - Expert Oracle Database Architecture - page 359

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 HWM of an object will be found
on the FREELIST . The blocks that remain above the HWM will be used only when the FREELIST s are empty, at which
point Oracle advances the HWM and adds these blocks to the FREELIST . In this fashion, Oracle postpones increasing
the HWM 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, then configuring more than one FREELIST can have a major positive impact on performance
(at the cost of possible additional storage). Having sufficient FREELIST s for your needs is crucial.
FREELIST s 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 FREELISTS correctly. First create a
tablespace that uses MSSM. You must specify the SEGMENT SPACE MANAGEMENT MANUAL clause. This example creates an
MSSM tablespace (named mssm ) and then creates a table named T within that tablespace:

EODA@ORA12CR1> create tablespace mssm
2 datafile size 1m autoextend on next 1m
3 segment space management manual;

Tablespace created.

Now consider this relatively simple table:

EODA@ORA12CR1> create table t ( x int, y char(50) ) tablespace mssm;
Table created.

■ mssm in the preceding example is the name of a tablespace, not a keyword. You may replace it with the name
of any tablespace you have that uses manual segment space management.

Note

Using five concurrent sessions, we start inserting into this table like wild. If we measure the system-wide wait
events for block-related waits both before and after inserting, we will find large waits, especially on data blocks (trying
to insert data). This is frequently caused by insufficient FREELIST s on tables (and on indexes, but we'll cover that in
detail in the next chapter “Indexes”). I used Statspack for this—I took a statspack.snap , executed a script that started
the five concurrent SQL*Plus sessions, and waited for them to exit, before taking another statspack.snap . The script
these sessions ran was simply:

begin
for i in 1 .. 1000000
loop
insert into t values ( i, 'x' );
end loop;
commit;
end;
/
exit;