Previous | Table of Contents | Next

Page 497

Object fragmentation is the umbrella term for three specific types of fragmentation: row migration, row chaining, and overextended segments (objects having a large number of extents or almost near the maxextents parameter). We discuss each of these in the following sections.

Understanding Row MigrationRow migration occurs when an update to a row makes the length of the row larger than the space available to it in the block. When this happens, the data for the row is migrated to a new data block. The address of this new row location is stored in the original row location. When Oracle needs to locate this row, it looks to the original location. It finds the pointer and goes to the new address and the data stored there. This method enables the RDBMS to keep the original ROWID of the row, as well as transparently handle the larger row. See Figure 21.6 for a pictorial representation.

FIG.21.6
An example of row
migration in blocks.

There are two problems with row migration:

You might wonder why the Oracle engine goes to the trouble of storing a pointer address for the row instead of moving the entire row block to the new location. If it did this, however, it would have to also change any references to dependent objects in the database, such as index blocks. Changing all dependent object references would be a much messier and error-prone operation on the database side than storing the pointer—which means we on the DBA side have to be aware and look out for these kinds of problems on our side.

Understanding Row ChainingRow chaining occurs when a row is too long to fit in any one block of the data segment. This results in the row being stored in a chain of one or more data blocks. Row chaining often occurs with large rows containing LONG, LONG RAW, or LOB datatypes. Refer to Figure 21.7 for more on chained rows in a table.

Eliminating Chained and Migrated RowsChained and migrated rows can pose a serious problem to the overall performance of your database installation. You can check for chained and migrated rows by issuing the following SQL statement:

analyze table customer list chained rows into chained_rows;

Page 498

FIG.21.7
Row length greater than
the block size, causing
row chaining.

To create the chained_rows table, execute utlchain.sql, located in the $ORACLE_HOME/rdbms/admin directory. When you run utlchain.sql, a table called chained_rows will be created, which you use as the destination of the SQL statement.

This command stores the row ID and table of all chained and migrated rows in the database into the chained_row table. Query the chained_row table directly to find specific information on the rows and tables.

Because migrated rows only occur when a row is updated to a larger value, you can eliminate all migrated rows by performing the following steps:

  1. Create a temporary table to hold the migrated rows. Use a SQL statement like the following:
    CREATE TABLE temp_emp AS
    SELECT * FROM emp WHERE rowid in
    (SELECT rowid FROM chained_rows
    WHERE table_name = `EMP');
    
  2. Delete the rows stored from the above statement from the master table:
    DELETE FROM emp WHERE rowid in
    (SELECT head_rowid FROM chained_rows
    WHERE table_name = `EMP');
    
  3. Insert the rows from the temporary table:
    INSERT INTO emp SELECT * FROM temp_emp;
    

This procedure eliminates all migrated rows. By deleting the rows from chained_rows and re-running the analyze statement, you can positively identify all rows that are chained.

The problem with this solution is that you waste all the space in the old blocks where these chained rows were originally stored. The inserted rows don't fit there and so all the deleted rows leave blocks of free space in the table where the old rows were placed. There are two big disadvantages to this type of row packing:

Page 499

An alternative to this is to store the data from the table into a temporary table, truncate the table, and insert the data back in. You could also drop the table and import the row data back in, but if you drop the table, you also must make sure you recreate all dependent objects as well.

Sizing of PCTFREE and PCTUSED is important when trying to avoid row chaining and migration. A rough calculation you can use to derive the PCTFREE is to assume a table has an average row length x bytes, and that y bytes are going to be updated after the original insertion. Given these requirements, the derivation for PCTFREE would be as follows:

PCTFREE=100*y/x

Applying practical values, if you have a table of row length 100 bytes and 21 bytes that are going to be updated later on, the value of PCTFREE would be 100*21/100, which would give us 21 percent.

Row chaining occurs when a row is too large to fit in any available database block. This is a harder fragmentation to solve and may not in fact be fixable. Many times the only solution is to rebuild the database with a larger block size, or take advantage of some of the new datatypes available in Oracle8, which enable you to store large data columns out of line with the rest of the table data.

Managing Rollback Segments

Management of the database rollback segments can be much more complex than other segments in the database. It is therefore important that the operational intricacies of the rollback segments are understood before we can look at strategies for managing these troublesome objects.

Rollback segments are used to provide read consistency (explained later in this chapter), transaction rollback, and database recovery (covered in Chapter 24, "Backup and Recovery"). They store the before image of data involved in a transaction. If the transaction wants to do away with all the changes, the information in the rollback segments can be used to restore the database to its prior state. This is referred to as transaction rollback.

NOTE
Rollback segments also permit other users to read the data that you're currently modifying.

Rollback segments, like any other segments, are located in a tablespace and made up of extents. Every database has a SYSTEM rollback segment, located in the system tablespace and created when the database is created. It is used when the database is created and cannot be taken offline or dropped. You should create additional rollback segments for the normal functioning of the database—it is common for rollback segments to extend and contract, which is not the type of activity you want to happen in your SYSTEM tablespace.

Rollback segments must be tuned to handle concurrent transactions as well transactions of different sizes. Typically, you need more rollback segments with more extents on systems with

Previous | Table of Contents | Next