Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Deferred Segment Creation (Segment Creation On-Demand) in Oracle 11gR2

Deferred Segment Creation (Segment Creation On-Demand) in Oracle 11gR2

In the post we will look at the Deferred Segment Creation feature in Oracle 11g R2. In Oracle Database 11g Release 2, when creating a non-partitioned heap-organized table in a locally managed tablespace, table segment creation is deferred by default until the first row is inserted. In addition creation of segments is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION set to TRUE. This parameter can be set via ALTER SYSTEM or ALTER SESSION commands at instance or session level respectively. To enable deferred segment creation, compatibility must be set to ‘11.2.0’ or higher. You can disable deferred segment creation by setting the initialization parameter DEFERRED_SEGMENT_CREATION to FALSE. The new clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE are available for the CREATE TABLE statement. These clauses override the setting of the DEFERRED_SEGMENT_CREATION initialization parameter. The advantages of this new space allocation method are:

• A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

         • The application installation time is reduced, because the creation of a table is a data dictionary operation only.

When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance. With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. For more details, see the Oracle Database Administrator’s Guide.

Create a demo user for the testing.

SQL> create user demo identified by demo default tablespace users temporary tablespace temp;

User created.

SQL> grant resource to demo;

Grant succeeded.

SQL>

 

 

Deferred Segment creation enabled

Connect as the demo user and check the default settings.

SQL> connect demo/demo

Connected.

SQL> show parameter def

NAME TYPE VALUE

———————————— ———– ——————————

deferred_segment_creation boolean TRUE

SQL>

SQL> show parameter compatible

NAME TYPE VALUE

———————————— ———– ——————————

compatible string 11.2.0.0.0

SQL>

 

Create a table using the default settings enabling deferred segment creation.

SQL> create table t1

( no number

constraint t1_pk primary key,

id number

constraint t1_id unique,

cust_name varchar2(32),

l_o clob

)

lob( l_O )

store as t1_l_o_lob

(index t1_l_o_lobidx);

2 3 4 5 6 7 8 9 10 11

Table created.

SQL>

SQL>

 

Check for any created segments.

 

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

no rows selected

SQL>

 

Insert a row in the table.

SQL> insert into t1 values (1,1,’ERIC CLAPTON’,’LAYLA, COCAINE’);

1 row created.

SQL> commit;

Commit complete.

SQL>

 

Check for any created segments. As expected segments are created for the table,the respective pk and unique constraints indexes and for the lob and the lob indexes.

SQL> column segment_name format a20

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

 

SEGMENT_NAME EXTENT_ID BYTES

——————– ———- ———-

T1 0 65536

T1_ID 0 65536

T1_L_O_LOB 0 65536

T1_L_O_LOBIDX 0 65536

T1_PK 0 65536

SQL>

 

Create a table explicitly enabling segment creation.

SQL> create table t1_1

( no number

constraint t1_1_pk primary key,

id number

constraint t1_1_id unique,

cust_name varchar2(32),

l_o clob

)SEGMENT CREATION IMMEDIATE

lob( l_O )

store as t1_1_l_o_lob

(index t1_1_l_o_lobidx); 2 3 4 5 6 7 8 9 10 11

Table created.

SQL>

 

We see that specifying explicit segment creation overrides the DEFERRED_SEGMENT_CREATION init parameter.

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES

——————– ———- ———-

T1 0 65536

T1_1 0 65536

T1_1_ID 0 65536

T1_1_L_O_LOB 0 65536

T1_1_L_O_LOBIDX 0 65536

T1_1_PK 0 65536

T1_ID 0 65536

T1_L_O_LOB 0 65536

T1_L_O_LOBIDX 0 65536

T1_PK 0 65536

10 rows selected.

SQL>

 

Deferred Segment creation disabled

SQL> show parameter def

NAME TYPE VALUE

———————————— ———– ——————————

deferred_segment_creation boolean TRUE

SQL> show parameter compatible

NAME TYPE VALUE

———————————— ———– ——————————

compatible string 11.2.0.0.0

SQL>

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> show parameter def

NAME TYPE VALUE

———————————— ———– ——————————

deferred_segment_creation boolean FALSE

SQL>

 

Create a table

SQL> create table t1

( no number

constraint t1_pk primary key,

id number

constraint t1_id unique,

cust_name varchar2(32),

l_o clob

)

lob( l_O )

store as t1_l_o_lob

(index t1_l_o_lobidx); 2 3 4 5 6 7 8 9 10 11

Table created.

SQL>

 

Check for any created segments. Segments are created. This is what we are used to see in pre-11gR2 releases.

 

 

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES

——————– ———- ———-

T1 0 65536

T1_ID 0 65536

T1_L_O_LOB 0 65536

T1_L_O_LOBIDX 0 65536

T1_PK 0 65536

SQL>

 

Create a table specifying the deferred segment creation option.

 

SQL> create table t1_1

( no number

constraint t1_1_pk primary key,

id number

constraint t1_1_id unique,

cust_name varchar2(32),

l_o clob

)SEGMENT CREATION DEFERRED

lob( l_O )

store as t1_1_l_o_lob

(index t1_1_l_o_lobidx); 2 3 4 5 6 7 8 9 10 11

Table created.

SQL>

 

Check for any created segments. The clause overrides the DEFERRED_SEGMENT_CREATION init parameter.

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES

——————– ———- ———-

T1 0 65536

T1_ID 0 65536

T1_L_O_LOB 0 65536

T1_L_O_LOBIDX 0 65536

T1_PK 0 65536

SQL>

 

Insert a row in the table.

 

SQL> insert into t1_1 values (1,1,’MARILLION’,’LAVENDER,NEVERLAND,KAYLEIGH,INCOMUNICADO,ASSASSING, SHE CHAMELEON’);

1 row created.

SQL> commit;

Commit complete.

SQL>

 

We see that inserting a row creates the segments.

SQL> select segment_name, extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES

——————– ———- ———-

T1 0 65536

T1_1 0 65536

T1_1_ID 0 65536

T1_1_L_O_LOB 0 65536

T1_1_L_O_LOBIDX 0 65536

T1_1_PK 0 65536

T1_ID 0 65536

T1_L_O_LOB 0 65536

T1_L_O_LOBIDX 0 65536

T1_PK 0 65536

10 rows selected.

SQL>

 

Source:

Oracle Database Administrator’s Guide.

MOS Note 887962.1

August 14, 2010 - Posted by | oracle

1 Comment »

  1. […] Starting with Oracle 11gR2 11.2.0.2 there is a new clause added to the truncate table SQL statement allowing to release the space allocated even for extents corresponding to the minextents storage clause. Prior to 11.2.0.2 truncate table statement with its different flavor of clauses was used to delete all the rows from the table and possibly release the space but up to the space allocated with minextents. Now in 11.2.0.2 a new clause DROP ALL STORAGE for the truncate table SQL statement allows releasing even the space allocated to store the extents specified by minextents storage clause. However this clause is a subject to the same restrictions that apply to the deferred segment creation. More on the deferred segment creation can be found in a previous post here. […]

    Pingback by New truncate table feature (DROP ALL STORAGE) in Oracle 11gR2 11.2.0.2 « Guenadi N Jilevski's Oracle BLOG | December 4, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: