Avoid Chained Rows

What is 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 of 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Some conditions that will cause row chaining are:


1- Tables whose row size exceeds the blocksize
2- Tables with long and long raw columns are prone to having chained rows
3- Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
In statpack?

u can take a look in a report a metric called “Table Fetch Continued Row”, this metric give a general
indication of database migrated/chained rows. When chaining becomes excessive, additional
I/O is required to service a query, and the DBA should increase PCTFREE for the table and
reorganize the table to remove the chains
How to indentify?

One way to identify chained rown in a specific table is using script
utlchain.sql ($ORACLE_HOME\RDBMS\ADMIN), this script create a table called
CHAINED_ROWS where store the ROWID, table name, scheme of all chained rows.

the procedure is:

1. Run script utlchain.sql (creation of table CHAINED_ROWS)
SQL> @?/rdbms/admin/utlchain.sql

2. Identify chained riows executing command:
ANALYZE TABLE DWH.DT_IND_OPERARIOS_DIA LIST CHAINED ROWS; <– USE DBMS_STAT

3. List results with next query:
SELECT owner_name,
table_name,
cluster_name,
head_rowid
FROM chained_rows
WHERE table_name = ‘[TABLE_NAME]’;

Once identify the rows you can proceed to fix problem:

1. Create a table for insert chained rows, is easy cause you have ROWID.

Example of Query:
SQL> CREATE TABLE temp_table AS SELECT * FROM [table_name]
WHERE rowid in (SELECT head_rowid FROM chained_rows);

2. Delete row from [table_name] you can use next command:

DELETE FROM [table_name] WHERE rowid in (SELECT head_rowid FROM chained_rows);

3.  Inser again the rows:
INSERT INTO [table_name] SELECT * FROM  temp_tablel;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s