Donald K. Burleson Oracle Tips

Finding chained rows with table_fetch_continued_row

Most Oracle professionals monitor their table to see when row chaining occurs so they can reorganize the table to remove the chained rows.

In Oracle, there is a STATSPACK and utlestat value table_fetch continued_row.  This value is a system-wide counter that is supposed to increment whenever Oracle fetches a chained table rows.  Many DBA’s use this value to determine when to reorganize their databases because chained rows are the number one reason for table reorganizations.

However, there are some bugs in the Oracle8 software.

In Oracle 8.0, the ANALYZE command considers rows with more than 255 columns as chained rows.  This is due to internal "restriction" that a row piece cannot contain more than 255 columns.  This was fixed in Oracle 8.1.

But one statistic called "table fetch continued row" continues to bump when you select a table with a large number of columns.

This is a bug -- 858617 -- where oracle still increments the "table fetch continued row" statistics when you select a table with more than 100 columns although the selected rows are in the same block.  Oracle may fix this in Oracle 8.1.6.

Until then, the DBA is better off running a dictionary query to display dba_tables.chain_cnt for each table, and using that value to determine when to reorganize a table.

Here is a script that will list all chained rows:

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 > 10% 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

(chain_cnt/num_rows > .1 or chain_cnt > 1000)

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

;

set heading off;

select 'Tables with > 10% chained rows that contain 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 chain_cnt/num_rows > .1

and

table_name in

 (select table_name from dba_tab_columns

   where

 data_type in ('RAW','LONG RAW')

 )

and

chain_cnt > 0

order by chain_cnt desc

;

spool off;

Here is a sample of the output from this report:

Owner  Table        PCTFREE PCTUSED avg row    Rows  Chains  Pct

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

READER PAGE_IMAGE        10      40     890  24,486     612  .02

READER TOC_ENTRY         10      40      62   9,039     160  .02

ARSD   EC_CUSTOMER       10      40      46     533      51  .10

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