Question: How to I prevent chained and migrated rows in
Oracle?
Answer:
First, the prevention techniques for chained rows vs. migrated rows is a
bit different. Note that both chained rows and migrated
(relocated) rows manifest as "table fetch continued row" in v$sysstat
and
stats$sysstat for STATSPACK and
dba_hist_sysstat for AWR.
-
Preventing chained rows - Chained rows can occur when a row is
to large for a data block. In these cases, moving large
objects into a tablespace with a larger blocksize can often relieve
chained rows.
-
Preventing migrated rows - Migrated rows occur when a row
expands (usually with a varchar2 data type), and there is not enough
reserve defined by PCTFREE for the row to expand. In this case, you
prevent future relocated (migrated) rows by adjusting the PCTFREE to
ensure that future rows will have enough room to expand and then
reorganize the existing table (using data pump, CTAS or
dbms_redefinition) to remove the fragments.
On some
tables which are stored tiny and grow huge, you may need to set PCTFREE
to a "large" value, so that only one row is stored per block. For
example, if I have a row with a varchar2 that is stored at 2k and grows
to 30k, I would need to use a 32k blocksize and set PCTFREE=95 so that
only one rows is stored on each data block. That way, at update
time, there will be room for the row to expand without fragmenting.
Operationally, Oracle consultant Steve Adams offers this tip for finding
the difference between chained and migrated rows:
"You
can tell the difference between row migration and chaining by
listing the chained rows with ANALYZE table LIST CHAINED ROWS and
then fetching the first column from each "chained row" in a single
query.
The
count of continued row fetches will be incremented for every
migrated row, but not for most chained rows (unless the first cut
point happens to fall with the first column, which should be rare)."
See my
notes here for
finding chained rows.
and include the URL for the page.
