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

This article mainly explains "how to achieve data row migration and row link in Oracle database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to achieve data row migration and row link in Oracle database"!

There are two kinds of data storage phenomena in Oracle database: row migration and row linking. When these two phenomena are serious, the performance of database query will be seriously low. Dealing with row linking or row migration of data storage is also an effective way to improve database performance.

I. definition of row migration and row links:

1. Row Migration:

ORACLE the DEFAULT SIZE of a BLOCK is 8 K. in fact, a BLOCK cannot store 8 K of data. How much data a BLOCK can store is controlled by the PCTFREE,PCTUSED parameter (for previously manually managed tablespaces).

PCTFREE: refers to the percentage of free space reserved by BLOCK for UPDATE. For rows that have been inserted into the BLOCK, subsequent UPDATE operations may increase the length of the row; PCTFREE is the free space reserved to accommodate the increased length. If the PCTFREE can no longer accommodate the increased length of the row when UPDATE, ORACLE migrates the entire row to a new BLOCK, the ROWID of the row is retained (it is not quite clear why the ORACLE does not change the ROWID), and the original BLOCK has a pointer to the new BLOCK stored by the ROW. This is line migration.

It can be seen that the row migration is caused by the UPDATE operation. Literally, the so-called migration must exist before it can be called migration.

two。 Line links:

It means that a BLOCK cannot hold a row (the length of the row is too large), and the row must be stored in several BLOCK. Line links are usually generated during Insert. Whether a BLOCK can be used in insert is controlled by PCTUSED.

PCTUSED: refers to the percentage of BLOCK used for INSERT. For INSERT operations, the maximum space that BLOCK can use to hold new rows is Blocksize-pctfree-overhead. When the BLOCK data storage is higher than PCTUSED,ORACLE, the block will be removed from the free linked list, and will not be added to Freelist again until the used space of the block has dropped below PCTUSED (this is the principle of table space management free blocks that ORACLE used to manage manually, but now ORACLE recommends using ASSM).

Check the migration of rows in the table after analyzing the table:

Select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='EMP'

(the field CHAIN_CNT shows the number of rows in the table of row migration or row link)

2. Analysis and detection of row migration and row links:

To detect whether there is a row migration or row link in the table, you need to analyze the table:

There are two ways to analyze a table:

1, analyze table emp compute statistics;2, exec dbms_stats.gather_schema_stats ('scott','emp');-- the former is the user name and the latter is the table name

However, ORACLE's dbms_stats.gather_schema_stats only collects optimizer statistics and does not detect row migrations and row links in the table's records, so you must use the analyse command to analyze row links or row migrations.

Detection of row migration and row links:

Select b.NAME where a.STATISTIC#=b.STATISTIC# and b.NAME like a.Vale from v$mystat a journal statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'table fetch continued row'

When there is a return value, you can know that there are row migrations and row links in the data of the table.

Row migration and line link cleanup:

You can clear the row migration through REBUILD data:

Create table MM_PM_temp as select * from MM_PM; truncate table MM_PM; insert into MM_PM select * from MM_PM_temp

Then reanalyze the table:

Analyze table MM_PM compute statistics

Look at it after the analysis:

Select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round ((t.chain_cnt / t.num_rows) * 100,2) as "chained rows%" from user_tables t where t.chain_cnt > 0

If the chain_cnt of the table becomes 0, it means that the original chain_cnt is all row migrations, not row links.

If the chain_cnt decreases after the REBUILD data, but is still greater than 0, it can be proved that this table contains both row migrations and real row links.

It turns out that row migration can be cleared and reduced by REBUILD data and adding PCTFREE%.

Note that the ASSM,PCTUSED,FREELIST,FREELIST GROUPS parameter is ignored.

But for real row chaining, you can only migrate the table to a large BLOCSIZE table space.

For example:

Create a 16K tablespace:

CREATE TABLESPACE LARGETBS BLOCKSIZE 16 K LOGGING DATAFILE'/ data/app/oracle/oradata/ora33/LARGETBS_01.dbf' SIZE 64m AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

An ORA- error was reported when it was created, because we did not set the DB_Buffer_cache of 16. Let's set it:

Alter system set db_16k_cache_size=34603008

MOVE the table to the table space of 16K:

Alter table SRC_CS move tablespace LARGETBS; alter table MM_PM move tablespace LARGETBS

Because of the migration, the index of the table becomes invalid, so we want the REBUILD index:

Alter index PK_SRC_CS rebuild; alter index PK_MM_PM rebuild

Then re-analyze:

Analyze table SRC_CS compute statistics; analyze table MM_PM compute statistics

Re-query:

Select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round ((t.chain_cnt / t.num_rows) * 100,2) as "chained rows%" from user_tables t where t.chain_cnt > 0 order by t.table_name

It was found that none of these watches had ROW CHAIN. It can be seen that tablespaces from MOVE to 16K can clear ROW CHAIN.

3. Ways to eliminate migration:

One: generate a table (chained_rows) to save the rowid of the migrated rows

@? / rdbms/admin/utlchain

Use the parse command to insert the rowid that produces the migrated row into the chained_ rows table:

Analyze table test01 list chained rows into chained_rows;create table tmp as select * from test01 where rowid in (select head_rowid from chained_rows); delete test01 where rowid in (select head_rowid from chained_rows); insert into test01 select * from tmp;drop table tmp purge

Reanalyze the table:

Analyze table test01 compute statistics

Then look at the statistics in the dictionary:

Select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='TEST01'

Second, export the table with row migration to the database, truncate the original table, and then re-import the data.

At this point, I believe you have a deeper understanding of "how to achieve data row migration and row link in Oracle database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.