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).

freelist  group = 1 的时候，freelist 都放在 segment  header 里面
当freelist group = n , n> 1 的时候
freelist 从 segment  header 里面分离出来，存储在单独的块里面，每个freelist
group 一个block，也就是真正的数据的存储就从 1(for  segment header) + n (for
freelist  group ) 块之后的块开始

对于频繁插入,特别是多个实例同时并发插入同一块时最好建立freelist  group(当然是本地管理表空间+自动段管理是最好的)

可以用下面的语句来查系统中INSERT语句的频率
SELECT SQL_TEXT, DECODE(COMMAND_TYPE,2,'INSERT'),EXECUTIONS
FROM V$SQL
WHERE COMMAND_TYPE = 2
ORDER BY EXECUTIONS;

COMMAND_TYPE
~~~~~~~~~~~~
ROLLBACK : 45
SAVEPOINT: 46
ALTER : 42
COMMIT : 44
TRUNCATE : 85 (!)
INSERT : 2
SELECT : 3
UPDATE : 6
DELETE : 7


SELECT SQL_TEXT, DECODE(COMMAND_TYPE,2,'INSERT',6,'UPDATE',7,'DELETE'),EXECUTIONS
FROM V$SQL
WHERE COMMAND_TYPE IN (2,6,7)
ORDER BY EXECUTIONS;

SELECT SUBSTR(SQL_TEXT,1,80), DECODE(COMMAND_TYPE,2,'INSERT',6,'UPDATE',7,'DELETE'),EXECUTIONS
FROM STATS$SQL_SUMMARY
WHERE COMMAND_TYPE IN (2,6,7)
ORDER BY EXECUTIONS;

有时频繁插入使用序列主键值的表，那么会都访问主键索引的右叶索引块，造成热块，所以也可能使用类似hash partitioned该对象。
或者将设置小的块


表和索引 的freelist  group是不同的,由于表在同时插入时,可以使用不同的 块存储数据,可能不会造成频繁访问相同的freelist冲突,而索引
如果键值相似的话,就必须插入到同一块

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/751371/viewspace-606933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751371/viewspace-606933/