Row Migration Vs Row Chaining

split_partition

Oracle database allocates logical space for all data in the database.

Logical storage structures are recognized by Oracle DB and are NOT known to Operating System.

At physical level, the data is stored in data files on disk which ultimately writes on OS blocks.

The logical unit of space allocation are :-

Data-Block :-
Smallest logical unit of data storage in Oracle database. One data block corresponds to a specific number of bytes of OS block.Default size is 8KB.

Extent :-
An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

Segment :-
A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each table’s data is stored in its own data segment, while each index’s data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.

Tablespace :-
Tablespace is a database storage unit contains one or more segments. A segment and all its extents are stored in one tablespace.

• Oracle Database allocates space for segments in units of one extent. When the existing extents of a segment are full, Oracle Database allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

• A segment can include extents that are created from more than one file.However, each extent can contain data from only one datafile.

INITRANS :-
Denotes initial transactions which is the minimum number of transactional slots that can guaranteed by a block.So if we specify INITRANS as 4 so,23 * 4 = 92 bytes of space will be reserved for transactional entries.

MAXTRANS :-
Denotes maximum transactions that can be operated on a block. There can be a maximum of 255 concurrent sessions that can access a block at any given time. So the maximum value for MAXTRANS parameter is 255.

PCTFREE :-
It states how much of free space should i reserve for future updates of the rows that are present inside the block.if PCTFREE is set to 20 then once the used space reaches 80%,this block will be removed from freelist so that no more insert take place in this block.

Now that the block has been removed from the freelist,no more inserts can occur,but there is a possibility that i delete some rows from this block and there could be free space available.so should i not populate rows into it?That is where PCTUSED comes into play.

PCTUSED :-
It determines when can a block that was removed from freelist be brought back into the freelist.if PCTUSED is set to 40 then when the used space in the block comes to below 40%,then it will add the block back into the freelist thereby new inserts can now happen in this block.

ROW-MIGRATION

If PCTFREE is set small let say 90,more rows will be stored in a block.If we update any of the rows and if there is no free space,then the row will be migrated to another block.

When user reads Row#4,it will come to block-1,but after going to row#4 location or rowid,it sees that that the row has been moved to block-2,and it eventually reads the data from block-2,so instead of reading one data block it is now reading two data block to fetch just one row.Row migration leads to more I/O.

In Row Migration,the data only moves to different block,but the ROWID stays in the original data block.

Solution :-
Set PCTFREE appropriately.

ROW-CHAINING

This happens during INSERT.

Suppose we have set the block size as 4KB,and we have a requirement to insert a row data of length 8KB.In this case,the row will be divided into 2 data blocks of 4KB each.This happens due to small/incorrect BLOCK SIZE.

             4KB (Block 1)           4 KB  (Block 2)

Solution :-
Set BLOCK SIZE properly.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

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