This content has been marked as final. Show 7 replies
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]
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.
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 ?
DarkFiBrE72,1 person found this helpful
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 --
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
Hi, The Db block size is 8k
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 --