Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what is SQL Line Migration and Line Link". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Row migration and row linking (cross-block storage of rows)

Line links:

Cause: refers to the situation in which a row is stored in multiple blocks, that is, a row link is a row that spans multiple blocks.

When you insert for the first time, one block cannot be saved and insert into multiple blocks.

When a row of data is larger than one block, ORACLE allocates two blocks at the same time and registers the address of the second block on the first block, forming a row link.

The consequence: the application needs to access more data blocks, and the performance is degraded.

Prevention: expand the block size for tablespaces.

Check: analyze table table name validate structure cascade into chained_rows

You need to execute the script $ORACLE_HOME/rdbms/admin/utlchain.sql to create the chained_ rows table.

Row Migration:

Cause: when a row is Update, if the row updated by Update is larger than the PCTFREE value of the data block, you need to apply for a second block, thus forming a row migration.

When a row of data causes the current block to be relocated to another block (where there is plenty of space) due to the update statement, but a pointer is retained in the original block.

The pointer in the original block is required because the ROWID entry of the index still points to the original location.

Row migration is caused by update statements when there is insufficient pctfree space, and it is independent of insert and delete statements.

The consequence: the application needs to access more data blocks, and the performance is degraded.

Prevention: 1. Increase the PCTFREE of the data block

two。 Expand the block size for tablespaces

Check: analyze table table name validate structure cascade into chained_rows

How do I know that a row link or row migration has occurred?

Analyze the table, and then look at the AVG_ROW_LEN column and CHAIN_CNT column of dba_tables. If it is not 0, a row migration or row link has occurred.

How do I determine whether a row migration or a line link has occurred?

Move's approach is adopted here:

If you move the table, then the column of the CHAIN_CNT changes to 0, and the blocks decreases, then only the row migration occurs.

If you move the table, and then the columns of the CHAIN_CNT remain the same, and the blocks remains the same, then only row chaining occurs.

There is also a special case where row migration and row linking occur at the same time (more extreme):

When update a table, the updated row is so large that a block cannot be saved, so it becomes a row migration, but because it is too large, more blocks are needed to store, and row linking occurs

If you move the table, then the columns of the CHAIN_CNT remain unchanged or decrease, and the blocks decreases, then both the row link and the row link occur.

Optimization:

Row migration: 1. If a row migration occurs during update, the table needs to be move (if there is an index, it needs to be rebuilt).

2. Exp/imp mode (for the table where row migration occurs)

3. Create a new temporary table for the data of the table in which the row migration occurs, and then insert the record back to the original table

Line links: can only be avoided by enlarging the BLOCK block

Row migration test: create table SQL > drop table T1 crystal table dropped.SQL > create table T1 (C1 varchar2 (20)); Table created. Insert data SQL > insert into T1 select''from dual connect by level

Commit;Commit complete. First analyze the T1 table to make sure that there is no row migration SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10SQL > analyze table T1 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10305 uses five blocks with a chain_cnt of 0, and no row migration occurs. Update empty column, and then analyze T1, with row migration SQL > update T1 set c1='oracle mysql';999 rows updated.SQL > commit;Commit complete.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 3 0 5SQL > analyze table T1 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 21 767 13 indicates that 767 of the 999 lines have been migrated and the number of blocks used has increased. Eliminate row migration:

Move is used to solve the problem here. If there is an index on the table and the index needs to be rebuilt, move will invalidate the index on the table:

After analyzing the SQL > alter table T1 move;Table altered.move table, the row migration disappears. SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 21 767 13SQL > analyze table T1 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16 0 6

Six blocks were used, from 13 blocks to 6 focus blocks reduced, and the CHAIN_CNT changed from 767 to 0, and row migration has been eliminated.

Summary: if the table has row migration during update, then the table needs to be eliminated by row migration, and the move operation can be used to eliminate row migration (if there is an index, it needs to be rebuilt).

