Systems Engineering and RDBMS

Online Segment Shrinking in 10g

Posted by decipherinfosys on June 11, 2007

Oracle 10g introduced a new feature of shrinking database segments online to reclaim the unused space below the high water mark (level at which blocks were used at some point. We can consider it as a point between used and un-used space within a segment).  This is a very cool feature, considering the options we had prior to 10g to reclaim the space back either by dropping and re-creating the table, by using exp/imp or by moving table to different tablespace using ‘ALTER TABLE ..MOVE’ command. All these operations are offline operations.

On the other hand, shrinking segments is an online operation. DML activities like UPDATE and DELETE can cause some free space in the segment which cannot be reused for new data, if it cannot fit into this space. In such cases data is put into new blocks and HWM is adjusted accordingly.  Over a period of time, such pockets of fragmented space results into more wasted space and performance issues. Shrinking segment process comes to rescue at this point. We can perform DML operation when command is being executed and hence no down time.

Shrinking segments works in two parts. First it compact the segment by consolidating free space and second it readjust the HWM to de-allocate the space. One thing to remember is, shrinking segments are available only for segments in locally managed tablespaces with automatic segment space management (ASSM).  Think of it as if Oracle is reading the table from the bottom and going up while reading it…the moment it hits the first row (the last row in the table), it would take it and re-insert it as close to the top as possible.  When it runs out of space at the top, it will then stop since all the free space is now at the bottom of the table.  It can then re-draw the HWM and thus release the allocated space.

Online segment shrinking does not work on
•    Tables with function based indexes.
•    Tables with ROWID based materialized views.
•    IOT mapping tables.

Let’s consider an example to illustrate this feature.  First step to check is whether ASSM is available for the tablespace or not. Connect to SQL*Plus and issue the following statement.

SQL> SELECT tablespace_name, Extent_Management, Segment_Space_Management
2    FROM user_tablespaces
3   WHERE tablespace_name = ‘USERS’
4  /

TABLESPACE_NAME                EXTENT_MAN SEGMEN
—————————— ———- ——
USERS                          LOCAL      AUTO

Segments in this tablespace are eligible for online shrinking as segment space management is set to auto. Let us create new table, index and check the space usage.

SQL> CREATE TABLE TEST
2  AS
3  SELECT* FROM all_objects;

Table created.

SQL> CREATE INDEX TEST_IND_1 ON TEST(OBJECT_NAME);

Index created.

Now we will check space usage by segment.

SQL> SELECT segment_name,segment_Type,bytes,blocks
2    FROM user_segments
3   WHERE segment_name IN (‘TEST’, ‘TEST_IND_1′);

SEGMENT_NAME    SEGMENT_TYPE         BYTES     BLOCKS
————— ————— ———- ———-
TEST            TABLE              6291456        768
TEST_IND_1      INDEX              3145728        384

Let us delete some data to create free space which we can reclaim by shrinking segment online.

SQL> DELETE FROM TEST WHERE ROWNUM <= 20000;

20000 rows deleted.

SQL> COMMIT;

Execute the same query shown above to check the space and it will return exact same results. Process of shrinking segments, physically move rows around so, we need to enable the row movement for the table.

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

Table altered.

Now let us issue the command to shrink the segment.

SQL> ALTER TABLE TEST SHRINK SPACE CASCADE;

Table altered.

Let us re-visit the user_segment table to check for space utilization. Issue above mentioned query and you will see that number on BYTES and BLOCKS are reduced.

SQL> SELECT segment_name,segment_Type,bytes,blocks
2    FROM user_segments
3   WHERE segment_name IN (‘TEST’, ‘TEST_IND_1′);

SEGMENT_NAME    SEGMENT_TYPE         BYTES     BLOCKS
————— ————— ———- ———-
TEST            TABLE              3342336        408
TEST_IND_1      INDEX              1376256        168

Here we have used CASCADE clause with SHRINK SPACE command to shrink space from dependent objects as well. Segment space is compacted, HWM is re-set and unused space is return to the database. If CASCADE option is omitted, it will not shrink the space for dependent objects. We need to issue this command separately on dependent objects.

There is another clause COMPACT which can be used with SHRINK SPACE command.  COMPACT clause shrink the space and compact the segment but it does not reset the HWM and delays it to the later time. An exclusive lock is acquired on the segment when HWM is reset for a very small period of time. So if application has long running queries, this option is particularly useful. In order to reset the HWM later, SHRINK SPACE command need to be issued again without COMPACT clause.

About these ads

2 Responses to “Online Segment Shrinking in 10g”

  1. http://pressposts.com/News/Online-Segment-Shrinking-in-10g/

    Submited post on PressPosts.com – “Online Segment Shrinking in 10g”

  2. […] by decipherinfosys on March 13, 2009 We have touched upon online segment shrinking in Oracle 10g. Oracle came up with a new feature of shrinking temporary tablespace using SHRINK SPACE or SHRINK […]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 85 other followers

%d bloggers like this: