Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Identifying Oracle Tables with Migrated/Chained Rows

Don Burleson 

This article pertains to tables designed to store row data in CHAR format rather than variable length VARCHAR columns.  Hence, we should not see chained rows except in cases where row length exceeds the database block size.  The identification of these tables that do have migrated/chained rows is important because of their use of RAW and LONG RAW data columns. 

As far as performance goes, the presence of migrated or chained rows can severely affect performance.  With SQL, for example, any statements that either query or create/update migrated or chained rows will be adding I/O overhead to the system, thus degrading performance.  It is best to quickly identify and rectify situations involving migrated or chained rows.  Chained rows can also add overhead to the performance of indexes and full-table scans.

Migrated rows and chained rows are similar, but each has a different performance impact on the database.  Attempting to update a row with data that will make it too large for the block causes the data to be migrated to a space where it will fit after the update. All that is left in the original block is the forwarding information.

Simply put, chained rows happen when a row is too big to fit into a single database block. Chained rows usually result from an insert.  For example, if the blocksize for the database is 4 kilobytes and there is an 8 kilobyte row to be inserted, Oracle will break the data into pieces and store it in 3 different blocks that are chained together.  There is forwarding information available to allow the database to collect all of the bits of chained row information from multiple blocks.

 There are a couple of other conditions that can contribute to chained rows:

  • Tables with RAW and LONG RAW columns are historically more prone to ending up with  chained rows.

  • Super-wide tables also end up with chained rows.  Tables with in excess of 255 columns end up with chained rows.  These wide tables get broken up into pieces.  As a result, there will be data on two or more blocks that has to be chained together. 

Specific information on migrated or chained rows is available as follows:

  • Listing 5-5 shows a list of all tables that contain migrated/chained rows.

  • Listing 5-6 shows the script that generates the migrated/chained rows report.  Note that this script does not include tables that contain RAW or LONG column datatypes, since such columns commonly span database blocks and will chain rows regardless of a database reorganization.

A properly tuned database should not have any row chaining, so the report shown in Listing 5-5 may be used as a database integrity check. As you can see, all of the migrated/chained rows are located in user-defined tables beginning with the letter “Z”.

Owner     Table        PCTFREE PCTUSED avg row         Rows       Chains  Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3     ZG_TAB            10      40      80        5,003        1,487  .30
TSTR3     ZMM               10      40     422       18,309          509  .03
TSTR3     Z_Z_TBLS          10      40      43          458           53  .12
TSTR3     USR03             10      40     101          327           46  .14
TSTR3     Z_BURL            10      40     116        1,802           25  .01
TSTR3     ZGO_CITY          10      40      56        1,133           10  .01 
6 rows selected.

Listing 5-5. A report of tables with migrated or chained rows

The following code generated the report on migrated/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.

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 migrated/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','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

Listing 5-6. The chain.sql script to detect tables with migrated/chained rows

Because of the large row length of some of the cluster tables, you will see a lot of chained rows in tables that contain RAW and LONG RAW columns.  These chained rows are caused by the row length exceeding the db_block_size, forcing the huge rows to chain onto many blocks. 

  • Listing 5-7 shows tables that contain chained rows caused by row length in excess of db_block_size.

  • Listing 5-8 contains the script that generates the report containing tables with chained rows caused by excessive row lenght compared to db_block_size.

These types of chained row tables are commonly found in the specific tablespace(s), and database reorganization will not reduce the chained rows. Unfortunately, there is no remedy to these chained rows, provided that you are using the maximum supported Oracle blocksize for your version of UNIX.

For example, from listing 5-7, we see that the D010L table has an average row length of over 13k. Because most UNIX operating systems do not support blocksizes greater than 8k, and as a result, there will be chained rows.

Owner     Table        PCTFREE PCTUSED avg row         Rows       Chains  Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3     KOCLU             40      60   1,981      597,125      472,724  .79
TSTR3     CDCLS             40      60     809      712,810      328,989  .46
TSTR3     VBFCL             40      60   1,398      340,917      285,930  .84
TSTR3     EDIDOC            40      60   2,211      158,426      114,859  .73
TSTR3     D010S             10      40   5,129       76,635       43,791  .57
TSTR3     TST03             40      60   6,559       44,596       22,298  .50
TSTR3     T512CLU           40      60   1,055       24,393       21,344  .88
TSTR3     D020L             10      40   1,629       84,968        6,294  .07
TSTR3     EUDB              40      60   3,068       13,910        6,028  .43
TSTR3     D010L             10      40  13,454        4,747        4,747   1
TSTR3     D022S             10      40     774      113,835        3,794  .03
TSTR3     D010Y             10      40   5,435        5,497        2,998  .55
TSTR3     D010Q             10      40   7,351        5,768        2,596  .45
TSTR3     D010T             40      60   1,220       39,550        1,720  .04
TSTR3     DSYO2             40      60   2,946          627          289  .46
TSTR3     SFHOA             40      60   3,247          319          152  .48
TSTR3     DSYO1             40      60   2,695          426           56  .13
TSTR3     RFDT              40      60   2,629          740           26  .04
TSTR3     INDX              40      60   2,288          302           20  .07
TSTR3     MACOB             40      60     694          103            2  .02
TSTR3     USR04             40      60      74          434            1  .00

Listing 5-7. A report of migrated/chained rows in tables that contain RAW columns.

set heading off;
select 'Tables with migrated/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
table_name in
 (select table_name from dba_tab_columns
where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;
 
 
spool off;

Listing 5-8. The chain1.sql script to identify migrated/chained rows in tables  that contain RAW or LONG RAW columns.

Bobby Durrett has this script to detect if a current session is accessing high volume of chained rows (table fetch continued row fetch):

--  get the required session ID
variable monitored_sid number;


begin

SELECT
   sid into :monitored_sid

from
   v$session

where
   audsid=USERENV('SESSIONID');


end;
/

select
   b.value "table fetch continued rows"

from
   v$sesstat b
where
   b.SID = :monitored_sid

and
   b.statistic# =
   (select
      statistic#
    from
      v$statname

    where
      name='table fetch continued row');

 
 
 
Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publisher.
 

 

 


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.