Database Tables - Expert Oracle Database Architecture - page 365

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.
Setting PCTFREE and PCTUSED Values
Setting PCTFREE and PCTUSED is an important—and greatly overlooked—topic. In summary, PCTUSED and PCTFREE
are both crucial when using MSSM; with ASSM, only PCTFREE is. On the one hand, you need to use them to avoid
too many rows from migrating. On the other hand, you use them to avoid wasting too much space. You need to look
at your objects and describe how they will be used, and then you can come up with a logical plan for setting these
values. Rules of thumb may very well fail you on these settings; they really need to be set based on usage. You might
consider the following (keeping in mind that “high” and “low” are relative terms, and that when using ASSM, only
PCTFREE applies):
• High PCTFREE , low PCTUSED : This setting is for when you insert lots of data that will be updated,
and the updates will increase the size of the rows frequently. This setting reserves a lot of space
on the block after inserts (high PCTFREE ) and makes it so that the block must almost be empty
before getting back onto the FREELIST (low PCTUSED ).
• Low PCTFREE , high PCTUSED : This setting is for if you tend to only ever INSERT or DELETE from
the table, or if you do UPDATE , the UPDATE tends to shrink the row in size.
Again there are no hard and fast rules as to what is high and low with these parameters. You'll have to consider
the behavior of your application when setting PCTFREE and PCTUSED . The PCTFREE value can range from 0 to 99. A high
setting of PCTFREE might be something like 70 which means that 70% of the block will be reserved for updates. A low
value of PCTFREE might be something like 5, meaning you leave little space on the block for future updates (that make
the row grow in size). And the PCTUSED parameter can contain values between 0 and 99. A high setting of PCTFREE
might be in the range of 70 to 80. A low setting of PCTFREE would be somewhere around 10.
LOGGING and NOLOGGING
Normally, objects are created in a LOGGING fashion, meaning all operations performed against them that can generate
redo will generate it. NOLOGGING allows certain operations to be performed against that object without the generation
of redo; we covered this in the Chapter 9 in some detail. NOLOGGING affects only a few specific operations, such as the
initial creation of the object, direct-path loads using SQL*Loader, or rebuilds (see the Oracle Database SQL Language
Reference manual for the database object you are working with to see which operations apply).