Oracle7 Server Tuning
Allocating Space in Data Blocks
Table data in the database is stored in data blocks. In this section, you will learn how to allocate space within data blocks for best performance. The following issues are discussed in this section:
- how to control data storage
- how to store data most efficiently based on your application
Migrated and Chained Rows
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
Dynamic space management, especially migration and chaining, is detrimental to performance:
- UPDATE statements that cause migration and chaining perform poorly.
- Queries that select migrated or chained rows must perform more I/O.
You can identify migrated and chained rows in a table or cluster by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. The definition of a sample output table named CHAINED_ROWS appears in a SQL script available on your distribution media. The common name of this script is UTLCHAIN.SQL, although its exact name and location may vary depending on your operating system. Your output table must have the same column names, datatypes, and sizes as the CHAINED_ROWS table.
To reduce migrated and chained rows in an existing table, follow these steps:
1. Use the ANALYZE command to collect information about migrated and chained rows. For example:
ANALYZE TABLE order_hist LIST CHAINED ROWS;
2. Query the output table:
SELECT *
FROM chained_rows
WHERE table_name = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... 0000186A.0003.0001 04-AUG-92
SCOTT ORDER_HIST ... 0000186A.0002.0001 04-AUG-92
SCOTT ORDER_HIST ... 0000186A.0001.0001 04-AUG-92
3. If the output table shows that you have many migrated or chained rows, follow these steps to eliminate migrated rows:
- 3.1 Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'ORDER_HIST');
- 3.2 Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
WHERE ROWID IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'ORDER_HIST');
- 3.3 Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
- 3.4 Drop the intermediate table:
DROP TABLE int_order_history;
4. Delete the information collected in step 1 from the output table:
DELETE FROM chained_rows
WHERE table_name = 'ORDER_HIST';
5. Use the ANALYZE command again and query the output table.
6. Any rows that appear in the output table are chained rows. You can eliminate chained rows by increasing your data block size. It may not be possible to avoid chaining in all situations. If your table has a LONG column or long CHAR or VARCHAR2 columns, chaining is often unavoidable.