Donald K. Burleson Oracle Tips

Finding chained rows in Oracle tables

Finding and repairing chained rows is an important part of Oracle administration.  When an Oracle rows expands, it sometimes chains onto multiple data blocks.  Excessive row chaining can cause a dramatic increase in disk I/O because several I/O’s are required to fetch the block instead of one single I/O.

Of course, row chaining can be prevented by setting the PCTFREE storage parameter high enough to keep space on each data block for row expansion.

In cases where the data columns contain RAW and LONG RAW columns, row chaining may be unavoidable because the average row length may exceed the data block size.  That is why the query below filters out tables with RAW data types.

Here is the code that I use to generate a report showing all tables with excessive chained rows.  Note that the use of this script is predicated on the use of Oracle’s ANALYZE command to populate the chain_cnt and num_rows columns of the DBA_TABLES data dictionary view.

Once identified, the tables should be reorganized using Create Table As Select (CTAS) or by using the Oracle export-import utilities.

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 in ('RAW','LONG RAW')

    )

and

   chain_cnt > 0

order by

   chain_cnt desc

;

This will produce a nice report that shows tables that you will want to reorganize:

Owner  Table     PCTFREE PCTUSED avg row      Rows    Chains  Pct

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

SAPR3  ZG_TAB         10      40      80     5,003     1,487  .30

SAPR3  ZMM            10      40     422    18,309       509  .03

SAPR3  Z_Z_TBLS       10      40      43       458        53  .12

SAPR3  USR03          10      40     101       327        46  .14

SAPR3  Z_BURL         10      40     116     1,802        25  .01

SAPR3  ZGO_CITY       10      40      56     1,133        10  .01

6 rows selected.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm