Reduce Disk I/O | Process   «Prev  Next»
Lesson 7

Reorganizing Oracle Tables (Conclusion)

This module covered table reorganization and how you can improve Oracle performance by reorganizing tables. You should be able to:
  1. Re-sequence rows in an Oracle table
  2. Re-sequence rows using CTAS
  3. Remove table extents
  4. Describe the performance impact of chained rows
  5. Define the performance ramifications of freelists
Before taking a final quiz, review the main concepts of the module.

Module review

Here is a list of concepts that should now be familiar.
  1. For tables that always are read via an index range scan, re-sequencing data rows can have a great benefit.
  2. Rows can be re-sequenced by using the CTAS command with an index hit or with ORDER BY.
  3. There is some debate about whether having a table in many extents is bad.
  4. Numerous table extents can be fixed by table reorganization.
  5. Chained rows can wreak havoc in an Oracle database, doubling the amount of I/O.
  6. Chained rows occur when rows are longer than the db_block_size, but these chains can only be fixed by changing the whole database block size.
  7. Chained rows are commonly caused by tasks that expand an empty VARCHAR column with a data value.
  8. When a row expands, it first tries to find free spaces on the same block in the area reserved by PCTFREE.
  9. If there is no space on a block, the rows will chain onto another data block.
  10. You can count chained rows by ANALYZE on a table and viewing the chained_rows column in DBA_TABLES.
  11. A reorganization will coalesce chained rows.
  12. Multiple freelists for a table cause a sparse table, where a table with lots of free space will extend.
  13. For table with multiple freelists, deletes should be parallel in the same degree as the inserts. This will keep the freelists in balance.
  14. Setting PCTUSED too high will effectively re-use space, but at the cost of higher I/O.

Glossary

Here are the terms from this module that may be new to you:
  1. clustering factor: The relative sequence of the table rows to the index.
  2. chained rows: Rows that span more than one data block.

The next module will examine database configuration in relation to I/O.

Trace Files - Quiz

Before you go ahead, complete this Quiz to test your knowledge of CPU issues with Oracle databases.
Trace Files - Quiz