Tales From A Lazy Fat DBA

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

Posts Tagged ‘New’

Shrinking Segments

Posted by FatDBA on February 7, 2013

Shrinking Segments:
The diagram in the slide describes the two phases of a table shrink operation. The first phase does the compaction. During this phase, rows are moved to the left part of the segment as much as possible. Internally, rows are moved by packets to avoid locking issues. After the rows have been moved, the second phase of the shrink operation is started. During this phase, the high-water mark (HWM) is adjusted and the unused space is released. The COMPACT clause is useful if you have long-running queries that might span the shrink operation and attempt to read from blocks that have been reclaimed. When you specify the SHRINK SPACE COMPACT clause, the progress of the shrink operation is saved in the bitmap blocks of the corresponding segment. This means that the next time a shrink operation is executed on the same segment, the Oracle database server remembers what has been done already. You can then reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.



Posted in Advanced | Tagged: , | Leave a Comment »


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/

Posted in Advanced | Tagged: , | 1 Comment »

Oracle Big Data: What’s the Hype …

Posted by FatDBA on November 5, 2012

“Oracle Big Data: A revolution that has already begun … ”

Read About It:





Posted in Advanced | Tagged: , | Leave a Comment »

%d bloggers like this: