Lesson 5	Reducing chained rows
Objective	Describe the performance impact of chained rows.

Reducing chained rows and Performance Impact

The trouble with chained rows

Chained rows, rows that span more than one data block, are one of the most onerous problems in Oracle tuning. When an Oracle row has chained onto multiple data blocks, the disk I/O to retrieve a row doubles, causing a severe degradation in response time. Chained rows can occur under two conditions:

When the row length exceeds the db_block_size for the database. This occurs when rows have LONG or LONG RAW datatypes. The only way to remedy this type of chained row is to increase the block size for the whole database.
When a row that contains a VARCHAR column is expanded with an SQL UPDATE statement. This happens when a row is stored with an empty VARCHAR column, and a subsequent update causes the row to expand. If there is not enough empty space on the data block (as specified by PCTFREE), then Oracle will chain the row onto multiple data blocks.

Example of chained row

The best way to prevent chained rows is to never use the VARCHAR or VARCHAR2 data types. Another alternative is to store blanks in columns where you later plan to update them with real data.

Detecting chained rows

View the Code below to see the script produced by running the @chain command produces.

spool chain.lst;

set pages 9999;

column c1 heading " owner "   format a9;

column c2 heading " table "   format a12;

column c3 heading " pctfree " format 99;

column c4 heading " pctused " format 99;

column c5 heading " avg row " format 99,999;

column c6 heading " rows "    format 999,999,999;

column c7 heading " chains "  format 999,999,999;

column c8 heading " pct "     format .99;

set heading off;

select 'Tables with chained rows and no RAW columns.' from dual;

set heading on;

select 

   owner              c1, 

   table_name         c2, 

   pct_free           c3, 

   pct_used           c4, 

   avg_row_len        c5, 

   num_rows           c6, 

   chain_cnt          c7,

   chain_cnt/num_rows c8

from dba_tables

where

owner not in ('SYS','SYSTEM')

and

table_name not in
 (select table_name from dba_tab_columns
   where
 data_type NOT in ('RAW','LONG RAW')

 )

and
chain_cnt gt; 0
order by chain_cnt desc
;

Fortunately, detecting chained rows is very simple. If you run the ANALYZE command against your table, you will populate the CHAINED_ROWS column of the DBA_TABLES view with chained row information. The following diagram contains an explanation for Chained rows.

chained_rows: The number of chained rows in the table.

avg row: The average row length.

rows: The number of rows, from num_rows in dba_tables.

pct: The percentage of chained rows.

Tables with Chained Rows

Here we see those tables that have chained rows. Note that the script omits tables that have RAW or LONG RAW datatypes since these will usually chain if the row length is greater than the block size. Fortunately, the remedy is quite simple. Individual tables can be reorganized with export-import or by using CTAS to copy the table. To remedy chained rows:

Increase PCTFREE to allow room for the rows to expand on the same data block
Re-define the columns without VARCHAR or VARCHAR2 data types
Store default blanks in VARCHAR columns

Be careful if you are using optimzer_mode=choose (the default) and you ANALYZE tables. This can change your queries to use the cost-based optimizer mode. The next lesson looks at table freelists and the performance implications of freelist imbalances.

Reducing Chained Rows - Exercise

Reducing Chained Rows - Exercise
Before moving on to the next lesson, try the following matching Exercise to see how well you understand block concepts.