Oracle row migration and row linking

Row migrations and row links lead to degraded Oracle performance, this article describes what row migration and row links are, what they do, how to judge them, and provides solutions to them.

What is row migration and row link row migration

Oracle's data blocks retain some space for later updates, and the usual data block structure is as follows:

Pctfree defines the percentage of space reserved for a block, which defaults to 10, which means that when the block has less than 10% of free space, it cannot be insert, only the update (see the following pctfree for details).
When a record is updated, the database engine first tries to find enough free space in the data block it saves, and if there is not enough free space available, the record is split into two parts, the first part includes the ROWID that points to the second part, which remains in the original data block, The second section contains all the specific data that will be saved to another new block of data, which becomes a row migration.

Why not put the whole line in a new block of data?
The reason is that this can cause the row data rowid to change, and rowID is stored in the index, it may be temporarily stored in memory by the client, ROWID changes may lead to query errors.

Pctfree Introduction

Pctfree can be specified when building a table:

CREATE TABLE Test1 Pctfree as select RowNum as ID from all_objects where rownum <= 1000;

This specifies that the pctfree of table test1 is 20, which can be viewed in the following way:

Select table_name, pct_free from user_tables where table_name = ' TEST1 '; Table_namepct_free---------------------------TEST120

After creating the table you can adjust the Pctfree value by ALTER TABLE:

ALTER TABLE test1 Pctfree 15;

Line link

Row links and row migration are different, the row link is when a record is too large, in a block of data can not be saved, this will be split into 2 or more parts, stored in multiple blocks, which will construct a chain between the blocks, as follows:

Row migration is caused by an update, while the reason for a row link might be:
1) insert large records directly;
2) An update record causes the record to be larger than a block of data, at which point the record may become both a row migration and a row link.

Issues with row migration and row chaining

Row migration does not affect full scan (full scan for more information see "Oracle Performance Analysis 4: Full scan of data access methods"), because the first part does not contain data and will be skipped directly, but for access through ROWID (Index scan or direct use of ROWID query), the overhead will double, Access to two blocks is mainly due to a single read.
Row links are independent of the way data is accessed, and each time you access the first record fragment, you need to access the other record fragments through ROWID.
Row and row links also affect row-level locks because each record fragment needs to hold a lock, and the cost of the lock is proportional to the increase in the number of record fragments.

Determine row migration and row links

There are several ways to determine row migration and row links.

View V$sysstat and V$sesstat views

The statistics item in the View table fetch continued row confirms that row migration and row links have occurred.

Select Name,value from v$sysstat where name = ' Table fetch continued row '; Namevalue-----------------------------------------------table Fetch continued row27455

But this value can only prompt you to have row migration or row links somewhere in your database, and if you want to evaluate the resulting impact, you need to compare the statistics of table scan rows gotten and table fetch by ROWID.

View row migration and row link information for a specific table

Executes on the specified table:

Analyze table <table_name> list chained rows

If a row link or row migration record is found, their rowid is recorded in the Chained_rows table, which can be used $oracle_home/rdbms/ The Utlchain.sql or Utlchn1.sql script in the admin directory is created with the following table statements:

CREATE TABLE Chained_rows (  owner_name         varchar2 (+),  table_name         VARCHAR2 (+),  cluster_name       varchar2 (+),  partition_name     varchar2 (+),  subpartition_name  varchar2 (+),  Head_ rowID         Urowid,  analyze_timestamp  date);

The following stored procedure will be used to parse all tables that meet the criteria:

Begin  for obj in (select owner, object_name from                dba_objects               where object_type = ' TABLE ' and                 <other Co nditions>) Loop    dbms_output.put_line (Obj.owner | | '.' || Obj.object_name);    Execute immediate ' analyze table ' | | Obj.owner | | '.' ||                      Obj.object_name | | ' List chained rows ';  End Loop;end;

You then discover which tables have row migration and row links by looking at the data in the Chained_rows table:

Select Table_name,head_rowid from Chained_rowstable_namehead_rowid--------------------------------------ind$ aaaaacaabaaaaadaaaind$aaaaacaabaaaaadaagind$aaaaacaabaaaaamaaiind$aaaaacaabaaagpraahind$aaaaacaabaaan0laadind$ Aaaaacaabaaan0oaae ...

Here we can see the rows of those tables that have row migration and row links, but do not know the exact row migration and row links, we can calculate the length of the record, and then compare the length and block size, so as to identify whether they are row migration or row link.
Calculate the length of a row using the following statement:

Select Vsize (<col1>) + vsize (<col2>) + ... + vsize (<col3>) from <table> where rowid = ' <ROWID&G t; ';

The block size of the database is saved in the parameter db_block_size, see the method of the parameter see "Summary of Oracle Parameter view method".

View row migration and row links in table statistics

The chain_cnt field of table Dba_tables represents the number of row migration and row links, but the Dbms_stats package does not collect this statistic, which is always 0.

Select table_name,chain_cnt from dba_tables where table_name = ' ind$ '; Table_namechain_cnt---------------------------ind$0

You can collect this information by using the following SQL statement:

Analyze table ind$ compute statistics;

Then check again:

Select table_name,chain_cnt from dba_tables where table_name = ' ind$ '; Table_namechain_cnt---------------------------ind$13

However, this method causes the statistics for all objects of the parsed table to be overwritten, so it is not recommended in practice.

Solutions

Row migration and row chaining are resolved differently, so be sure to differentiate between row and row links before processing.

Row migration

First, we should avoid row migration by reserving enough free space in the original block, which is to adjust the Pctfree parameter value, and the size of the value needs to evaluate the average size of the record extension.
When a row migration occurs, it can only be resolved by moving the data, in the following ways:
1) Restructure the table by exporting, importing, or by ALTER TABLE move;
2) Copy the migrated data to a staging table, delete the original table, and reinsert the data.

Line link

Processing a row link can only increase the size of the data block, but in some cases, you can increase the efficiency of some queries by placing characters commonly used segments in front of the table and infrequently accessed fields at the end of the table (because only query-related fields are taken by Oracle queries).

Oracle row migration and row linking