Improve database performance with Oracle segment space management

Deleting database rows can create small data gaps that lead to slower table scans. Find out how to use Oracle segment space management to fix this.

Oracle Database tables with heavily fragmented data waste disk space and can result in moderate to severe performance...

implications. That is when Oracle segment space management, and in particular the Oracle Database segment shrink function, can help.

Updating or deleting rows in a table can create pockets of empty space in data blocks. Some of the empty space will be reused by later inserts. However, deletes and inserts of varying sizes can create gaps small enough that they remain unused indefinitely. Alternately, in tables where a significant percentage of the rows have been deleted, the segment will contain large numbers of sparse (or empty) blocks below the high water mark. This will result in very inefficient full table scans.

The online segment shrink functionality is the preferred method for Oracle segment space management by reclaiming fragmented free space below the high water mark in a segment. Online segment shrink is a different operation from the DEALLOCATE UNUSED clause of the ALTER TABLE, INDEX or CLUSTER commands. The deallocate operation simply frees space above the high water mark in a segment. Shrinking a segment will consolidate the fragmented free space below the high water mark and compact the data. The high water mark will be moved back and the reclaimed space will be released. After the shrink operation, data in the segment will be contained in a smaller number of blocks. This will result in better cache utilization and fewer blocks being read during full table scans.

Oracle segment space management using segment shrink

A segment shrink can be performed during business hours while the table is in use. It does not interfere with data manipulation language (DML) operations or queries and does not require additional disk space. At the very end of the shrink operation, concurrent DML operations will be blocked for a short period of time while space is being de-allocated. All of the indexes for the segment are maintained during the shrink operation. They will remain usable after it has completed.

The shrink operation will physically move rows in the datafile to new locations. In order for this to be possible, row movement in the object must be enabled with the ALTER TABLE.ENABLE ROW MOVEMENT command. You must also disable any rowid-based triggers on the segment. In addition, shrink operations can only be performed on segments in locally managed tablespaces with automatic segment space management. Online segment shrink cannot be performed on several segment types, including: compressed tables, IOT mapping tables, SecureFile LOBs, tables with one or more function-based indexes, and tables with rowid-based materialized views.

The easiest method to identify segments that would benefit from online segment shrink is to use the Segment Advisor. The Segment Advisor examines usage and growth statistics in the Automatic Workload Repository and samples the data in segments in order to identify candidates that have space available for reclamation. By default the advisor will run during maintenance windows as an automated maintenance task. It is also possible to run it manually. If the advisor locates a segment that has a significant amount of free space that can be reclaimed, it will recommend an online segment shrink. If the object is not eligible for a segment shrink, the advisor will instead recommend an online table redefinition (also referred to as reorganization). The end result of performing an online redefinition is very similar to that of an online segment shrink. However, redefinition requires extra disk space to be allocated during the operation.

Oracle segment space management can shrink space in a table, index-organized table, index, partition, subpartition, materialized view or materialized view log. The operation is performed using the SHRINK SPACE clause in the appropriate ALTER statement (i.e., ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW or ALTER MATERIALIZED VIEW LOG). In addition, the SHRINK SPACE clause has two optional keywords that control the behavior of the shrink operation:

  • COMPACT -- When specified, this clause divides the shrink segment operation into two phases. The database will defragment the segment space and compact the table rows, but the shrink operation will not reset the high water mark or deallocate space. This is most commonly done in a database that may have long-running queries against the table that might span the operation and attempt to read from reclaimed blocks. At a time of low database activity, a second SHRINK SPACE operation can be executed without the COMPACT keyword to complete the process.
  • CASCADE -- This will cause the segment shrink operation to act on all dependent segments of the object. For example, shrinking a table with CASCADE would also perform segment shrink operations against any indexes on that table.

The syntax and restrictions of the ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW and ALTER MATERIALIZED VIEW LOG statements with the SHRINK SPACE clause are available in the Oracle Database SQL Language Reference.

Dig Deeper on Oracle database performance problems and tuning