|
 |
|
Oracle Tips by Burleson Consulting |
Monitoring Tables for
Chained Rows
Row chaining occurs as data is added to an
existing record. When there is insufficient room for the addition,
the row is chained to another block and added there. Block chaining
occurs when a row is too long to fit into a single block (such as
with long raw or LOB columns or when a row has more than 255
columns). If chaining is occurring regularly, it can lead to
significant performance degradation. This degradation is caused by
the requirement to read multiple blocks to retrieve a single record.
An example of a script to monitor a single table for chained rows is
shown in Source 10.10. Au: Confirm Source number here: the next
number in sequence is 10, not 11, but based on subsection below,
it’s difficult to determine if you intended calling this source out
of sequence. Fixed Note that this script is limited, in that the
table must have a primary or unique key defined in order for it to
work. With a companion script, all tables in an application can be
checked with this script. As an alternative, you can analyze the
tables of concern and use the table statistics report, as described
in the following subsection.
The ANALYZE Command
The ANALYZE command can also be used to
generate chained row information into the DBA_TABLES view. Actual
chained-row rowids can be listed in a separate table if desired. The
general format of this command follows:
See Code Depot
www.dba-oracle.com/oracle_scripts.htm |
