Database Reference
In-Depth Information
In contrast, the second technique gives precise information about the migrated and chained rows. Unfortunately,
it requires the execution of the
ANALYZE TABLE LIST CHAINED ROWS
statement for each table potentially containing
chained or migrated rows. If chained or migrated rows are found, their rowid is inserted into the
chained_rows
table.
Then, based on the rowids, as shown in the following query, it's possible to estimate the size of the rows and, from
that, by comparing their size with the block size, recognize whether they're migrated or chained.
SELECT vsize(
<col1>
) + vsize(
<col2>
) + ... + vsize(
<coln>
)
FROM <
table>
WHERE rowid = '
<rowid>
'
Alternatively, as a rough estimation, it's also possible to look at the
avg_row_len
column in a view like
dba_tables
.
If the average row length is close or even larger than the block size, it's likely that there are chained rows.
■
as discussed in Chapter 8, the
chain_cnt
column of the data dictionary views like
dba_tables
should
provide the number of chained and migrated rows. Unfortunately, this statistic isn't gathered by the
dbms_stats
package.
if
chain_cnt
isn't set, the package sets it to 0. Otherwise, the package doesn't modify
chain_cnt
at all. the
chain_cnt.sql
script demonstrates this behavior. although the only way to populate
chain_cnt
with correct values is to
execute the
ANALYZE TABLE COMPUTE STATISTICS
statement, this will cause all object statistics for the analyzed table to
be overwritten. this isn't recommended practice.
Caution
Solutions
The measures applied to avoid migration are different from those applied for chaining. Because of this, let me stress
that you have to determine whether the problem is caused by migration or chaining before taking measures.
Preventing row migration is possible. It's only a matter of correctly setting
PCTFREE
or, in other words, of reserving
enough free space to fully store the modified rows in the original blocks. This way, if you have determined that you're
are experiencing row migration, you should increase the current values of
PCTFREE
. To choose a good value, you
should estimate the average row growth. To do that, you should know their average size at the time when they're
inserted and their average size once they're no longer being updated.
To remove migrated rows from a table, there are two possibilities. First, you can completely reorganize the table
with export/import or
ALTER TABLE MOVE
. Second, you can copy only the migrated rows into a temporary table
and then delete and reinsert them into the original table. The second approach is especially useful when a small
percentage of the rows are migrated and there isn't enough time or resources to fully reorganize the table.
Avoiding row chaining is much more difficult. The obvious measure to apply is to use a larger block size.
Sometimes, however, even the largest block size isn't large enough. In addition, if chaining is due to the number of
columns being greater than 255, only a redesign can help. Therefore, in some situations, the only possible workaround
to this problem is to place infrequently accessed columns at the end of the table and thereby avoid accessing all pieces
every time.
Block Contention
Block contention, which occurs when multiple processes vie for access to the same blocks at the same time, can lead
to poor application performance. Block contention can sometimes be alleviated by manipulating the physical storage
parameters for a table or for an index. This section describes those situations in which an application can experience
block contention, and shows how to identify and prevent it.
