FREELIST和FREELIST GROUPS簡介

Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02

The storage_clause lets you specify how Oracle should store a database object. Storage parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used.

FREELIST GROUPS
Specify the number of groups of free lists for the database object you are creating. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.

Each free list group uses one database block. Therefore:

If you do not specify a large enough value for INITIAL to cover the minimum value plus one data block for each free list group, then Oracle increases the value of INITIAL the necessary amount.
If you are creating an object in a uniform. locally managed tablespace, and the extent size is not large enough to accommodate the number of freelist groups, then the create operation will fail

Restriction on FREELIST GROUPS
You can specify the FREELIST GROUPS parameter only in CREATE TABLE, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, and CREATE INDEX statements.


FREELISTS
For objects other than tablespaces, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If you specify a FREELISTS value that is too large, then Oracle returns an error indicating the maximum value.



Restriction on FREELISTS
You can specify FREELISTS in the storage_clause of any statement except when creating or altering a tablespace or rollback segment.



Example

SQL>  create table t1 (a int,b char(16));

Table created.

SQL>  select owner,segment_name,freelists,freelist_groups from dba_segments where segment_name='T1';

OWNER           SEGMENT_NAME               FREELISTS FREELIST_GROUPS
--------------- ------------------------- ---------- ---------------
PERF            T1                                 1               1

SQL> alter table t1 storage (freelists 5);

Table altered.

SQL> select owner,segment_name,freelists,freelist_groups from dba_segments where segment_name='T1';

OWNER           SEGMENT_NAME               FREELISTS FREELIST_GROUPS
--------------- ------------------------- ---------- ---------------
PERF            T1                                 5               1

SQL>

SQL> create table t2 (a int,b char(16)) STORAGE (freelists 2 freelist groups 4);

Table created.

SQL>  select owner,segment_name,freelists,freelist_groups from dba_segments where segment_name='T2';

OWNER           SEGMENT_NAME               FREELISTS FREELIST_GROUPS
--------------- ------------------------- ---------- ---------------
PERF            T2                                 2               4



Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02

segment_management_clause
The segment_management_clause is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle should track the used and free space in the segments in the tablespace using free lists or bitmaps.

MANUAL
Specify MANUAL if you want Oracle to manage the free space of segments in the tablespace using free lists.

AUTO
Specify AUTO if you want Oracle to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, then Oracle ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management.

To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT column of the DBA_TABLESPACES or USER_TABLESPACES data dictionary view.

--------------------------------------------------------------------------------
Notes:
If you specify AUTO, then:

If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks, given the database block size.
If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 1M.

--------------------------------------------------------------------------------

Restrictions on Automatic Segment-space Management
You can specify this clause only for permanent, locally managed tablespace.
You cannot specify this clause for the SYSTEM tablespace.


Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02

segment header
If the contention is on the segment header, then this is most likely freelist contention.

Automatic segment-space management in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters. If possible, switch from manual space management to automatic segment-space management.

The following information is relevant if you are unable to use automatic segment-space management (for example, because the tablespace uses dictionary space management).

A freelist is a list of free data blocks that usually includes blocks existing in a number of different extents within the segment. Blocks in freelists contain free space greater than PCTFREE. This is the percentage of a block to be reserved for updates to existing rows. In general, blocks included in process freelists for a database object must satisfy the PCTFREE and PCTUSED constraints. Specify the number of process freelists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.

To find the current setting for freelists for that segment, run the following:

SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;


Set freelists, or increase of number of freelists. If adding more freelists does not alleviate the problem, then use freelist groups (even in single instance this can make a difference). If using Oracle Real Application Clusters, then ensure that each instance has its own freelist group(s).