Re: PCTFREE=0 and updates

Sybrand Bakker wrote:
>
> Decreasing PCTFREE to 0 will likely result in chained rows (ie rows in
> more than 1 Oracle block)
> The effect of chaining on performance is big enough to avoid reducing
> PCTFREE to 0. Please reconsider.

Just to be clear, you mean migrated rows, right? Not chained? Reducing PCTFREE will likely result in more *migrated* rows, but will typically *decrease* the number of chained rows. Migrated rows are those that grew during an update such that they exceeded the amount of usable free space in the block and had to be moved entirely out of that block and placed in another block (or blocks), leaving behind only a pointer to the new location. A chained row is merely one that doesn't entirely fit in the usable free space of a block during an insert and is spread across multiple blocks. Therefore *increasing* PCTFREE (i.e. reserving more room in the block for updates, and thereby reducing the amount of usable freespace for inserts) will likely result in chained rows, while decreasing PCTFREE typically reduces the likelihood that a row will be chained across blocks but increase the chance it will be migrated during an update.

If your row is bigger than your blocksize, you simply can't avoid chaining. But migrated rows hurt worse since you have to waste a read on the original block only to find the row is no longer there and you have to go read it's new block (which itself may be migrated, etc.).

Regards,
Sean Received on Wed Apr 10 2002 - 23:35:42 CDT