Oracle row migration and row link clearing and Detection

I. Introduction of row migration and row link in actual work we often encounter some problems with low Oracle database performance. Of course, there are many reasons that cause low Oracle database performance, we can use some correct design and diagnosis to avoid the poor performance of some Oracle databases. RowMigration (row migration) RowChaining

I. Introduction of row migration and row link in actual work we often encounter some problems with low Oracle database performance. Of course, there are many reasons that cause low Oracle database performance, we can use some correct design and diagnosis to avoid the poor performance of some Oracle databases, Row Migration (Row Migration) Row Chaining

1. Introduction to row migration and row Link 　

In actual work, we often encounter some problems with low Oracle database performance. Of course, there are many reasons that cause low Oracle database performance, we can try to avoid some poor Oracle database performance through correct design and diagnosis. Row Migration (Row Migration) & Row Chaining (Row link) that is, we can avoid potential problems that may cause low Oracle database performance. Through reasonable row migration and row link diagnosis, we can greatly improve the performance of the Oracle database.

So what is row migration and row link? Let's start with the Oracle block.

The minimum read/write operation unit of the operating system is the block of the operating system. Therefore, when creating an Oracle database, we should set the block size of the database to an integer multiple of the block size of the operating system, oracle block is the smallest unit for read/write operations in Oracle databases. in Oracle Database versions earlier than Oracle9i, once the Oracle block is set during database creation, it cannot be changed. To determine the size of a reasonable Oracle block before creating a database, we need to consider some factors, such as the size of the database and the number of concurrent transactions. Using an appropriate Oracle block size is very important for database optimization.

An Oracle block consists of three parts: data block header, free space, and actual data.

Data Block header: It mainly contains some basic information and segment types with data block addresses, as well as the addresses of tables and actual rows containing data.

Free Space: the space that can be allocated for future update and insert operations. The size is affected by two parameters: PCTFREE and PCTUSED.

Actual data: refers to the actual data stored in the row.

When creating or changing any table or index, Oracle uses two storage parameters for Space Control:

PCTFREE: Percentage of space reserved for future updates of existing data.

PCTUSED: the minimum percentage of space used to insert data into a new row. This value determines the block availability status. You can insert a block when a block is available. blocks in the unavailable status can only be deleted and modified. blocks in the available status are placed in freelist.

When the data of a row in a table cannot be placed in a data block, there will be two situations: one is row link and the other is row migration.

Row links are 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.

When a row of records is inserted at the beginning, they can be stored in a block. Because the update operation increases the length of the row, and the free space of the block is full, 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.

When a row migration or a row link occurs, the performance of this row of data operations will be reduced, because Oracle must scan more blocks to obtain information about this row.

The following example describes the generation process of row migration and row link.

Create a test table with pctfree 20 and pctused 50:

create table test（col1 char(20),col2 number)storage (pctfree 20pctused 50);

When a record is inserted, Oracle searches for a free block in the free list and inserts the data into the free block. The free block in the free list is determined by the pctfree value. The initial empty blocks are all in the free list, until the free space in the block reaches the pctfree value, this block will be removed from the free list, when the space used in this block is lower than pctused, the block is re-stored in the free list.

Oracle uses the free list mechanism to greatly improve performance. For each insert operation, Oracle only needs to find the free list, rather than finding all the blocks to find free space.

The following experiment shows how row links and row migration are generated and reflected in data files. First, check the data file number of the ALLAN tablespace. To facilitate the test, I only created one data file.

SQL> select file_id from dba_data_files where tablespace_name='ALLAN';FILE_ID----------23

Create a test table:

SQL> create table test ( x int primary key, a char(2000), b char(2000), c char(2000), d char(2000), e char(2000) ) tablespace allan;Table created.

Because my database's db_block_size is 8 K, the table I created has five fields, each of which occupies 2000 bytes. In this way, a row records about 10 K, the size of a block is exceeded.

Insert a record with only one field:

SQL> insert into test(x) values (1);1 row created.SQL> commit;Commit complete.

Find the block where the row of records is located and dump it out:

SQL> select dbms_rowid.rowid_block_number(rowid) from test;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)34SQL> alter system dump datafile 23 block 34;System altered.

