Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯


Posted by FatDBA on February 7, 2013

Deferred segment creation (DEFERRED_SEGMENT_CREATION):

Oracle Database 11gR2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the DEFERRED_SEGMENT_CREATION initialization parameter set to TRUE.

Advantages of this new space allocation method:
1. 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.
2. The application installation time is reduced.

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.

A new parameter DEFERRED_SEGMENT_CREATION is created to control the feature. It can be enabled/disabled it at the session or system level (doesn’t require Instance restart).
Alter session set deferred_segment_creation=true;
Alter system set deferred_segment_creation=true;

Deferred segment creation is enabled by default. It’s also possible to enable/disable it for a single table by specifying the deferred segment creation clause.


SQL> SHOW PARAMETERS deferred_segment_creation
NAME                                 TYPE        VALUE
———————————— ———– ——
deferred_segment_creation            boolean     TRUE

SQL> CREATE TABLE seg_test(c number, d varchar2(500));
Table created.
SQL> SELECT segment_name FROM user_segments;
no rows selected

Inserting rows and creating segments:
SQL> INSERT INTO seg_test VALUES(1, ‘aaaaaaa’);
1 row created.

SQL> SELECT segment_name FROM user_segments;

DB_ULTRA_SAFE is a new parameter brought in 11g.
It provides an combined mechanism to offer protection from various possible data corruptions and provides critical high availability advantages for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

DB_BLOCK_CHECKING: DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks. Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable. For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.
DB_BLOCK_CHECKSUM: detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.
DB_LOST_WRITE_PROTECT (also introduced with 11gR1): enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

DB_ULTRA_SAFE Parameter can be set to 3 different values: OFF, DATA_ONLY and DATA_AND_INDEX. Default value is OFF.

Here are the descriptions of these values:
OFF: It will not change values of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT if they are explicitly set, otherwise all of them will set to default values.

DATA_ONLY: It will set DB_BLOCK_CHECKING to medium, DB_LOST_WRITE_PROTECT to typical and DB_BLOCK_CHECKSUM to full.


The only difference between DATA_AND_INDEX and DATA_ONLY is DB_BLOCK_CHECKING. When DB_BLOCK_CHECKING is set to full, Oracle will do substance checks for index blocks.

Note: Please check blog from Francisco Munoz Alavarez (Oracle ACE Director) over DB_ULTRA_SAFE parameter  — http://oraclenz.wordpress.com/2009/12/16/db_ultra_safe-a-new-gem-for-high-availability/


One Response to “DEFERRED_SEGMENT_CREATION and DB_ULTRA_SAFE Parameters (New to 11gr2)”

  1. testdomain said

    Oh my goodness! an incredible article dude. Thank you Nonetheless I’m experiencing challenge with ur rss . Don know why Unable to subscribe to it. Is there anyone getting comparable rss drawback? Anybody who knows kindly respond. Thnkx

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 )

Connecting to %s

%d bloggers like this: