Managing Database Blocks (Oracle)

Use

You manage database blocks in your Oracle database to avoid chained rows. A chained row is spread over multiple database blocks.

Prerequisites

Chained rows are often caused by an incorrectly set PCTFREE value. To give a simplified example, with a database block size of 8 KB and a PCTFREE value of 25 (that is, 25% is kept free), 6 KB is available to store new rows in an empty block (insert operation). If a row has a length of 7 KB it is split in two pieces, with 6 KB stored in one block and 1 KB in a second block. If PCTFREE were set to 10% (7.2 KB available), the row would be stored in one block, so avoiding chaining.

Procedure

...

       1.      Monitor and adjust PCTFREE.

Adjust PCTFREE to reduce or eliminate chaining. This requires a single-table reorganization only. Use SQLPLUS to check average row length and number of chained rows in the Oracle view DBA_TABLES and adjust the table when it is still small. This reduces the required reorganization time.

For more information about reorganizations, see Reorganizing Objects (Oracle).

       2.      Distinguish chained rows from “migrated” rows.

A migrated row is caused when an update increases the size of a row such that it does not fit in the current block any more, leading to the entire row being moved (or “migrated”) to another data block. Since the row’s index entry still points to the original location, a pointer is set in the original location so that the new position can be found. Subsequent access to the migrated row now requires an extra read to locate the migrated row, causing performance deterioration. A table reorganization eliminates this problem since the index entries then point directly to the rows.

Result

You avoid chained rows and so improve performance for your Oracle database and the SAP system. Poor performance can lead to downtime so avoiding chained rows also reduces downtime.