Oracle row migration

Problem description: another small problem today: After adding two new fields to a table, the query speed is obviously slow.

We suspect this is caused by row migration. If you try to move it, it will be OK.

Impact of row migration
If you read this row through an index, the index points to the original block, and the block points to the new block. To obtain specific row data, you can obtain row data instead of executing two or more I/O statements. This is not a big problem, or even cannot be noticed. However, if the proportion of such rows is large and a large number of users access these rows, you will notice this side effect. The speed of accessing this data starts to slow down (Additional I/O and I/O-related latencies will increase the access time), and the buffer cache efficiency starts to decrease (two blocks need to be cached, if the row is not migrated, only one block needs to be cached)

Two cases

A line of link is generated when data is inserted for the first time if a block cannot store a row of records. In this case, Oracle uses a link to one or more blocks in this segment to store this row of records. The row link is more likely to occur on a relatively large row, for example, a row contains fields of the LONG, long raw, and LOB data types. In this case, the row link is inevitable.

2. When a row of records is inserted at the beginning, they can be stored in a block. As a result of the update operation, the length is increased, and the free space of the block is full, at this time, row migration is generated. In this case, Oracle will migrate the entire row of data to a new block (assuming that a block can store the entire row of data ), oracle reserves the original pointer of the migrated ROW to point to the new block that stores the ROW data, which means that the row id of the migrated ROW will not change.

Two Parameters

CHAIN_CNT in user_tables

-- Number of row migration

Number of rows in the table that are chained from one data block to another,

Or which have migrated to a new block, requiring a link to preserve the old ROWID

-- Number of rows

NUM_ROWS

Example:

Analyze table t compute statistics;

Table analyzed.

SQL> select table_name, num_rows, CHAIN_CNT from user_tables where table_name = 'T ';

TABLE_NAME NUM_ROWS CHAIN_CNT
--------------------------------------------------
T 41616 3908

SQL> alter table t move;

Table altered.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select table_name, num_rows, CHAIN_CNT from user_tables where table_name = 'T ';

TABLE_NAME NUM_ROWS CHAIN_CNT
--------------------------------------------------
T 41616 0