Spotlight on Oracle 10.6 - Getting Started Guide

Continued Fetch Rate Alarm

Under certain circumstances, Oracle may be forced to migrate a row from its original block to a new block. This can happen, for instance, when a row is updated and the new row length is too great for the existing block. Less frequently, a row is so big that it cannot fit in a single block and must be chained across multiple blocks.

These migrated and chained rows cause additional unnecessary I/O: the index entries point to the original location, which must then be read to get the addresses of the real location. The Continued Fetch Rate alarm occurs when the number of these "continued row" fetches exceeds a threshold.

To reduce row migration and row chaining

You cannot eliminate row migration entirely — it occurs whenever an updated row in a table grows too long for the Oracle block where it resides. You can, however:

• Set the value of the PCTFREE parameter to minimize row migration.
• PCTFREE allows space for row growth by setting the minimum percentage of a data block that is reserved as free space for updates to rows that are already in that block. The preferred value for PCTFREE depends on how often the rows in a table are updated: if the rows are seldom or never updated, a low value may be adequate; rows that are updated often may need a higher value for PCTFREE.

• Remove all migrated rows from the table, and reinsert them into a new block.

  You can change PCTFREE as required, but this will not affect existing rows that already have too little free space. When you change PCTFREE, you should also use the statement

  ANALYZE TABLE table_name LIST CHAINED ROWS

  to reduce or eliminate migrated rows in an existing table. For more information refer to the Oracle Database Help Center. See the Oracle Database Administrator's Guide | Managing Schema Objects | Analyzing Tables, Indexes, and Clusters | Eliminating Migrated or Chained Rows in a Table.

You can eliminate chained rows (which, by definition, are too large to fit any single data block) only by increasing the data block size.