7 Replies Latest reply: Nov 12, 2008 9:28 AM by 181444 RSS

    Chained Rows ?

    587671
      I have run the following SQL

      select
      owner ,
      table_name,
      pct_free,
      pct_used,
      avg_row_len,
      num_rows,
      chain_cnt,
      chain_cnt/num_rows
      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

      It has return three tables that seem to have a chained row issue

      Owner

      Owner Table_Name Pct_Free Pct_Used Avg_Row_Len Num_Rows Chain_Cnt Chain_Cnt/num_Rows
      1 TABLE1 10 296 50938 131 0.002571754
      1 TABLE2 10 389 962928 117 0.000121504
      1 TAB3 10 92 362612 19 5.23976E-05

      My question is, should I be concerned about this.. and if so, How can I resolve it ?
        • 1. Re: Chained Rows ?
          Anand...
          TO remove the rows chaining you can

          1. Increase the PCTFREE of the table.

          2. ALTER TABLE ...MOVE . Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, so later rebuild the indexes on it.


          Refer to [http://www.akadia.com/services/ora_chained_rows.html]

          [http://www.dbasupport.com/forums/archive/index.php/t-11772.html]


          HTH
          Anand
          • 2. Re: Chained Rows ?
            26741
            In TABLE1, one quarter of 1 percent (0.25%) appear as CHAINED ROWS.

            I wouldn't be concerned.

            TABLE2 and TABLE3 have even lower counts.
            • 3. Re: Chained Rows ?
              587671
              Hi, Thanks for the reply, I did notice within toad an alert stating

              Chained Fetch Ratio 0.0267 PCTFREE too low for a table

              What shall I increase it to ?
              • 4. Re: Chained Rows ?
                181444
                DarkFiBrE72,

                The dba_tables.chained_rows column contains the count of both chained and migrated rows.

                Do not bother to rebuild tables that have chained rows since a chained row is a row that is too big to fit into a single Oracle block.

                A migrated row is a row that was too big to fit back into a block after being updated so Oracle had to move it to another block leaving a pointer to the new location behind. These are the rows of concern.

                So the first step is to determine if the chaining is chaining or migration. You listed the average row lenth so your first two tables appear to be migration. The next step which you also did is to see what percentage of the table data the migrated rows represent. For you first two tables the percentage is pretty insignificant so I do not think I would bother rebuilding either at this time.

                I would analyze the pctfree and pctused settings and make a note for future reference.

                Your third table has a very large average row length. It looks to be chaining rather than migration. What is your actual database block size? Depending on how the long rows are distributed rebuilding in this case is likely to not reduce the chain count.

                HTH -- Mark D Powell --
                • 5. Re: Chained Rows ?
                  26741
                  I see the AVG_ROW_LEN as 296 , 389 and 92 for the three tables. Not very large.

                  Edited by: Hemant K Chitale on Nov 12, 2008 10:48 PM
                  • 6. Re: Chained Rows ?
                    587671
                    Hi, The Db block size is 8k
                    • 7. Re: Chained Rows ?
                      181444
                      I misaligned the data to the headings so I was looking at the wrong value for the avg_row_len. Pct_used appears in the heading but apparently does not appear in the data. I just counted columns without looking at the values. My mistake.

                      The general comments on chaining, migration, and determing what the chained_rows column represents before performing work fortunately still applies.

                      HTH -- Mark D Powell --