Reclaiming Wasted Space

This section explains how to reclaim wasted space, and also introduces the Segment Advisor, which is the Oracle Database component that identifies segments that have space available for reclamation. The following topics are covered:

Understanding Reclaimable Unused Space

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

Objects with fragmented free space can result in much wasted space, and can impact database performance. The preferred way to defragment and reclaim this space is to perform an online segment shrink. This process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated. The segment remains available for queries and DML during most of the operation, and no extra disk space need be allocated.

You use the Segment Advisor to identify segments that would benefit from online segment shrink. Only segments in locally managed tablespaces with automatic segment space management (ASSM) are eligible. Other restrictions on segment type exist. For more information, see "Shrinking Database Segments Online".

If a table with reclaimable space is not eligible for online segment shrink, or if you want to make changes to logical or physical attributes of the table while reclaiming space, you can use online table redefinition as an alternative to segment shrink. Online redefinition is also referred to as reorganization. Unlike online segment shrink, it requires extra disk space to be allocated. See "Redefining Tables Online" for more information.

Using the Segment Advisor

The Segment Advisor identifies segments that have space available for reclamation. It performs its analysis by examining usage and growth statistics in the Automatic Workload Repository (AWR), and by sampling the data in the segment. It is configured to run during maintenance windows as an automated maintenance task, and you can also run it on demand (manually). The Segment Advisor automated maintenance task is known as the Automatic Segment Advisor.

The Segment Advisor generates the following types of advice:

If the Segment Advisor determines that an object has a significant amount of free space, it recommends online segment shrink. If the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without automatic segment space management, the Segment Advisor recommends online table redefinition.

If the Segment Advisor determines that a table could benefit from compression with the OLTP compression method, it makes a recommendation to that effect. (Automatic Segment Advisor only. See "Automatic Segment Advisor".)

If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows.

Note:

The Segment Advisor flags only the type of row chaining that results from updates that increase row length.

If you receive a space management alert, or if you decide that you want to reclaim space, you should start with the Segment Advisor.

Automatic Segment Advisor

The Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows.

The Automatic Segment Advisor does not analyze every database object. Instead, it examines database statistics, samples segment data, and then selects the following objects to analyze:

Tablespaces that have exceeded a critical or warning space threshold

Segments that have the most activity

Segments that have the highest growth rate

In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB in size and that have at least three indexes to determine the amount of space saved if the tables are compressed with the OLTP compression method.

If an object is selected for analysis but the maintenance window expires before the Segment Advisor can process the object, the object is included in the next Automatic Segment Advisor run.

You cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis. You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run, or adjust automated maintenance task system resource utilization. See "Configuring the Automatic Segment Advisor" for more information.

Running the Segment Advisor Manually

You can manually run the Segment Advisor at any time with Enterprise Manager or with PL/SQL package procedure calls. Reasons to manually run the Segment Advisor include the following:

You want to analyze a tablespace or segment that was not selected by the Automatic Segment Advisor.

You want to repeat the analysis of an individual tablespace or segment to get more up-to-date recommendations.

You can request advice from the Segment Advisor at three levels:

Segment level—Advice is generated for a single segment, such as an unpartitioned table, a partition or subpartition of a partitioned table, an index, or a LOB column.

Object level—Advice is generated for an entire object, such as a table or index. If the object is partitioned, advice is generated on all the partitions of the object. In addition, if you run Segment Advisor manually from Enterprise Manager, you can request advice on the object's dependent objects, such as indexes and LOB segments for a table.

Tablespace level—Advice is generated for every segment in a tablespace.

The OBJECT_TYPE column of Table 19-2 shows the types of objects for which you can request advice.

Running the Segment Advisor Manually with Enterprise Manager

You must have the OEM_ADVISOR role to run the Segment Advisor manually with Enterprise Manager. There are two ways to run the Segment Advisor:

Using the Segment Advisor Wizard

This method enables you to request advice at the tablespace level or object level. At the object level, you can request advice on tables, indexes, table partitions, and index partitions. Dependent objects such as LOB segments cannot be included in the analysis.

