Shulou(Shulou.com)06/01 Report--

I. Overview:

If your Oracle database performance is poor, row links and row migration may be one of the reasons. We can prevent this phenomenon by reasonably designing or adjusting the database.

Row linking and row migration are two potential problems that can be avoided. We can improve the performance of the database through reasonable adjustments. The main description of this article is:

What is row migration and line link

How to judge row migration and row links

How to avoid row migration and line links

Row migration affects the OLTP system when a single row is read using an index. In the worst case, for all read operations, an additional Iripple O is added. Row links affect index reads and full table scans.

Note: use a record to describe (easy to understand) when translating a line (row), such as the first line, use the first record.

II. Oralce block

The size of the operating system block is the minimum operating unit for reading and writing the operating system, and it is also one of the attributes of the operating system file. When creating a database, select an operating system block-based

The integer multiple size is the size of the Oracle database block. Oracle database read and write operations are based on Oracle blocks as the minimum unit, not operating system blocks. Once the size of the Oracle block is set

Cannot be changed throughout the lifetime of the database (except Oracle 9i). So customize a reasonable Oralce block size for the Oracle database, such as the expected total database size and the number of concurrent users

Factors should be taken into account.

The database block consists of the following logical structure (under the entire database structure)

SELECT xrem dpentry e FROM row_mig_chain_demo WHERE x = 3

SELECT a.name, b.value

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower (a.name) = 'table fetch continued row'

NAME VALUE

-

Table fetch continued row 2

Now when we extract data from the tail of record 3 through a primary key index scan, this will increase the value of table fetch continued row. Because you need to get data from the head and tail of the row to combine.

Now let's see if full table scans have the same effect.

SELECT * FROM row_mig_chain_demo

X

-

three

two

one

SELECT a.name, b.value

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower (a.name) = 'table fetch continued row'

NAME VALUE

-

Table fetch continued row 3

At this point, the value of table fetch continued row is increased because the tail of record 3 has to be fused. While records 1 and 2, even if there is a migration phenomenon, but because it is a full table scan

-- so the value of table fetch continued row is not increased.

SELECT x,a FROM row_mig_chain_demo

X

-

three

two

one

SELECT a.name, b.value

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower (a.name) = 'table fetch continued row'

NAME VALUE

-

Table fetch continued row 3

Table fetch continued row does not increase when the data to be extracted is the first two columns of the entire table. Because there is no need for data fusion of record 3.

SELECT x,e FROM row_mig_chain_demo

X

-

three

two

one

SELECT a.name, b.value

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower (a.name) = 'table fetch continued row'

NAME VALUE

-

Table fetch continued row 4

But when the columns d and e are extracted, the value of table fetch continued row is increased. Usually, it is easy to generate row migration when querying, even if there are real row links, because our query

-- the required columns are usually located in the first few columns of the table.

8. How to identify row links and row migration

Aggregate the tables created by statistics, which will also cause table fetch continued row to occur by refactoring the entire row

SELECT count (e) FROM row_mig_chain_demo

COUNT (E)

-

one

SELECT a.name, b.value

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower (a.name) = 'table fetch continued row'

NAME VALUE

-

Table fetch continued row 5

-- check the number of links on the table through analyze table

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

SELECT chain_cnt

FROM user_tables

WHERE table_name = 'ROW_MIG_CHAIN_DEMO'

CHAIN_CNT

-

three

-- 3 records are linked. Obviously, two of their records are migrated (record 1, record 2) and one record is linked (record 3).

-- the total number of table fetch continued row after the instance is launched

View v$mystat tells us how many times all tables have been table fetch continued row since the instance was started.

Sqlplus system/

SELECT 'Chained or Migrated Rows =' | | value

FROM v$sysstat

WHERE name = 'table fetch continued row'

Chained or Migrated Rows = 31637

The above query results show that there may be a row link on one table that has been fetch 31637 times, or there may be 31637 tables, each table has a row link, one fetch at a time. There are.

-- it may be a combination of the above.

31637 times may be good, maybe bad, just a value.

It depends on

How long has the database been started?

How many rows does this value account for as a percentage of the total extracted data?

If it accounts for 0.001% of your fetch from the table, it doesn't matter.

Therefore, it is necessary to compare table fetch continued row with the total number of extracted records.

SELECT name,value FROM v$sysstat WHERE name like'% table%'

NAME VALUE

-

Table scans (short tables) 124338

Table scans (long tables) 1485

Table scans (rowid ranges) 0

Table scans (cache partitions) 10

Table scans (direct read) 0

Table scan rows gotten 20164484

Table scan blocks gotten 1658293

Table fetch by rowid 1883112

Table fetch continued row 31637

Table lookup prefetch client count 0

9. How many rows are linked on a table?

By analyze the table (no analyze is null), you can get the number of linked records from the data dictionary user_tales.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

SELECT chain_cnt

Round (chain_cnt/num_rows*100,2) pct_chained

Avg_row_len, pct_free, pct_used

FROM user_tables

WHERE table_name = 'ROW_MIG_CHAIN_DEMO'

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED

--

3 100 3691 10 40

-- PCT_CHAINED is 100%, indicating that all lines are linked or migrated.

10. List link lines

When you use the list chained rows clause in analyze table, you can list linked rows on a table. The result of this command is that all links are stored on a list chained rows clause

Displays in the specified table. These structures help determine whether there is enough space to implement row updates in the future.

Create a CHAINED_ROWS table

