Online Segment Shrink

Online Segment Shrink

A segment is a logical storage structure that contains data structure (segment type) of database objects. A segment is a set (unordered) of extents. A segment and all its extents are stored in one tablespace.

From Oracle Database 10g, we can use the segment shrinking capability to reclaim unused space in tablespace. When we update, insert and delete on object within a tablespace can create pockets of empty space and that empty space also not large enough to use for new data. This empty space is called as fragmented free space.  This type of fragmentation in tablespace can impact on database performance. Online segment shrink is preferred way to reclaim this unused space.  This is a space saving feature in oracle database.

Remember only segments in locally managed tablespace with automatic segment space management (ASSM) are eligible. You cannot execute a shrink operation on segments managed by freelists. However, tables in clusters, tables with long columns, tables with on-commit materialized views, IOT overflow segments, and compressed tables cannot be shrunk.

Here we have a diagram which shows the before and after effect of shrinking a segment. The upper part of the diagram in the slide shows a sparsely populated segment. There’s some unused space both above and below the segments high water mark. You can make more free space available in tablespace by shrinking sparsely populated segments. When a segment is shrunk, its data is compacted, its high water mark pushed down, and unused space is released back to the tablespace containing the segment. Rowids, however, are not preserved, so when a segment is shrunk, the rows move inside the segment to different blocks causing the rowid to change.

Let’s look at some of the considerations around using this type of operation.

• A shrink operation occurs online
• It will maintain any indexes on the table while operation is running
• Although the table is not locked during the SHRINK SPACE operation and users can do DML on the table as usual
• A lot of before image created so it will consume space in UNDO tablespace
• This operation modifies so many blocks, therefore lots of redo logs also generated so does archive log
• DML triggers are not fired because the data itself has not changed
• Indexes are in a usable state after shrinking the corresponding table

Benefits and why we need to shrink segments:

• improves the performance of scan
• improve the DML operations

This is because there are fewer blocks to look at after the segment has been shrunk, and is especially for full table scans and for better index access.

Here we are going to perform in 12c database but same can be done with 11g, 10g. Let’s create one tablespace name ktexpts after that let’s create one table name ktexpts and insert some data in table.

 

Let’s increase tablespace size by resizing datafile as we are going to insert a lot of data in this tablespace;

To insert some data in tablespace, we need to create first one table so we are going to create one table name ktexpts.

Let’s insert some data into table ktexpts.

–Let’s insert more data in same table and after that to check how many rows available in tablespace execute below command. As we can see 4,10,000rows available in table ktexpts.

As we checked above query total size of segment in tablespace is 38MB.

Now, let’s delete some data from table to check how High water mark will work and to check tablespace would regain the space that was consumed by deleted rows.

To check data is deleted from table or not, execute below command. As we can see below output rows deleted successfully.

After deleting rows from table, we need to check again segment size in tablespace. As we can see below output segment size is still same as previously.

Let’s delete more rows from table and after that commit.

We can check again segment size but as we can see below query output that after deleting a lot of rows still segment size is same means no changes in used space in tablespace ktexpts.

This is my point that we have deleted successfully a lot of rows from table but still high water mark didn’t move

As we are going to shrink table or going to make some room in the tablespace before that we need to analyse table by using analyse command, the ANALYZE command gets the necessary statistics to do this computation. In below example we will use same table ktexpts and after that we will analyse that weather we can go for shrink operation or not.

The AVG_SPACE column shows the average amount of free space per block. You can use the below formula to calculate the amount of empty space in the ktexpts table.

Total empty space available in ktexpts table=>

(AVG_SPACE – (DB_BLOCK_SIZE × FREE_SPACE)) × TAB_BLOCKS =>

(2899 – (8192 *0.10)) * 4780 => 99,41,444 (Total in MB => 9.48MB out of 38MB)

Looking at free space in tablespace, we can go for shrinking segment.

 

You must enable row movement on the corresponding segment before executing a shrink operation on that segment.

The diagram below describes the two phases of a table shrink operation. Compaction is performed in the first phase. During this phase, rows are moved to the left part of the segment as much as possible. After the rows have been moved, the second phase of the shrink operation is started. During this phase, the high water mark is adjusted and the unused space is released. During a shrink operation, you can execute only the compaction phase by specifying the SHRINK SPACE COMPACT clause. This is illustrated by the first example in the below diagram. As shown by the second example in the slide, if COMPACT is not specified, the segment space is compacted, and at the end of the compaction phase the high water mark is adjusted and the unused space is released.However, only packets of rows are locked at one time to avoid the locking of the entire segment. Conventional DML operations as well as queries can co-exist with the segment shrink operation. During the second phase of the segment shrink operation, when the high water mark is adjusted, the object is locked in exclusive mode. This occurs for a very short duration and does not affect the availability of the segment significantly. Dependent cursors, however, are invalidated at that point.

To recover space, but don’t amend the high water mark (HWM).

To recover space and amend the high water mark (HWM).

To recover space for the object and all dependent objects.

When we checked above query output of segment size in tablespace, it decreased by 11MB after shrinking segment operation. This is how we can shrink segment and release space.

 

Thanks for browsing ktexperts.com ………