Using the Run Segment Advisor command on a schema object page.

For example, if you display a list of tables on the Tables page (accessible from the Schema page), you can select a table and then select the Run Segment Advisor command from the Actions menu.

This method enables you to include the schema object's dependent objects in the Segment Advisor run. For example, if you select a table and select the Run Segment Advisor command, Enterprise Manager displays the table's dependent objects, such as partitions, index segments, LOB segments, and so on. You can then select dependent objects to include in the run.

In both cases, Enterprise Manager creates the Segment Advisor task as an Oracle Database Scheduler job. You can schedule the job to run immediately, or can take advantage of advanced scheduling features offered by the Scheduler.

To run the Segment Advisor manually with the Segment Advisor Wizard:

From the database Home page, under Related Links, click Advisor Central.

Follow the wizard steps to schedule the Segment Advisor job, and then click Submit on the final wizard page.

The Advisor Central page reappears, with the new Segment Advisor job at the top of the list under the Results heading. The job status is SCHEDULED or RUNNING. (If you do not see your job, use the search fields above the list to display it.)

Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page repeatedly. (Do not use your browser's Refresh icon.)

When the job status changes to COMPLETED, select the job by clicking in the Select column, and then click View Result.

Running the Segment Advisor Manually with PL/SQL

You can also run the Segment Advisor with the DBMS_ADVISOR package. You use package procedures to create a Segment Advisor task, set task arguments, and then execute the task. You must have the ADVISOR privilege. Table 19-1 shows the procedures that are relevant for the Segment Advisor. Please refer to Oracle Database PL/SQL Packages and Types Reference for more details on these procedures.

Use this procedure to create the Segment Advisor task. Specify 'Segment Advisor' as the value of the ADVISOR_NAME parameter.

CREATE_OBJECT

Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type. Table 19-2 lists the parameter values for each type of object.

Note: To request advice on an IOT overflow segment, use an object type of TABLE, TABLEPARTITION, or TABLESUBPARTITION. Use the following query to find the overflow segment for an IOT and to determine the overflow segment table name to use with CREATE_OBJECT:

select table_name, iot_name, iot_type from dba_tables;

SET_TASK_PARAMETER

Use this procedure to describe the segment advice that you need. Table 19-3 shows the relevant input parameters of this procedure. Parameters not listed here are not used by the Segment Advisor.

EXECUTE_TASK

Use this procedure to execute the Segment Advisor task.

Table 19-2 Input for DBMS_ADVISOR.CREATE_OBJECT

Input Parameter

OBJECT_TYPE

ATTR1

ATTR2

ATTR3

ATTR4

TABLESPACE

tablespace name

NULL

NULL

Unused. Specify NULL.

TABLE

schema name

table name

NULL

Unused. Specify NULL.

INDEX

schema name

index name

NULL

Unused. Specify NULL.

TABLE PARTITION

schema name

table name

table partition name

Unused. Specify NULL.

INDEX PARTITION

schema name

index name

index partition name

Unused. Specify NULL.

TABLE SUBPARTITION

schema name

table name

table subpartition name

Unused. Specify NULL.

INDEX SUBPARTITION

schema name

index name

index subpartition name

Unused. Specify NULL.

LOB

schema name

segment name

NULL

Unused. Specify NULL.

LOB PARTITION

schema name

segment name

lob partition name

Unused. Specify NULL.

LOB SUBPARTITION

schema name

segment name

lob subpartition name

Unused. Specify NULL.

Table 19-3 Input for DBMS_ADVISOR.SET_TASK_PARAMETER

Input Parameter

Description

Possible Values

Default Value

time_limit

The time limit for the Segment Advisor run, specified in seconds.

Any number of seconds

UNLIMITED

recommend_all

Whether the Segment Advisor should generate findings for all segments.

TRUE: Findings are generated on all segments specified, whether or not space reclamation is recommended.

FALSE: Findings are generated only for those objects that generate recommendations for space reclamation.

TRUE

Example The example that follows shows how to use the DBMS_ADVISOR procedures to run the Segment Advisor for the sample table hr.employees. The user executing these package procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege.

Note that passing an object type of TABLE to DBMS_ADVISOR.CREATE_OBJECT amounts to an object level request. If the table is not partitioned, the table segment is analyzed (without any dependent segments like index or LOB segments). If the table is partitioned, the Segment Advisor analyzes all table partitions and generates separate findings and recommendations for each.

Viewing Segment Advisor Results

The Segment Advisor creates several types of results: recommendations, findings, actions, and objects. You can view results in the following ways:

With Enterprise Manager

By querying the DBA_ADVISOR_* views

By calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure

Table Table 19-4 describes the various result types and their associated DBA_ADVISOR_* views.

Table 19-4 Segment Advisor Result Types

Result Type

Associated View

Description

Recommendations

DBA_ADVISOR_RECOMMENDATIONS

If a segment would benefit from a segment shrink, reorganization, or compression, the Segment Advisor generates a recommendation for the segment. Table 19-5 shows examples of generated findings and recommendations.

Findings

DBA_ADVISOR_FINDINGS

Findings are a report of what the Segment Advisor observed in analyzed segments. Findings include space used and free space statistics for each analyzed segment. Not all findings result in a recommendation. (There may be only a few recommendations, but there could be many findings.) When running the Segment Advisor manually with PL/SQL, if you specify 'TRUE' for recommend_all in the SET_TASK_PARAMETER procedure, then the Segment Advisor generates a finding for each segment that qualifies for analysis, whether or not a recommendation is made for that segment. For row chaining advice, the Automatic Segment Advisor generates findings only, and not recommendations. If the Automatic Segment Advisor has no space reclamation recommendations to make, it does not generate findings. However, the Automatic Segment Advisor may generate findings for tables that could benefit from OLTP compression.

Actions

DBA_ADVISOR_ACTIONS

Every recommendation is associated with a suggested action to perform: either segment shrink, online redefinition (reorganization), or compression. The DBA_ADVISOR_ACTIONS view provides either the SQL that you need to perform a segment shrink or table compression, or a suggestion to reorganize the object.

Objects

DBA_ADVISOR_OBJECTS

All findings, recommendations, and actions are associated with an object. If the Segment Advisor analyzes more than one segment, as with a tablespace or partitioned table, then one entry is created in the DBA_ADVISOR_OBJECTS view for each analyzed segment. Table 19-2 defines the columns in this view to query for information on the analyzed segments. You can correlate the objects in this view with the objects in the findings, recommendations, and actions views.

If any recommendations are present, select a tablespace and click Recommendation Details.

The Recommendation Details page appears. (See Figure 19-4.) You can initiate the recommended activity from this page (shrink or reorganize).

To view Segment Advisor results with EM—Specific run:

Start at the Advisor Central page.

If you ran the Segment Advisor with the Enterprise Manager wizard, the Advisor Central page appears after you submit the Segment Advisor task. Otherwise, to get to this page, on the Database Home page, under Related Links, click Advisor Central.

Check that your task appears in the list under the Results heading. If it does not, complete these steps (See Figure 19-2):

In the Search section of the page, under Advisor Tasks, select Segment Advisor in the Advisory Type list.

In the Advisor Runs list, select All or the desired time period.

(Optional) Enter a task name.

Click Go.

Your Segment Advisor task appears in the Results section.

Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page until your task status shows COMPLETED. (Do not use your browser's refresh icon.)

The Chained Row Analysis page appears, showing all segments that have chained rows, with a chained rows percentage for each.

Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views

The headings of Table 19-5 show the columns in the DBA_ADVISOR_* views that contain output from the Segment Advisor. Refer to Oracle Database Reference for a description of these views. The table contents summarize the possible outcomes. In addition, Table 19-2 defines the columns in the DBA_ADVISOR_OBJECTS view that contain information on the analyzed segments.

Before querying the DBA_ADVISOR_* views, you can check that the Segment Advisor task is complete by querying the STATUS column in DBA_ADVISOR_TASKS.

(Note: This finding is for objects with reclaimable space that are not eligible for online segment shrink.)

Allocated Space:xxx: Used Space:xxx: Reclaimable Space :xxx

Perform re-org on the object object, estimated savings is xxx bytes.

Perform reorg

The object has chained rows that can be removed by re-org.

xx percent chained rows can be removed by re-org.

-

-

Compress object object_name, estimated savings is xxx bytes.

(This outcome is generated by the Automatic Segment Advisor only)

Compress object object_name, estimated savings is xxx bytes.

-

The command to execute. For example: ALTERTABLET1COMPRESSFOROLTP

For this finding, see also the ATTR2 column of DBA_ADVISOR_ACTIONS.

Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS

The ASA_RECOMMENDATIONS procedure in the DBMS_SPACE package returns a nested table object that contains findings or recommendations for Automatic Segment Advisor runs and, optionally, manual Segment Advisor runs. Calling this procedure may be easier than working with the DBA_ADVISOR_* views, because the procedure performs all the required joins for you and returns information in an easily consumable format.

The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:

Configuring the Automatic Segment Advisor

The Automatic Segment Advisor is an automated maintenance task. As such, you can use Enterprise Manager or PL/SQL package procedure calls to modify when (and if) this task runs. You can also control the resources allotted to it by modifying the appropriate resource plans.

You can call PL/SQL package procedures to make these changes, but the easier way to is to use Enterprise Manager.

To configure the Automatic Segment Advisor task with Enterprise Manager:

Log in to Enterprise Manager as user SYSTEM.

On the Database Home page, under the Space Summary heading, click the numeric link next to the label Segment Advisor Recommendations.

Viewing Automatic Segment Advisor Information

The following views display information specific to the Automatic Segment Advisor. For details, see Oracle Database Reference.

View

Description

DBA_AUTO_SEGADV_SUMMARY

Each row of this view summarizes one Automatic Segment Advisor run. Fields include number of tablespaces and segments processed, and number of recommendations made.

DBA_AUTO_SEGADV_CTL

Contains control information that the Automatic Segment Advisor uses to select and process segments. Each row contains information on a single object (tablespace or segment), including whether the object has been processed, and if so, the task ID under which it was processed and the reason for selecting it.

Shrinking Database Segments Online

You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:

The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operations are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object. You enable row movement in a table with the ALTERTABLE ... ENABLEROWMOVEMENT command.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:

Before invoking online segment shrink, view the findings and recommendations of the Segment Advisor. For more information, see "Using the Segment Advisor".

You invoke online segment shrink with Enterprise Manager (EM) or with SQL commands in SQL*Plus. The remainder of this section discusses the command line method.

Note:

You can invoke segment shrink directly from the Recommendation Details page in EM. (See Figure 19-4.) Or, to invoke segment shrink for an individual table in EM, display the table on the Tables page, select the table, and then click Shrink Segment in the Actions list. (See Figure 19-1.) Perform a similar operation in EM to shrink indexes, materialized views, and so on.

You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTERTABLE, ALTERINDEX, ALTERMATERIALIZEDVIEW, or ALTERMATERIALIZEDVIEWLOG statement with the SHRINK SPACE clause.

Two optional clauses let you control how the shrink operation proceeds:

The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package.

As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause.

Examples

Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE employees SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB segment only:

ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);

Shrink a single partition of a partitioned table:

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

Shrink an IOT index segment and the overflow segment:

ALTER TABLE cities SHRINK SPACE CASCADE;

Shrink an IOT overflow segment only:

ALTER TABLE cities OVERFLOW SHRINK SPACE;

See Also:

Oracle Database SQL Language Reference for the syntax and restrictions of the ALTERTABLE, ALTERINDEX, ALTERMATERIALIZEDVIEW, and ALTERMATERIALIZEDVIEWLOG statements with the SHRINK SPACE clause

Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use the SPACE_USAGE procedure for more accurate information on unused space.