How do you detect Row Chaining and Row Migration? ~ Oracle DBA Secrets

Row Migration:

Row migration refers to rows that were moved to another block due to an update making them too large to fit into their original blocks. Oracle will leave a forwarding pointer in the original block so indexes will still be able to find the row. The rowid of the migrated row does not change. Thus, ROWID which is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

Row Chaining:

A chained row is a row that is too large to fit into a single database data block. For example, if you use a 4KB blocksize for your database, and you need to insert a row if 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Oracle has provided three ways to detect the Row Chaining and Row Migration.

The ANALYZE command
The dynamic views
Report.txt method

SQL> SELECT count(e) FROM row_mig_chain_demo;

COUNT(E)

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

SQL>

SQL> 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

Analyze the table to verify the chain count of the table:

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL>

SQL> SELECT chain_cnt FROM user_tables WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

CHAIN_CNT

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

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of table fetch continued row since instance startup?

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

SQL> SELECT 'Chained or Migrated Rows = '||value FROM v$sysstat WHERE name = 'table fetch continued row';

'CHAINEDORMIGRATEDROWS='||VALUE

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

Chained or Migrated Rows = 2272

How many Rows in a Table are chained?

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL> 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 1009 10 40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

List Chained Rows

You can look at the chained and migrated rows of a table using the ANALYZE statement with the LIST CHAINED ROWS clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS clause. These results are useful in determining whether you have enough room for updates to rows.

Creating a CHAINED_ROWS Table

To create the table to accept data returned by an ANALYZE ... LIST CHAINED ROWS statement, execute theUTLCHAIN.SQL or UTLCHN1.SQL script in $ORACLE_HOME/rdbms/admin. These scripts are provided by the database. They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SQL> 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

);

Table Created

After a CHAINED_ROWS table is created, you specify it in the INTO clause of the ANALYZE statement.

SQL> ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT owner_name,

table_name,

head_rowid

FROM chained_rows;

OWNER_NAME TABLE_NAME HEAD_ROWID

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

SCOTT ROW_MIG_CHAIN_DEMO AAAPVIAAFAAAAkiAAA

SCOTT ROW_MIG_CHAIN_DEMO AAAPVIAAFAAAAkiAAB

How to Avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to grow. Reorganizing the table ensures that the main free space is totally empty blocks.

The ALTER TABLE ... MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those that cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.

1. Alter Table Move

First, count the number of Rows per Block before the ALTER TABLE MOVE

SQL> 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, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:

SQL> ALTER TABLE row_mig_chain_demo MOVE

PCTFREE 20

PCTUSED 40

STORAGE (INITIAL 20K

NEXT 40K

MINEXTENTS 2

MAXEXTENTS 20

PCTINCREASE 0);

Table altered.

Again count the number of Rows per Block after the ALTER TABLE MOVE

SQL> 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

2. Rebuild the Indexes for the Table

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid, and new statistics should be collected after moving the table.

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

ERROR at line 1:

ORA-01502: index 'SYS.SYS_C007354' or partition of such index is in unusable state

This is the primary key of the table which must be rebuilt.

SQL> ALTER INDEX SYS_C007354 REBUILD ;

Index altered.

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL> 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 33.33 1009 20 40

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

1. Create the CHAINED_ROWS table

[oracle@rac01 ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@rac01 admin]$ sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 3 00:07:16 2024

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @utlchain.sql

Table created.

2. Analyse all or only your Tables

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

FROM user_tables

2 3

'ANALYZETABLE'||TABLE_NAME||'LISTCHAINEDROWSINTOCHAINED_ROWS;'

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

ANALYZE TABLE ICOL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE IND$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE TAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE CLU$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE LOB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE SUBCOLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE NTAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE REFCON$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE OPQTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

11 rows selected.

SQL> ANALYZE TABLE ICOL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE IND$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE TAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE CLU$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE LOB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE SUBCOLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE NTAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE REFCON$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE OPQTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

Table analyzed.

3. Show the RowIDs for all chained rows

This will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREE.

OWNER_NAME TABLE_NAME ROW_COUNT

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

SYS COL$ 1

SYS IND$ 7

SYS TAB$ 5

Conclusion: Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Row migration is typically caused by UPDATE operation
Row chaining is typically caused by INSERT operation.
SQL statements that are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
To diagnose chained/migrated rows use ANALYZE command, query V$SYSSTAT view
To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

Please feel free to ask. thank you 🙂

Toufique Khan

Oracle DBA Secrets

Monday, June 03, 2024