11g deferred_segment_creation特性

11g中的 deferred_segment_creation 延迟段创建特性，在CREATE TABLE DDL执行时实际不会在指定的表空间上生成segment ，而会延迟到实际有第一次INSERT或其他方式加载数据后才会产生segment。

该deferred_segment_creation特性的优点是：

1. 当需要创建大量表时可以节约时间

2. 当系统中存在大量空表/空分区时可以节约空间

该deferred_segment_creation特性的缺点是：

1. 由于要在第一次INSERT或其他加载数据方式时才产生segment段，而段的创建需要在表空间上分配空间allocate space，若短期内

大量空表存在插入的需求，则可能在短期内出现空间分配争用

2. deferred_segment_creation引入了少量的BUG.

以下是11.2.0.3上存在的一些bug,部分在psu或者bp中修复了：

NB Bug Fixed Description

15866428 11.2.0.4, 12.1.0.0 ORA-14766 / ORA-14403 during concurrent partition maintenance

14252187 12.1.0.0 ORA-600 [qesmaGetTblSeg1] from deferred segment creation in RAC

13986244 11.2.0.3.BP14, 11.2.0.4, 12.1.0.0 Various ORA-600 seen with deferred segment creation in RAC

13611310 12.1.0.0 Parallel DML with LOBs fails with ORA-7445 [qesmaGetFromLocalOrQCCache]

12614714 11.2.0.4, 12.1.0.0 ORA-1950 occurs when executing DML after EXCHANGE PARTITION and DROP USER

13649031 11.2.0.3.4, 11.2.0.3.BP06, 11.2.0.4, 12.1.0.0 ORA-10637 occurs on SHRINK of a partitioned table with deferred segments

13497523 11.2.0.3.BP15, 11.2.0.4, 12.1.0.0 Errors from SQLLDR loads into non-partitioned tables with deferred segment creation

* 13326736 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3.3, 11.2.0.3.BP05, 11.2.0.4, 12.1.0.0 Dictionary corruption / ORA-959 due to DROP TABLESPACE

12535346 11.2.0.3.3, 11.2.0.3.BP07, 12.1.0.0 ORA-7445 [kxccexi] using referential integrity constraints with deferred segments or interval partitions

12358753 12.1.0.0 INDEX_STATS has wrong values for ANALYZE of deferred segmentindexes

11930350 12.1.0.0 Deadlock / undetected FK violation from DML on REFERENCE partitioned table

建议：

1. 对于存在较多空表或空分区且存在空间压力的，对性能、响应时间没有太高要求的系统可以考虑使用该特性

2. 对对性能、响应时间有较高要求的库建议关闭该特性，deferred_segment_creation=false

Comments