View the trace file in the udump directory as follows:

Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34buffer tsn: 34 rdba: 0x05c00022 (23/34)scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x05c00022Object id on Block? Yseg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f30x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000data_block_dump,data header at 0xadb505ctsiz: 0x1fa0hsiz: 0x14pbl: 0x0adb505cbdba: 0x05c0002276543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1f9aavsp=0x1f83tosp=0x1f830xe:pti[0] nrow=1 offs=00x12:pri[0] offs=0x1f9ablock_row_dump:tab 0, row 0, @0x1f9atl: 6 fb: --H-FL-- lb: 0x1 cc: 1col 0: [ 2] c1 02end_of_block_dumpEnd dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

Explain some of the information:

Fb: H refers to the row record header, L refers to the last column of the row record, and F refers to the first column of the row record.

Cc: Number of columns.

Nrid: the value of the next row id for row link or row migration.

From the dump information above, we can see that there is no row link or row migration in the current table test.

Then update the test table and run the following command again:

SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;1 row updated.SQL> commit;Commit complete.

In this case, row migration and row link are generated.

SQL> alter system dump datafile 23 block 34;System altered.

View the trace file in the udump directory as follows:

Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34buffer tsn: 34 rdba: 0x05c00022 (23/34)scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x05c00022Object id on Block? Yseg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f30x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442bdata_block_dump,data header at 0xadb505c===============tsiz: 0x1fa0hsiz: 0x14pbl: 0x0adb505cbdba: 0x05c0002276543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x178aavsp=0x177ctosp=0x177c0xe:pti[0] nrow=1 offs=00x12:pri[0] offs=0x178ablock_row_dump:tab 0, row 0, @0x178atl: 2064 fb: --H-F--N lb: 0x2 cc: 3nrid: 0x05c00023.0col 0: [ 2] c1 02col 1: [2000]74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 2020 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20…………col 2: [48]74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 2020 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20end_of_block_dumpEnd dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

It is not hard to see that the nrid has a value pointing to the next row id, proving that the update operation has produced a row link or line for this row of records?

2. Row migration and row link Detection

We know from the previous introduction that the row link is mainly because the database's db_block_size is not large enough, and some large fields cannot be stored in a block, resulting in a row link. In addition to increasing db_block_size, there is no way to avoid row links. However, db_block_size cannot be changed after the database is created (before 9i ), for Oracle9i databases, we can specify different db_block_size values for different tablespaces. Therefore, the generation of row links is almost inevitable, and there is not much to adjust. Row migration is mainly because the pctfree parameter of the table is too small during table update, so there is not enough space in the block to accommodate the updated records, resulting in row migration. It is necessary for row migration to be adjusted because it can be adjusted and cleared.

How can I check whether row migration and row link exist in the database? We can use the script utlchain provided by the Oracle database itself. SQL (in the $ ORACLE_HOME/rdbms/admin directory) generates the chained_rows TABLE, and then analyzes the analysis results one by one using the ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows command to save the analysis results to the chained_rows TABLE. From the utlchain. SQL script, we can see that the chained_rows table creation script is applicable to partition tables and cluster tables. Then, you can generate the script for the table required for analysis by using the patchwork statement, and execute the script to put the specific analysis data into the Chained_rows table. For example, the following is a script for analyzing all the tables of a user:

SPOOL list_migation_rows.sqlSET ECHO OFFSET HEADING OFFSELECT 'ANALYZE TABLE ' || table_name || ' LIST CHAINED ROWS INTO chained_rows;' FROM user_tables;SPOOL OFF

Query the chained_rows table to view the number of row links and row migration in a table.

SELECT table_name, count(*) from chained_rows GROUP BY table_name;

Of course, you can also query the 'table fetch continued row' column in the v $ sysstat view to get the current row link and row migration quantity.

SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

You can use the following script to directly search for tables with row links and row migration to automatically complete all analysis and statistics.

