Segments Shrink
By Kimberly Floss

Shrink segments online and in real time.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

Now Shrink

With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment includes two key phases:

• Segment data is compacted. Through a series of INSERT and DELETE statements (during which DML-compatible locks are held on individual rows or blocks of the table), the segment data is moved as far to the beginning of the segment as possible. Given that rowids change, you must enable row movement and also disable any triggers based on rowid for table segments you want to shrink.
   
• High-water mark (HWM) is adjusted to an appropriate location (exclusive locks are held on the data at this point), and unused space is deallocated from the segment, so it is available for the tablespace to reallocate to other objects as needed.

The shrink capability is implemented in Oracle Database 10g as an optional SHRINK SPACE clause on the ALTER... SQL statements for the associated object. The SHRINK SPACE clause performs both phases of the shrink process. You can also use the optional COMPACT clause in conjunction with the SHRINK SPACE clause to perform just the first phase—the compacting—by itself, to defer the locking of the second phase, for example, and then issue the SHRINK SPACE clause (without COMPACT) later to complete the process, as in

ALTER TABLE KIMBERLY.PRODUCT 
SHRINK SPACE COMPACT

and later, run

ALTER TABLE KIMBERLY.PRODUCT 
SHRINK SPACE

But how do you identify which segments to shrink? And how do you make this process a regular part of maintaining system performance? Fortunately, with Oracle Database 10g, segment usage data is captured, by default, along with all the other statistical data captured by the Automatic Workload Repository (AWR) infrastructure. The segment usage information is easy to obtain with the advisor infrastructure (DBMS_ADVISOR)—specifically, the Segment Advisor, a simple-to-use new feature provided with Oracle Database 10g that identifies which segments have significant free space, and, therefore, are good candidates for segment shrinking.

Getting Started with the Oracle Database 10g Segment Advisor

You can run the Segment Advisor against specific objects (tables, indexes, and materialized views), against an entire tablespace, or against multiple tablespaces.

As with the other advisors provided by Oracle Database 10g, you can launch the Segment Advisor by using Oracle Enterprise Manager Database Control or the DBMS_ADVISOR built-in PL/SQL package.

You can launch the Segment Advisor from several places in the Enterprise Manager Database Control (or Grid Control) browser, such as Advisor Central (from the Enterprise Manager home page), or from the specific database object management page (the Tables or Index page, for example) by selecting Run Segment Advisor from the drop-down menu, with the specific table, index, or materialized view selected.

You can proactively run the Segment Advisor against the specific table you suspect may have a segment usage issue. For example, you might run the Segment Advisor if you've just purged 5,000 old accounts from a 25,000-customer table or run it against a complete tablespace you use as working storage, to stage or cleanse data for a data warehouse.

Whether you launch the Segment Advisor from Advisor Central or within the context of a specific object or tablespace, you initiate a four-page sequence of configuration pages in which you define a Segment Advisor task for submission to the job subsystem.

The four pages of the Segment Advisor wizard step you through the settings for defining the Segment Advisor task's parameters, including the following:

Advisor mode. Can be run in Limited or Comprehensive mode. In Comprehensive mode, when the task runs, the Segment Advisor samples the objects being analyzed, in addition to using already gathered statistics on the objects from the AWR. Limited mode relies on existing statistics only—so if your statistics aren't current (or don't exist at all), Segment Advisor will generate no recommendations.

Time. Limited or unlimited time for analysis by the Segment Advisor task.

Schedule. Whether to run the task immediately or schedule it for later, such as during a maintenance window (this is the default), and whether the task repeats. You can also change the system-generated task name on this page.

Review. Includes a list of your Segment Advisor settings and a Submit button to submit the new task to the job scheduler.

The task is also stored as an object in the AWR (the default retention is for 30 days), so you can rerun it anytime later.

Shrink the Segment

Once the task completes its analysis, you can review the advisor's findings on the Recommendations page (see Figure 1), available from Advisor Central. The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.

In the example in Figure 1, the Segment Advisor recommends shrinking several segments from different tablespaces (owned by different users). The page shows the segment name, the space allocated to the segment, the used space, the reclaimable space, and recommendations such as "Perform shrink, estimated savings is 14284326 bytes."

Two shrink options are available on this page. In this example, with an index segment selected, "Compact Segments and Release Space" is equivalent to:

ALTER INDEX <INDEX_NAME> SHRINK SPACE

and "Compact Segments" is equivalent to

ALTER INDEX <INDEX_NAME> SHRINK 
SPACE COMPACT

You can choose to implement the recommendation directly from this page, selecting as many tables, indexes, or other listed objects as you like and then clicking on the Schedule Implementation button.

The next page lets you set the time for shrinking the segment or segments selected. Your selection then goes to the job subsystem as a series of SQL statements executed immediately or per your schedule.

Whether you implement a recommendation or simply select Shrink Segment from the drop-down menu on another page of Enterprise Manager, the appropriate SQL statements are submitted to the job subsystem to shrink the segment.

You can also select "View other Segments" to view the status of all other segments and obtain this same level of detail, but in the case of segments in which the advisor doesn't find extra space, recommendations include information such as "The free space in the object is less than the size of the last extent" or "The object has less than 1% free space, it is not worth shrinking."

Using the DBMS_ADVISOR Built-in Package

The Segment Advisor wizard provided by Enterprise Manager Database Control (and Grid Control) uses the functionality of the DBMS_ADVISOR built-in PL/SQL package of the Oracle database. If you prefer, you can call the various subroutines of this package from the command line or by using scripts. Listing 1 shows a script that calls DBMS_ADVISOR and creates a Segment Advisor task.

The DBMS_ADVISOR built-in PL/SQL package, new with Oracle Database 10g, lets you create the complete array of advisor tasks, such as SQL Tuning Advisor and SQL Access Advisor, many of which previous Talking Tuning columns have discussed. The Segment Advisor is another subsystem (or set of procedures) available in that package.

Code Listing 1: Script for creating a Segment Advisor task on a table

VARIABLE ID NUMBER; 
BEGIN 
  DECLARE 
    TASK_ID NUMBER;
    NAME VARCHAR2(100) ; 
    DESCR VARCHAR2(500) ; 
    OBJID NUMBER;
   BEGIN 
    NAME := '' ; 
    DESCR := 'SEGMENT ADVISOR ON A TABLE'; 
    DBMS_ADVISOR.CREATE_TASK('SEGMENT ADVISOR', :ID, NAME, DESCR, NULL); 
    DBMS_ADVISOR.CREATE_OBJECT(NAME, 'TABLE', 'KIMBERLY','PRODUCT', NULL, NULL, OBJID); 
   DBMS_ADVISOR.SET_TASK_PARAMETER(NAME, 'RECOMMEND_ALL', 'TRUE'); 
DBMS_ADVISOR.EXECUTE_TASK(NAME); 
   END; 
END;

Conclusion

Whether you use Enterprise Manager or the DBMS_ADVISOR package, the Segment Advisor is easy to run and lets you quickly find areas in your database where you can reclaim space. It finds the pockets of reclaimable space in segments and then, rather than making you unload the table and rebuild it offline, lets you shrink segments online and in place. No additional storage is needed, because the operation effectively does an INSERT and a DELETE right in the object itself.