Oracle Row Chaining and Migration:

http://www.akadia.com/services/ora_chained_rows.html

If we notice poor performance in your oracle database Row chaining and Row Migration may be one of several reasons.

Row migration and Row chaining are two potential problems that can be prevented. By suitable diagnosing, we can improve database performance.

The main considerations are:

* What is Row Migration and Row Chaining?
* How to identify Row Migration and Row Chaining?
* How to avoid Row Migration and Row Chaining?

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, we can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

OLTP : On-line Transaction Processing is a class of software programs capable of supporting Transaction-Oriented applications on the internet. Typically, OLTP systems are used for order entry, financial trasactions, customer relationship management (CRM) and retail sales.

Oracle Block:
-------------

The Operating System Block Size is the minimum unit of operation (Read/Write) by the OS and is a property of the OS file system.
While creating an oracle database we have to choose the <<Database Block Size>> as a multiple of the Operating System Block size.
The minimum unit of operation (Read/Write) by the Oracle Database would be this <<Oracle Block>>, and not the OS block.
Once set, the <<Database Block Size>> cannot be changed during the life of the database (except in case of Oracle 9i). 
To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected.

The database block has the following structure (Within the whole database structure).

Header:
Header contains the general infomation about the data i.e. block address, Type of segment (table, index etc).
It also contains the information about table and the actual row (address) which that holds the data.

Freespace:
Space allocated for future update/insert oprations. Generally affected by the values of PCTFREE and PCTUSED paramters.

Data:
Actual row data.

FREELIST, PCTFREE and PCTUSED:

While creating/altering any table/index, Oracle used two storage parameters for space control.
PCTFREE - The percentage of space reserved for future update of existing data.
PCTUSED - The percentage of minimum space used for insertion of new data.The value determines when the block gets back into the FREELISTS structure.
FREELIST - Structure where Oracle maintains a list of all free available blocks.

    Oracle will first search for a free block in the FREELIST and then the data is inserted into that block.The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free spae reaches the PCTFREE value.

    Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.


ROW MIGRATION:
--------------
We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). 
A migration means that the entire row will move and we just leave behined the <<forwarding address>>.
So, the original block just has the rowid (address of the rows) of the new block and the entire row is moved.

Full table scans are not affected by migrated rows:

---------------------------------------------------

The forwarding addresses are ignored. We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in full scan migrated rows don't cause us to really do any extra work.

Index Read will cause additional IO's on migrated rows:

-------------------------------------------------------

When we index Read into a table, then a migrated row will cause additional IO's. That is because the index will tell us <<goto file x, block y, slot z to find this row>>. But when we get there we find a message that says <<well, really got file a, block b, slot c to find this row>>. we have to do another IO (Logical or Physical) to find the row.

ROW CHANNING:

-------------

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query:

select col1 from table;

where col1 is in block 1, would not cause any <<table fetch continued row>>. It would not actually have to get col2, it would not follow the chained row all of the way out. On the other hand, if we ask for:

select col2 from table;

and col2 is in block2 due to row chaining, then you would in fact see a <<table fetch continued row>>.