Space-Management Tips and Tricks

Oracle recommends using automatic segment-space management for permanent tablespaces. These tablespaces are locally managed tablespaces and are also known as bitmap tablespaces. Locally managed tablespace (LMT) uses bitmaps at the extent level and ASSM uses bitmap at the segment level.

When you create database objects (tables and indexes), specify automatic segment-space management in the creation script itself. This helps Oracle to provide better performance for the database.

If, after using Segment Advisor, you find that there is space available in the object for reclamation, you should compact and shrink the segments. You may also deallocate free space at the end of a database segment. Segment shrink is available through the OEM as well as through the command-line interface.

The database will perform poorly during table scans and query operations when its segments have unused space above and below the high-water mark (HWM). When a segment is shrunk, the data is compacted and the HWM is pushed down to release unused space. This online operation does not take extra database space to run. With a shrink operation, row migration also could be reduced to a certain extent.

In ASSM, there are two types of high-water marks: Low High Water Mark (LHWM) and High High Water Mark (HHWM). Low High Water Mark is the one before which all the blocks are used. High High Water Mark is the other HWM where some unused blocks may be there before that. You can consider that from block 1 to LHWM, all the blocks are used and from LHWM to HHWM, some blocks may not be touched or utilized. This is because of the tree structure of the data blocks in ASSM where L1 blocks will have the actual data blocks and few of them may be unused, but still within HHWM.

Does the Shrink Operation Significantly Improve Database Performance?

Oracle Database 10g performs shrink operations to reclaim segment space. For the shrink operation to be successful, the segments must reside on automatic segment spacemanaged tablespaces. Even though the HWM is pushed to a lower limit for all shrunk segments, it may not touch all segment blocks. Hence, online shrink operations may not always result in significant performance improvements.

During the shrink operation, the database will compact the segment, adjust the high-water mark, and release the reclaimed space. You can optionally specify COMPACT or CASCADE clauses to control the shrink operation. The database actions associated with the COMPACT and CASCADE clauses are explained here:

• With the COMPACT clause, Oracle Database will defragment the segment space and compact the table rows. But it will postpone resetting of HWM and reallocation of the space to a future time. The defragmentation and compaction results are saved to disk. When you reissue the SHRINK SPACE clause without the COMPACT clause during maintenance hours, the database completes the resetting of the HWM and the reallocation of the space.

• With the CASCADE clause, the segment-shrink operation happens on all dependent segments of the object (like indexes for a table).

In the following code, substitute TABLE, INDEX, or CLUSTER for the OBJECT type and add the Object_name:

 ALTER OBJECT Object_name SHRINK SPACE CASCADE; 

Compact data will lead to better cache utilization and better performance. For full table scans, there will be fewer blocks to be scanned.

The DBA can deallocate unused space using the following command. Substitute TABLE, INDEX, or CLUSTER for OBJECT, and add an Object_name and a value for integer.

 ALTER OBJECT Object_name DEALLOCATE UNUSED KEEP integer; 

The optional KEEP clause can be used to specify the amount of retained space in the segment. Using the DBA_FREE_SPACE view to verify, you can check that the deallocated space is available for use by other objects.

You should run the UNUSED_SPACE procedure of the DBMS_SPACE package to get information about HWM and unused space in a segment. You can then deallocate unused space in that segment for other objects. For locally managed tablespaces using automatic segment-space management, run the DBMS_SPACE.SPACE_USAGE procedure to get information on unused space.

You can also set up database alerts to proactively manage the disk space used by tablespaces as discussed in Chapter 6, "Monitoring with Automatic Statistics Collection," under the heading "Metrics Monitoring and Customization." For temporary tablespaces, the threshold value should be set up as a limit on the used space. For read-only or offline tablespaces, you should not set up alerts because you cannot do any space reclamation on these tablespaces.

Limitations for Online Segment-Shrink Operations

You can only shrink segments in automatic segment spacemanaged tablespaces. Even in ASSM tablespaces, you cannot shrink the following objects:

• Tables in clusters

• Tables having long columns, or on-commit materialized views, or rowid-based materialized views

• LOB indexes

For Dictionary-Managed Tablespaces

If you are unable to use automatic segment-space management and still want to improve performance for segments, you will need to review the free lists or free list groups. A free list is a list of free data blocks whose free space has not reached the PCTFREE or whose used space is below PCTUSED. In other words, a free list is a linked list structure of data blocks associated with a segment that are eligible for accepting data when a new insert request comes.

The default value for the FREELISTS parameter is 1, while the maximum number depends on the block size. If you have to improve performance, increase the number of free lists or use the FREELIST GROUPS parameter. Increasing free lists will help only when there are too many concurrent sessions doing DMLs on that segment. For RAC, make sure that each instance has its own free list group.