accept owner prompt " Enter the schema name to check for Row Chaining (RETURN for All): "promptpromptaccept table prompt " Enter the table name to check (RETURN for All tables owned by &owner): "promptpromptset head off serverout on term on feed off veri off echo off!clearprompt declarev_owner varchar2(30);v_table varchar2(30);v_chains number;v_rows number;v_count number := 0;sql_stmt varchar2(100);dynamicCursor INTEGER;dummy INTEGER;cursor chains isselect count(*) from chained_rows;cursor analyze isselect owner, table_namefrom sys.dba_tables where owner like upper('%&owner%')and table_name like upper('%&table%')order by table_name;begindbms_output.enable(64000);open analyze;fetch analyze into v_owner, v_table;while analyze%FOUND loopdynamicCursor := dbms_sql.open_cursor;sql_stmt := 'analyze table '||v_owner||'.'||v_table||' list chained rows into chained_rows';dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);dummy := dbms_sql.execute(dynamicCursor);dbms_sql.close_cursor(dynamicCursor);open chains;fetch chains into v_chains;if (v_chains != 0) thenif (v_count = 0) thendbms_output.put_line(CHR(9)||CHR(9)||CHR(9)||'<<<<< Chained Rows Found >>>>>');v_count := 1;end if;dynamicCursor := dbms_sql.open_cursor;sql_stmt := 'Select count(*) v_rows'||' From '||v_owner||'.'||v_table;dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);dummy := dbms_sql.execute(dynamicCursor);dummy := dbms_sql.fetch_rows(dynamicCursor);dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);dbms_sql.close_cursor(dynamicCursor);dbms_output.put_line(v_owner||'.'||v_table);dbms_output.put_line(CHR(9)||'---> Has '||v_chains||' Chained Rows and '||v_rows||' Num_Rows in it!');dynamicCursor := dbms_sql.open_cursor;sql_stmt := 'truncate table chained_rows';dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);dummy := dbms_sql.execute(dynamicCursor);dbms_sql.close_cursor(dynamicCursor);v_chains := 0;end if;close chains;fetch analyze into v_owner, v_table;end loop;if (v_count = 0) thendbms_output.put_line('No Chained Rows found in the '||v_owner||' owned Tables!');end if;close analyze;end;/set feed on head onprompt

3. Row migration and row link clearing

Because the row link can only be cleared by increasing db_block_size, and db_block_size cannot be changed after the database is created, the row link clearing is not described too much here, this article mainly focuses on how to clean up row migration in the actual production system.

To clear row migration, there are two steps: Step 1: control the growth of row migration so that it does not increase; Step 2: Clear the existing row migration.

As we all know, the main reason for row migration is that the pctfree parameter setting on the table is too small. To achieve the first step to control the growth of row migration, you must set a correct and appropriate pctfree parameter, otherwise, many new row migration will be generated immediately after the current row migration is cleared. Of course, this parameter is not as big as possible. If pctfree is set too large, it will lead to low utilization of data blocks and a large waste of space. Therefore, a reasonable pctfree parameter must be set. Generally, there are two methods to determine a reasonable pctfree parameter for a table.

The first method is to use the formula to set the pctfree size. First, use the analyze table table_name estimate statistics command to ANALYZE the TABLE for which pctfree is to be modified, then view the AVG_ROW_LEN column value in user_tables, get an average president AVG_ROW_LEN1, and then perform a large number of operations on the TABLE, again using the above command analysis table, get the second average Governor AVG_ROW_LEN2, then use the formula 100*(AVG_ROW_LEN2-AVG_ROW_LEN1)/(AVG_ROW_LEN2-AVG_ROW_LEN1 + original AVG_ROW_LEN) the result is a suitable pctfree value calculated quantitatively. This method may not be accurate because it is calculated quantitatively. It is not very suitable for systems that use RBO execution plans because analysis tables are required. For example, avg_row_len_1 = 60, avg_row_len_2 = 70, the average modification volume is 10, and PCTFREE should be adjusted to 100*10/(10 + 60) = 16.7%.

