In general, the PCTFREE parameter tells Oracle how much space should be reserved on a block for future updates. By default, this is ten percent. If there is a higher percentage of free space than the value specified in PCTFREE, then the block is considered to be free. PCTFREE tells Oracle how much space should be reserved on a block for future updates.
This means if we use an 8KB block size, as soon as the addition of a new row onto a block causes the free space on the block to drop below about 800 bytes, Oracle will use another block from the FREELIST instead of the existing block. This ten percent of the data space on the block is set aside for updates to the rows on that block. When you are using ASSM, PCTFREE limits if a new row may be inserted into a block, but it does not control whether a block is on a FREELIST or not, as ASSM does not use FREELISTs at all.
Note In ASSM, PCTUSED is simply ignored.
There are three settings for PCTFREE: too high, too low, and just about right. If you set PCTFREE for blocks too high, you will waste space. If you set PCTFREE to 50 percent and you never update the data, you have just wasted 50 percent of every block. On another table, however, 50 percent may be very reasonable. If the rows start out small and tend to double in size, setting PCTFREE too small will cause row migration as you update the rows.
Row Migration
What is row migration? Row migration is when a row is forced to leave the block it was created on because it grew too large to fit on that block with the rest of the rows. To illustrate row migration, we start with a block that looks like Figure 10-3.
Figure 10-3. Data block before update
Approximately one-seventh of the block is free space. However, we would like to more than double the amount of space used by row 4 via an UPDATE (it currently consumes one-seventh of the block). In this case, even if Oracle coalesced the space on the block as shown in Figure 10-4, there is still insufficient room to double the size of row 4, because the size of the free space is less than the current size of row 4.
Figure 10-4. Data block as it would appear after coalescing free space
If the row fit into the coalesced space, it would have happened. This time, however, Oracle will not perform this coalescing and the block will remain as it is. Since row 4 would have to span more than one block if it stayed on this block, Oracle will move, or migrate, the row. However, Oracle cannot just move the row; it must leave behind a forwarding address. There may be indexes that physically point to this address for row 4. A simple update will not modify the indexes as well.
Note There is a special case with partitioned tables that a rowid, the address of a row, will change. We will look at this case in Chapter 13. Additionally, other administrative operations such as FLASHBACK TABLE and ALTER TABLE SHRINK may change rowids assigned to rows as well.
Therefore, when Oracle migrates the row, it will leave behind a pointer to where the row really is. After the update, the blocks might look as shown in Figure 10-5.
Figure 10-5. Migrated row depiction
So, a migrated row is a row that had to move from the block it was inserted into onto some other block. Why is this an issue? Your application will never know; the SQL you use is no different. It only matters for performance reasons. If you go to read this row via an index, the index will point to the original block. That block will point to the new block. Instead of doing the two or so I/Os to read the index plus one I/O to read the table, you’ll need to do yet one more I/O to get to the actual row data. In isolation, this is no big deal—you won’t even notice it. However, when you have a sizable percentage of your rows in this state, with many users accessing them, you’ll begin to notice this side effect. Access to this data will start to slow down (additional I/Os and the associated latching that goes with the I/O add to the access time), your buffer cache efficiency goes down (you need to buffer two blocks instead of just the one you would if the rows were not migrated), and your table grows in size and complexity. For these reasons, you generally do not want migrated rows (but do not lose sleep if a couple hundred/thousand rows in a table of thousands or more rows are migrated).
It is interesting to note what Oracle will do if the row that was migrated from the block on the left to the block on the right in Figure 10-5 has to migrate again at some future point in time. This would be due to other rows being added to the block it was migrated to and then updating this row to make it even larger. Oracle will actually migrate the row back to the original block and, if there is sufficient space, leave it there (the row might become unmigrated). If there isn’t sufficient space, Oracle will migrate the row to another block altogether and change the forwarding address on the original block. As such, row migrations will always involve one level of indirection.
So, now we are back to PCTFREE and what it is used for: it is the setting that will help you to minimize row migration when set properly.