Delay Section Function of Oracle 11gR2

Delay Section Function of Oracle 11gR2

In versions earlier than 11gR2, when creating a table, the segment space is automatically allocated, which has several drawbacks:

1. space needs to be allocated during initial table creation, which naturally takes some time. If multiple tables are initialized, this effect will be magnified.

2. If many tables are not required for a period of time at the beginning, the space will be wasted.

For this reason, there is a new feature from 11gR2, called the delay segment, that is, the space of the delay allocation segment. Simply put, by default, the physical space allocation of the table (as well as the index and LOB) is postponed until the first record is inserted into the table. That is, when the actual data is inserted into the table, the space is allocated for each object initialization. 11.2.0.1 does not support partition tables, bitmap join indexes, and domain indexes. In version 11.2.0.2, partitioned tables are supported.

Note: The premise for using features is that the COMPATIBLE parameter is 11.2.0 or later.

 

Lab:

1. First, let's take a look at the allocation of table space in versions earlier than 11g:

 

SQL> select version from v $ instance;
VERSION
-----------------
10.2.0.4.0 SQL> create table tbl_seg (reg_id number, reg_name varchar2 (200 ));
Table created.
SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1 SQL> select segment_name, segment_type, bytes, blocks, extents, initial_extent, next_extent, max_extents
2 from user_segments where segment_name = 'tbl _ seg ';
SEGMENT_NAME SEGMENT_TYPE bytes blocks extents INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
--------------------------------------------------------------------------------------------------------------------------------------------
TBL_SEG TABLE 65536 8 1 65536 2147483645

 

As you can see, after the TBL_SEG table is created, user_segments and user_extents have records, indicating that the TBL_SEG has been allocated space for segments and zones. The size of one EXTENTS is 64 K.

2. Next, let's take a look at the 11g allocation:

 

[Oracle @ riserver1 ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.1.0 Production on Mon Aug 4 07:53:24 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select version from v $ instance;
VERSION
-----------------
11.2.0.1.0 SQL> create table tbl_seg (reg_id number, reg_name varchar2 (200 ));
Table created.

SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

 

Why is it still allocated?

IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tablesare not supported. tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.

The reason is explained here. The table in SYS cannot use a delay segment. Therefore, the segment space is allocated immediately when the table is created.

Create a table using a non-system account:

 

SQL> create user user01 identified by user01; User created. SQL> grant connect, resource to user01;
Grant succeeded. [oracle @ riserver1 ~] $ Sqlplus user01/user01
SQL * Plus: Release 11.2.0.1.0 Production on Mon Aug 4 08:32:06 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user
USER is "USER01"
SQL> select username, default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------
USER01 users SQL> create table tbl_seg (reg_id number, reg_name varchar2 (200 ));
Table created.

SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';
COUNT (*)
----------
0

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';
COUNT (*)
----------
0 at this time, we can see that after the table is created, the segments and zones are not allocated space, then insert the record: SQL> insert into tbl_seg values (1, 'brdstn '); 1 row created.

SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';

COUNT (*)
----------
1

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';

COUNT (*)
----------
1
We can see that the segments and zones have been allocated space. The user_segments and user_extents tables have records corresponding to TBL_SEG. 3. Disable delay segments:

You can disable delay segments. whether to use the delay segments is defined by the DEFERRED_SEGMENT_CREATION parameter. This parameter can be modified at the session level. If you want to completely delete the delay segments, you can modify them in the spfile, it will take effect this time and next time, for example:

SQL> alter session set DEFERRED_SEGMENT_CREATION = false; Session altered.

SQL> create table tbl_seg (reg_id number, reg_name varchar2 (200 ));
Table created.

SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1
4. Use the segment creation clause:

Even if the latency SEGMENT is disabled, you can use segment creation to specify whether to use the latency SEGMENT when creating a table. For example:

 

SQL> create table tbl_seg (
2 reg_id number,
3 reg_name varchar2 (200 ))
4 segment creation immediate;
Table created.

SQL> select count (*) from user_segments where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

SQL> select count (*) from user_extents where segment_name = 'tbl _ seg ';
COUNT (*)
----------
1

 

This shows that the segments and zones are allocated immediately. If you use segment creation deferred, the latency SEGMENT function is used.
5. view the changes from USER_TABLES:

11g USER_TABLES has more fields than the previous version. One of them is SEGMENT_CREATED (VARCHAR2 (3), so that you can know whether a segment has been allocated space.

 

6. Impact of import and export:

In 11.2.0.1, if a table has not been assigned segments, the table with unallocated segments will not be exported when exp is used for export. The solution is to use allocate extent to manually ALLOCATE segments before export, you can also use the data pump expdp to export tables with unallocated segments.

Summary:

The benefits of the new features of this delay segment are obvious, and the disadvantages are also obvious. As to whether to use the new features, you need to decide based on the actual business, this is also the purpose of Oracle to disable the delay range option.

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian