Database Reference
In-Depth Information
There's a second situation that causes row chaining: tables with more than 255 columns. In fact, the database
engine isn't able to store more than 255 columns in a single row piece. Consequently, whenever more than 255
columns have to be stored, the row is split into several pieces. This situation is particular, in that several pieces
belonging to the same row can also be stored in a single block. This is called
intra-block row chaining
. Figure
16-6
shows a row with three pieces (since it has 654 columns).
Figure 16-6.
Tables with more than 255 columns might cause intra-block row chaining
Note that migrated rows are caused by updates, while chained rows are caused by either inserts or updates. When
chained rows are caused by updates, the rows might be migrated and chained at the same time.
Problem Description
The impact on performance caused by row migration depends on the access path used to read the rows. If they're
accessed by rowid, the cost doubles. In fact, both row pieces have to be accessed separately. Instead, there's no
overhead if they're accessed through full scans. This is because the first row piece, which contains no data, is
simply skipped.
The impact on performance caused by chaining is independent of the access path. In fact, every time the first
piece is found, it's necessary to read all the other pieces through the rowid as well. There's one exception, however.
As discussed previously in the “Optimal Column Order” section, when only part of a row is needed, not all pieces may
need to be accessed. For example, if only columns stored in the first piece are needed, there's no need to access all
other pieces.
An overhead that applies to both migration and chaining is related to row-level locking. Every row piece has to be
locked. This means that the overhead due to the lock increases proportionally with the number of pieces.
Problem Identification
There are two main techniques for detecting migrated and chained rows. Unfortunately, neither is based on response
time. This means no information about the real impact of the problem is available. The first, which is based on the
v$sysstat
and
v$sesstat
views, merely gives a clue that somewhere in the database there are either migrated or chained
rows. The idea is to check the statistic that gives the number of fetches that read more than one row piece (including intra-
block chained rows), namely,
table fetch continued row
. To assess the relative impact of row chaining and migration,