Row link test: create table SQL > create table T2 (C1 varchar2 (4000), c2 varchar2 (4000), c3 varchar2 (4000)); Table created. Insert data SQL > insert into T2 select'arecords from dual connect by level commit;Commit complete from dual connect by level commit;Commit complete. First analyze the T1 table and make sure that there is no line link SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 10SQL > analyze table T2 compute statistics Table analyzed.SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2' PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 109 05 uses five blocks with a chain_cnt of 0, and no row chaining occurs. Insert more than 8K rows, and then analyze T2. There are line links SQL > insert into T2 values (lpad), lpad ('axiaoqiang 4000 values'), lpad ('axia`)); 1 row created.SQL > commit;Commit complete.SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2' PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 109 0 5SQL > analyze table T2 compute statistics;Table analyzed.SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2' PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 128 1 5

Indicates that the newly inserted 1 row of data has been migrated and the blocks used remain unchanged.

Remove line links:

Create a non-standard block size tablespace 16KSQL > show parameter cacheNAME TYPE VALUE-- client_result_cache _ lag big integer 3000client_result_cache_size big integer 0db_16k_cache_size big integer 0db_2k_cache_size big integer 0db_32k_cache_size big integer 1Gdb_4k_cache_size big integer 0db_8k_cache_size big integer 0db_ Cache_advice string ONdb_cache_size big integer 0db_flash_cache_file stringdb_flash_cache_size big integer 0db_keep_cache_size big integer 0db_recycle_cache_size big integer 0object_cache_max_size_percent integer 10object_cache_optimal _ size integer 102400result_cache_max_result integer 5result_cache_max_size big integer 12064Kresult_cache_mode string MANUALresult_cache_remote_expiration integer 0session_cached_cursors integer 50 set db_16k_cache_size to 16mSQL > alter system set db_16k_cache_size=16m System altered. Create tablespace create tablespace tabspace_16k blocksize 16Kdatafile'/ oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20m autoextend on extent management local segment space management auto; to move T2 table to tabspace_16k tablespace SQL > alter table T2 move tablespace tabspace_16k;Table altered. Analyze again to see SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 128 1 5SQL > analyze table T2 compute statistics Table analyzed.SQL > select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 128 0 4

Four blocks are currently used, and the chain_cnt is 0, indicating that the line link has been eliminated.

Row migration and row linking occur at the same time (more extreme):

Create table SQL > drop table t3bot table dropped.SQL > create table T3 (C1 int,c2 varchar2 (4000), c3 varchar2 (4000), c4 varchar2 (4000), c5 varchar2 (4000)), Table created. Insert data SQL > insert into T3 select level,'' from dual connect by level commit;Commit complete. First analyze the T3 table to determine that there is no row migration and row link SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10SQL > analyze table T3 compute statistics;Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10605 uses 5 blocks and chain_cnt is 0 There is no row migration or row link to update the column. After analyzing T1, there is row migration or row link SQL > update T3 set c2 = LPAD ('1levels, 4000,' *'), c3 = LPAD ('1numbers, 4000,' *'), c4 = LPAD ('1numbers, 4000,' *'), c5 = LPAD ('1numbers, 4000,' *') 100 rows updated.SQL > commit;Commit complete.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 1060 5SQL > analyze table T3 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16030 100 496

It means that 100 of the 100 lines have been migrated or linked, and the number of blocks used has also increased.

How do I determine if a row migration or a line link has occurred?

Move's approach is adopted here:

If you move the table, then the column of the CHAIN_CNT changes to 0, and the blocks decreases, then only the row migration occurs.

If you move the table, and then the columns of the CHAIN_CNT remain the same, and the blocks remains the same, then only row chaining occurs.

There is also a special case where row migration and row linking occur at the same time (more extreme):

When update a table, the updated row is so large that a block cannot be saved, so it becomes a row migration, but because it is too large, more blocks are needed to store, and row linking occurs

If you move the table, then the columns of the CHAIN_CNT remain unchanged or decrease, and the blocks decreases, then both the row link and the row link occur.

Eliminate row migration: move is used here. If there is an index on the table and the index needs to be rebuilt, move will invalidate the index on the table: SQL > alter table T3 move;Table altered.move table, then analyze t3SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16030 496SQL > analyze table T3 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16044 100 234

234 blocks have been used, from 496 blocks to 234 CHAIN_CNT blocks, and the number of blocks has not changed, indicating that row migration has been eliminated, but there are also line link elimination.

Therefore, row migration and row linking occur at the same time.

The next step is to eliminate line links:

Consistent with the normal way to eliminate line links:

Create a non-standard block size tablespace 16KSQL > show parameter cacheNAME TYPE VALUE-- client_result_cache _ lag big integer 3000client_result_cache_size big integer 0db_16k_cache_size big integer 0db_2k_cache_size big integer 0db_32k_cache_size big integer 1Gdb_4k_cache_size big integer 0db_8k_cache_size big integer 0db_ Cache_advice string ONdb_cache_size big integer 0db_flash_cache_file stringdb_flash_cache_size big integer 0db_keep_cache_size big integer 0db_recycle_cache_size big integer 0object_cache_max_size_percent integer 10object_cache_optimal _ size integer 102400result_cache_max_result integer 5result_cache_max_size big integer 12064Kresult_cache_mode string MANUALresult_cache_remote_expiration integer 0session_cached_cursors integer 50 set db_16k_cache_size to 16mSQL > alter system set db_16k_cache_size=16m System altered. Create tablespace create tablespace tabspace_16k blocksize 16Kdatafile'/ oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20m autoextend on extent management local segment space management auto; to move T2 table to tabspace_16k tablespace SQL > alter table T3 move tablespace tabspace_16k;Table altered. Analyze again to see SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16044 234SQL > analyze table T3 compute statistics Table analyzed.SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3' PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS- 10 16018 0 107

Currently, 107 blocks are used, and the chain_cnt is 0, which means that the line link has been eliminated.

At this point, row migration and line links are eliminated.

This is the end of "what is SQL Line Migration and Line Link". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.