The Secrets of Oracle Row Chaining and Migration

转自:

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

• Row migration is typically caused by UPDATE operation

• Row chaining is typically caused by INSERT operation.

• SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.

• To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view

• To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

A chained row is a row that is too large to fit into a single database data 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 row size 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.

Detecting row chaining

This query will show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

To see which rows are chained:

ANALYZE TABLE tablename LIST CHAINED ROWS;

This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (in $ORACLE_HOME/rdbms/admin).

SELECT * FROM chained_rows;