Oracle Extents

An extent is a unit of database storage made up of logically contiguous data blocks.

只是逻辑连续的blocks id，实际组成block的物理block会分散在磁盘上

Extent是segment的扩展单位, 即一次扩展1个extent，extent不能跨数据文件

（一）Allocation of Extents

在创建segment时会分配一个initial extent(可能包含多个extent)

By default, the database allocates an initial extent for a data segment when the segment is created. An extent is always contained in one data file.

Although no data has been added to the segment, data blocks in the initial extent are reserved for this segment exclusively. The first data block of every segment contains a directory of the extents in the segment. Figure 12-17 shows the initial extent in a segment in a data file that previously contained no data.

 
在分配extent时ASSM会视情况format blocks

The blocks of a newly allocated extent, although they were free, may not be empty of old data.

In ASSM, Oracle Database formats the blocks of a newly allocated extent when it starts using the extent, but only as needed.

Note: This section applies to serial operations, in which one server process parses and runs a statement. The database allocates extents differently in parallel SQL statements, which entail multiple server processes.

（二）Deallocation of Extents

Segment中的free extent不会被其它段使用

In general, the extents of a user segment do not return to the tablespace unless you drop the object using a DROP statement. For example, if you delete all rows in a table, then the database does not reclaim the data blocks for use by other objects in the tablespace. You can also drop the segment using the DBMS_SPACE_ADMIN package.

但对于undo segment，oracle会定时收回空闲extents

In an undo segment, Oracle Database periodically deallocates one or more extents if it has the OPTIMAL size specified or if the database is in automatic undo management mode.

你可以手动来回收segment的free extent:

1. Use an online segment shrink to reclaim fragmented space in a segment. In general, data compaction leads to better cache utilization and requires fewer blocks to be read in a full table scan.
2. Move the data of a nonpartitioned table or table partition into a new segment.
3. Rebuild or coalesce the index.
4. Truncate a table or table cluster, which removes all rows. By default, Oracle Database deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. In Oracle Database 11g Release 2 (11.2.0.2), you can also use TRUNCATE with the DROP ALL STORAGE option to drop entire segments.
5. Deallocate unused space, which frees the unused space at the high water mark end of the database segment and makes the space available for other segments in the tablespace.

（三）Storage Parameters for Extents

Segment的extent分配可以在3个层级设置

1. Segment storage clause
2. Tablespace storage clause
3. Oracle Database default(详见create database语法)

1. Tablespace storage clause

Tablesapce级存储子句详见create tablespace语法，重点子句为：

EXTENT MANAGEMENT DICTIONARY/LOCAL [AUTOALLOCATE| UNIFORM SIZE xx m]

LOCAL管理的表空间会有两个扩展extent选项autoallocat与uniform size

1. For uniform extents, you can specify an extent size or use the default size of 1 MB. All extents in the tablespace are of this size. Locally managed temporary tablespaces can only use this type of allocation.
2. For automatically allocated extents, Oracle Database determines the optimal size of additional extents. Extent大小会随着segment的增长而自动变大，会选择下面之一的大小：64K，1M，8M以及64M，即同一segment下extent大小是不同的

1. Segment storage clause

对于Local表空间很多之前用于扩展extent的选项有不同解释了，它通过这些选项共同决定initial大小，而扩展extent的属性完全由local的autoallocate/manual来设定了

The storage_clause is interpreted differently for locally managed tablespaces. For locally managed tablespaces, Oracle Database uses INITIAL, NEXT, PCTINCREASE, and MINEXTENTS to compute how many extents are allocated when the object is first created. After object creation, these parameters are ignored.

DBA_TABLES中有INITIAL_EXTENT，NEXT_EXTENT等相关列，这里的inital_extent表示初始所有extents的总大小，如果想查看每个extent大小通过dba_extents:

select INITIAL_EXTENT,NEXT_EXTENT from DBA_TABLES where OWNER='' AND TABLE_NAME='';

SELECT EXTENT_ID,BYTES FROM dba_extents where OWNER='' AND SEGMENT_NAME='';

对于temporary segments永远使用tablespace级设置

The storage_clause lets you specify how Oracle Database should store a permanent database object. Storage parameters for temporary segments always use the default storage parameters for the associated tablespace.

1. INITIAL

Specify the size of the first extent of the object. Oracle allocates space for this extent when you create the schema object.

如果表空间为local+autoallocate: oracle会用inital值设置初始化多个少extents, 单个extent大小用最接近inital大小的64k,1M,8M与64M之一

With AUTOALLOCATE extent management, Oracle uses the INITIAL setting to optimize the number of extents allocated. Extents of 64K, 1M, 8M, and 64M can be allocated. 

During segment creation, the system chooses the greatest of these four sizes that is equal to or smaller than INITIAL, and allocates as many extents of that size as are needed to reach the INITIAL setting. For example, if you set INITIAL to 4M, then the database creates four 1M extents.

如果表空间为local+uniform: oracle会用inital值与uniform大小来设置初始多少个extents

For UNIFORM extent management, the number of extents is determined from initial segment size and the uniform extent size specified at tablespace creation time. For example, in a uniform locally managed tablespace with 1M extents, if you specify an INITIAL value of 5M, then Oracle creates five 1M extents.