Create one to store analyze... A table of the results of the list chained rows command, you can execute UTLCHAIN.SQL or UTLCHN1.SQL scripts located in the $ORACLE_HOME/rdbms/admin directory.

This script creates a table called chained_rows under the current schema

Create table CHAINED_ROWS (

Owner_name varchar2 (30)

Table_name varchar2 (30)

Cluster_name varchar2 (30)

Partition_name varchar2 (30)

Subpartition_name varchar2 (30)

Head_rowid rowid

Analyze_timestamp date

);

When the chained_rows table is created, you can use the analyze table command to point to the table as output.

How to avoid row links and row migration

Adding pctfree can help avoid line links. If we leave more free space for the block, there is room on the line to meet future growth. You can also reorganize tables that have a higher deletion rate.

Or rebuild the table index to avoid row links and row migration. If some rows on the table are deleted frequently, there will be more free space on the data block. When the inserted row is subsequently expanded, the inserted row may

Distributed to those deleted lines and there is still no more space for expansion. Reorganizing the table ensures that the main free space is a complete free block.

ALTER TABLE... The MOVE command allows data on an unpartitioned or partitioned table to be reassigned to a new segment. It can also be assigned to a different tablespace with quotas. This command also allows

You can modify some storage properties on a table or partition when you cannot use alter table. You can also use ALTER TABLE... The compress keyword in the MOVE command uses the compression option when storing to a new segment.

[sql] view plain copy

1. ALTER TABLE MOVE

Count the number of rows on each block before using alter table move.

SELECT dbms_rowid.rowid_block_number (rowid) "Block-Nr", count (*) "Rows"

FROM row_mig_chain_demo

GROUP BY dbms_rowid.rowid_block_number (rowid) order by 1

Block-Nr Rows

--

2066 3

Now remove the row links from the table, use alter table move to rebuild the row_mig_chain_demo table to a new segment, and specify some new storage options.

ALTER TABLE row_mig_chain_demo MOVE

PCTFREE 20

PCTUSED 40

STORAGE (INITIAL 20K

NEXT 40K

MINEXTENTS 2

MAXEXTENTS 20

PCTINCREASE 0)

Table altered.

-- count the rows on each block again after alter table move

SELECT dbms_rowid.rowid_block_number (rowid) "Block-Nr", count (*) "Rows"

FROM row_mig_chain_demo

GROUP BY dbms_rowid.rowid_block_number (rowid) order by 1

Block-Nr Rows

--

2322 1

2324 1

2325 1

two。 Rebuild the index on the table

Moving a table will change the rowid recorded on the table. This causes the index on the table to be set to the unusable state. DML statements that use indexes based on the table will receive an ORA-01502 error.

Therefore, the index on the table must be deleted or rebuilt. Similarly, the statistics on the table become invalid. Therefore, statistics should also be re-collected after the table has been moved.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

ERROR at line 1:

ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable state

The primary key on the table must be rebuilt

ALTER INDEX SYS_C003228 REBUILD

Index altered.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

Table analyzed.

SELECT chain_cnt

Round (chain_cnt/num_rows*100,2) pct_chained

Avg_row_len, pct_free, pct_used

FROM user_tables

WHERE table_name = 'ROW_MIG_CHAIN_DEMO'

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED

--

1 33.33 3687 20 40

If the table contains a LOB column, the user can specify that the command be moved (move) along with the LOB segment and the LOB index segment (related to the table).

-- when not specified, LOB data segments and LOB index segments do not participate in the move.

Detect row joins and row migrations on all tables

You can get row links and row migrations on all tables through the CHAINED_ROWS table.

1. Create a chained_rows table

Cd $ORACLE_HOME/rdbms/admin

Sqlplus scott/tiger

@ utlchain.sql

2.ananlyze all tables / or specified tables

SELECT 'ANALYZE TABLE' | | table_name | | 'LIST CHAINED ROWS INTO CHAINED_ROWS;'

FROM user_tables

/

ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTO CHAINED_ROWS

ANALYZE TABLE DEPT LIST CHAINED ROWS INTO CHAINED_ROWS

ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS

ANALYZE TABLE BONUS LIST CHAINED ROWS INTO CHAINED_ROWS

ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTO CHAINED_ROWS

ANALYZE TABLE DUMMY LIST CHAINED ROWS INTO CHAINED_ROWS

Table analyzed.

3. View the rowid of a line link

SELECT owner_name

Table_name

Count (head_rowid) row_count

FROM chained_rows

GROUP BY owner_name,table_name

/

OWNER_NAME TABLE_NAME ROW_COUNT

-

SCOTT ROW_MIG_CHAIN_DEMO 1

In this way, you can quickly locate the problem of how many rows are linked on a table. If you have more row links or row migrations, you should increase the value of pctfree or rebuild the table based on the table.

XIII. Conclusion:

Row migration affects the OLTP system to use indexes to read single rows. In the worst-case scenario, all reads are added with an extra Imax O. Row links affect index reads and full table scans.

Row migration is usually caused by update operations

Line links are usually caused by insert operations

A query or creation (such as an index) based on row chaining or row migration will degrade the performance of the database due to the need for more Imax O

Adjust the trial link or row migration to query the v$sysdate view using the analyze command

Move out line links or row migrations use larger pctfree parameters or use the alter table move command

XIV. About the author

Original link: The Secrets of Oracle Row Chaining and Migration

Martin Zahn, Akadia AG, Information Technology, CH-3672 Oberdiessbach

EMail: martin dot zahn at akadia dot ch

12.09.2007: Updated for Oracle 10.2

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.