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

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *