Bank migration/Row link Learning in Oracle database (i) What is row migration/row chain

In the actual work we often encounter some Oracle database performance is low, of course, the reasons for the low performance of Oracle database is various, we can through some correct design and diagnosis to try to avoid some Oracle database performance is not good, Row migration (Row migration) & Row chaining (row link) is one of the potential problems that we try to avoid in the performance of the Oracle database. With reasonable diagnostic row migration/row links, we can significantly improve the performance of Oracle databases.

So what exactly is a row migration/row link, let's start with Oracle's block.

The operating system's smallest read and write unit is the block of the operating system, so when creating an Oracle database we should say that the block size of the database is set to be an integer multiple of the operating system's block size, Oracle Block is the smallest unit of read and write operations in an Oracle database, and Oracle block is no longer able to change once it is created in the Oracle database version prior to oracle9i. To determine the size of a reasonable Oracle block before creating a database, we need to consider factors such as the size of the database itself and the number of concurrent transactions. Using an appropriate Oracle block size is important for tuning the database. The structure of the Oracle block is shown in the following illustration:

Figure I: Oracle block structure diagram

As we can see from the above illustration, an Oracle block consists of three parts, namely, data size, free space, and three parts of actual data.

Data size: Some basic information and the type of segment that contains the address of the block, and the address of the table and the actual row that contains the data.

Free space: The space that can be allocated for later updates and inserts, the size of which is affected by the two parameters of Pctfree and pctused.

Actual data: Refers to the actual data stored within a row.

Oracle uses two storage parameters for space control when creating or changing any tables and indexes:

PCTFREE: The percentage of space reserved for future updates of existing data.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45554.htm

pctused: The percentage of the minimum space used for inserting a new row of data. This value determines the available state of the block. Blocks that are available can execute inserted blocks, blocks that are not usable can only be deleted and modified, and blocks of usable states are placed in freelist.

When a row of data in the table can not be placed in a data block, this time there will be two kinds of cases, one is the row link, the other is the row migration.

A row link produces the first time the data is inserted if a block cannot hold a row of records. In this case, Oracle will use links one or more blocks stored in this section to store this row of records, the row link is more likely to occur on the larger rows, such as a long, long RAW, LOB, and other data types of fields, such as row links are inevitable.

When a row records the initial insertion of the event can be stored in a block, because the update operation led to the increase in the governor, and block free space is full, this time a row migration. In this case, Oracle will migrate the entire row of data into a new block (assuming that the entire row of data can be stored in a block), and Oracle retains the original pointer of the migrated row to the new block that holds the row data, which means that the row ID of the migrated rows does not change.

When a row migration or row link occurs, the performance of this row of data is reduced, because Oracle has to scan more blocks for this line of information.

The following example illustrates the process of producing a row migration/row link.

First, create a test table with Pctfree 20 and pctused 50:

CREATE TABLE Test (

col1 Char (20),

Col2 number)

Storage (

Pctfree 20

pctused 50);

When you insert a record, Oracle searches for a free block in the free list and inserts the data into the Freedom block. The free blocks that exist in the Freedom List are determined by the Pctfree value. The initial empty block is in the free list, and the block is removed from the Freedom list until the space in the block reaches the Pctfree value, and the block is then put back into the release list when the space used in the block is lower than the pctused.

Oracle uses the free list mechanism to greatly improve performance, and for each insert, Oracle only needs to find the free list, not to find all the blocks to find space.

Suppose you first insert an empty block that the data uses, as shown in the following illustration:

Figure II: Oracle Empty block structure diagram

Assuming that the first record is inserted with a block 10% space (minus the size of the block header), the remaining free space of 90% is greater than pctfree20%, so this block will also continue to provide space for the next insert operation.

Insert seven consecutive records so that the remaining free space of the block is left 20%, at which point the block will be removed from the Freedom list, and if the record is inserted, Oracle will then search the free list for the next slot to store the data that was inserted later.

Figure IV: Oracle block structure diagram after inserting 80%

If you go to update the first inserted record, so that its president to add 15%,oracle will use the remaining 20% of the block in the free space to store this data, if the second record, the same to increase the number of its President 15%, and this block only 5% of the free space, Not enough to store the second record of the update, so Oracle will look for a free space block to store the block of the line record, and hold the pointer to the new block in the original block, the row of the 10%+15% The ID remains unchanged, and this time a row migration is generated.

And when we insert a new record, if a blcok is not enough to hold this record, Oracle will look for a certain number of blocks together to accommodate the record, which creates a row link, which is mainly generated in lob, CLOB, BLOBs and large VA row links are HAR2 data types.

Let's go through one of the following experiments to see how the row links and row migrations are generated and reflected in the data file.

First look at the data file number Allan This table space, in order to facilitate testing, I only set up a data file.

Sql> Select file_id from dba_data_files where tablespace_name= ' ALLAN ';

file_id

----------

23

To create a test table:

Sql> CREATE TABLE test (x int primary key, a char (), b char (), C char (), D char (), E char ()) tabl Espace Allan;

Table created.

Because the db_block_size of my database is 8K, the table I created has five fields, each 2000 byte, so that a row of about 10K can exceed the size of a block.

Then insert a row of records with only one field:

sql> INSERT into Test (x) values (1);

1 row created.

Sql> commit;

Commit complete.

Find the block where this line of records is located and dump it:

Sql> Select Dbms_rowid.rowid_block_number (ROWID) from test;

Dbms_rowid. Rowid_block_number (ROWID)

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

34

sql> alter system dump datafile block 34;

System altered.

The contents of the trace file are viewed under the Udump directory as follows:

Start dump data blocks tsn:34 file#: minblk maxblk 34

Buffer tsn:34 rdba:0x05c00022 (23/34)

SCN:0X0000.013943F3 seq:0x01 flg:0x02 tail:0x43f30601

frmt:0x02 chkval:0x0000 Type:0x06=trans Data

Block Header dump:0x05c00022

Object ID on block? Y

SEG/OBJ:0X3CCD csc:0x00.13943ef itc:2 flg:o typ:1-DATA

fsl:0 fnx:0x0 ver:0x01

Itl Xid Uba Flag Lck SCN/FSC

0x01 0x000a.02e.00000ad7 0x00800036.03de.18--u-1 FSC 0x0000.013943f3