The second method is differential fine-tuning. First, the pctfree value of the current table is queried, then the pctfree parameter is monitored and adjusted, and the pctfree size is increased each time, the ratio of each increase should not exceed 5 percentage points. Then, use the analyze table TABLE_NAME list chained rows into chained_rows command to ANALYZE the growth of all row migration and row links each time, different tables adopt different growth ratios. For tables with fast row migration growth, the pctfree value increases by more points, and for tables with slow growth, it increases by less points, until the row migration of the table remains unchanged. However, be sure not to tune the pctfree too large. Generally, it can be set to below 40%. Otherwise, it will cause a great waste of space and increase the IO for database access.

After using the above method to control the growth of row migration in the current table, you can start to clear the existing row migration in the previous table. Whether row migration is cleared is related to whether the system performance can be greatly improved. Therefore, the existing row migration must be cleared. There are many methods to clear existing row migration, but not all methods can apply to all cases, such as the number of records in the table, the number of table associations, the number of table upstream migrations, and so on all of these factors will become a condition for you to use what method to clear. Therefore, based on the characteristics of the table and the specific situations, we should use different methods to clear row migration. Next I will introduce various methods for clearing row migration and their respective applicability.

Method 1: traditional methods for clearing row migration

The procedure is as follows:

1. Run the utlchain. SQL script in the $ ORACLE_HOME/rdbms/admin directory to create the chained_rows table.

@ $ ORACLE_HOME/rdbms/admin/utlchain. SQL

2. Put the rowid of the row generated for row migration in the table with row migration (replaced by table_name) into the chained_rows table.

Analyze table table_name list chained rows into chained_rows;

3. Store the row id of row migration in the temporary table.

CREATE TABLE table_name_temp ASSELECT * FROM table_nameWHERE rowid IN(SELECT head_rowid FROM chained_rowsWHERE table_name = 'table_name');

4. Delete the existing row migration record rows in the original table.

DELETE table_nameWHERE rowid IN(SELECT head_rowidFROM chained_rowsWHERE table_name = 'table_name');

5. Retrieve and re-insert the deleted data from the temporary table to the original table and delete the temporary table.

INSERT INTO table_name SELECT * FROM table_name_temp;DROP TABLE table_name_temp;

For this traditional method of clearing RM, the advantage is that the execution process is simple and easy to implement. However, this algorithm does not take table association into account. In most databases, many tables are associated with other tables and have foreign key restrictions, in this way, the record rows with row migration cannot be deleted at all in step 3. Therefore, this method can be applied to a limited range of tables, only applicable to tables without any foreign key Association. Because this method does not disable the index during data insertion and deletion, the primary time consumed is to maintain the balance of the index tree during deletion and insertion, this is a relatively short time for a small number of records, but the time consumed for a table with a large number of records is not acceptable. Obviously, this method is not feasible when processing tables with large data volumes.

The following is an example of migration by clearing rows in the production database. Before that, the pctfree parameter of the table has been adjusted to a proper value:

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sqlTable created.SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;Table analyzed.SQL>SELECT count(*) from chained_rows；TABLE_NAME COUNT(*)CUSTOMER 213061 rows selected.

View the restrictions on the CUSTOMER table:

SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';CONSTRAINT_NAME C TABLE_NAME------------------------------ - --PK_CUSTOMER1 P CUSTOMERSQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_CUSTOMER1';no rows selectedSQL> CREATE TABLE CUSTOMER_temp ASSELECT * FROM CUSTOMER WHERE rowid IN(SELECT head_rowid FROM chained_rowsWHERE table_name = 'CUSTOMER'); Table created.SQL>select count(*) from CUSTOMER;COUNT(*)----------338299SQL> DELETE CUSTOMER WHERE rowid IN(SELECT head_rowidFROM chained_rowsWHERE table_name = 'CUSTOMER');21306 rows deleted.SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;21306 rows created.SQL> DROP TABLE CUSTOMER_temp;Table dropped.SQL> commit;Commit complete.SQL> select count(*) from CUSTOMER;COUNT(*)----------338299SQL> truncate table chained_rows;Table truncated.SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;Table analyzed.SQL> select count(*) from chained_rows; COUNT(*)----------0

The migration process for clearing more than 20 thousand rows is about three minutes, and all rows are completed online, which basically does not affect the business, the only difference is that there is no foreign key restriction on the table for row migration to be cleared; otherwise, this method cannot be used to clear the table.