因为不能带队最终设置大小可能会比inital值大一些

Consider this comparison: With AUTOALLOCATE, if you set INITAL to 72K, then the initial segment size will be 128K (greater than INITIAL). The database cannot allocate an extent smaller than 64K, so it must allocate two 64K extents. If you set INITIAL to 72K with a UNIFORM extent size of 24K, then the database will allocate three 24K extents to equal 72K.

如果是dictionary管理，初始化extent为5blocks, 分配多个extent

In dictionary managed tablespaces, the default initial extent size is 5 blocks, and all subsequent extents are rounded to 5 blocks.

1. NEXT

Specify in bytes the size of the next extent to be allocated to the object

local管理表空间会忽略用户设置的next值，而是按表空间设置

In locally managed tablespaces, any user-supplied value for NEXT is ignored and the size of NEXT is determined by Oracle if the tablespace is set for autoallocate extent management. In UNIFORM tablespaces, the size of NEXT is the uniform extent size specified at tablespace creation time.

dictionary管理表空间extent默认值为5 blocks, 最小为1 block, 会按next分配多个extent

In dictionary-managed tablespaces, the default value is the size of 5 data blocks. The minimum value is the size of 1 data block. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks. For values greater than 5 data blocks, Oracle rounds up to a value that minimizes fragmentation.

1. PCTINCREASE

Local管理表空间只会在创建时用此值来决定initial大小，之后分配extent会忽略

In locally managed tablespaces, Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size and ignores this parameter during subsequent space allocation.

Dictionary管理表空间会用这个值决定下次分配extent增长大小

In dictionary-managed tablespaces, specify the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value depends on your operating system. Oracle rounds the calculated size of each new extent to the nearest multiple of the data block size.

1. MINEXTENTS

在local管理表空间初始段大小为INITIAL * MINEXTENTS

In a locally managed tablespace, MINEXTENTS is used to compute the initial amount of space allocated, which is equal to INITIAL * MINEXTENTS. Thereafter this value is set to 1, which is reflected in the DBA_SEGMENTS view.

在dictionary管理表空间此值用于设置初始化分配extent个数

In a dictionary-managed tablespace, MINEXTENTS is simply the minimum number of extents that must be allocated to the segment.

If the MINEXTENTS value is greater than 1, then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE storage parameters.

When changing the value of MINEXTENTS by specifying it in an ALTER statement, you can reduce the value from its current value, but you cannot increase it. Resetting MINEXTENTS to a smaller value might be useful, for example, before a TRUNCATE ... DROP STORAGE statement, if you want to ensure that the segment will maintain a minimum number of extents after the TRUNCATE operation.

1. MAXEXTENTS

只用于dictionary表空间，表示segment最多分配extents个数

This storage parameter is valid only for objects in dictionary-managed tablespaces. Specify the total number of extents, including the first, that Oracle can allocate for the object. The default value depends on your data block size.

1. MAXSIZE

The MAXSIZE clause lets you specify the maximum size of the storage element. For LOB storage, MAXSIZE has the following effects:

If you specify RETENTION MAX in LOB_parameters, then the LOB segment increases to the specified size before any space can be reclaimed from undo space.

If you specify RETENTION AUTO, MIN, or NONE in LOB_parameters, then the specified size is a hard limit on the LOB segment size and has no bearing on undo retention.

Use the UNLIMITED clause if you do not want to limit the disk space of the storage element. This clause is not compatible with a specification of RETENTION MAX in LOB_parameters. If you specify both, then the database uses RETENTION AUTO and MAXSIZE UNLIMITED.

1. FREELISTS

用于dictionary以及local+manual表空间，多个freelists可减少insert争用

In tablespaces with manual segment-space management, Oracle Database uses the FREELISTS storage parameter to improve performance of space management in OLTP systems by increasing the number of insert points in the segment.

In tablespaces with manual segment-space management, for objects other than tablespaces and rollback segments, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index.

The maximum value of this parameter depends on the data block size.

This clause is not valid or useful if you have specified the SECUREFILE parameter of LOB_parameters. If you specify both the SECUREFILE parameter and FREELISTS, then the database silently ignores the FREELISTS specification.

1. FREELIST GROUPS

用于RAC环境下dictionary以及local+manual表空间，一般设置为实例个数。通过把segment的free block分区到不同groups, 从而避免了实例间的segment元信息交换

In tablespaces with manual segment-space management, Oracle Database uses the value of this storage parameter to statically partition the segment free space in an Oracle Real Application Clusters environment. This partitioning improves the performance of space allocation and deallocation by avoiding inter instance transfer of segment metadata.

Oracle uses the instance number of Oracle Real Application Clusters (Oracle RAC) 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.

This clause is not valid or useful if you have specified the SECUREFILE parameter of LOB_parameters. If you specify both the SECUREFILE parameter and FREELIST GROUPS, then the database silently ignores the FREELIST GROUPS specification.

1. OPTIMAL

The OPTIMAL keyword is relevant only to rollback segments. It specifies an optimal size in bytes for a rollback segment.

Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.

The value of OPTIMAL cannot be less than the space initially allocated by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size.

Specify NULL for no optimal size for the rollback segment, meaning that Oracle never deallocates the extents of the rollback segment. This is the default behavior.