Shrink Table and Fragmentation in Oracle, Part 2

This is the second part of the article, in the previous part I explained how extent management and shrink table works. In this post I’ll dive into a problem that happens when you use the “shrink table” command often in a system allocation tablespace.

The Fragmentation Problem

The fragmentation problem resides in something we didn’t check or think about. Remember the extent map from the previous post? I checked the number of extents and their size. To find the problem we need to add to the extent map the physical location of the extent. The location of the extent in the file is according to the block_id column in DBA_EXTENTS table. The block_id column represents the id of the first block in the extent where 1 is the beginning of the file. To simplify things, I’ll use a single file in the relevant tablespace. This is the query we will use to see the full map of extents with their location:

select 
	mapping.file_id,
	mapping.block_id,
	(mapping.blocks*tbs.block_size)/1024 SIZE_KB,
	mapping.segment_name
from
	(select file_id,block_id,blocks,segment_name,tablespace_name
	 from dba_extents
	 where tablespace_name='TBS_UNI'
	 union all
	 select file_id,block_id,blocks,'Free Space',tablespace_name
	 from dba_free_space
	 where tablespace_name='TBS_UNI'
	) mapping,
	dba_tablespaces tbs
where tbs.tablespace_name=mapping.tablespace_name
order by mapping.file_id,mapping.block_id;

The result of this query will be the file_id of the relevant file, the extent information (block_id and the size) and the content (segment name or “free space”).

I will not post the entire result set. This is a part from the TBS_UNI result:

   FILE_ID   BLOCK_ID    SIZE_KB SEGMENT_NAME
---------- ---------- ---------- ------------------------------
         7      13952       1024 TAB_UNI
         7      14080       1024 TAB_UNI
         7      14208       1024 TAB_UNI
         7      14336       1024 TAB_UNI
         7      14464       1024 TAB_UNI
         7      14592    1931264 Free Space

All extents come one after the other, while the free space is at the end of the file.

For table TAB_SYS, however, this is part of the result:

   FILE_ID   BLOCK_ID    SIZE_KB SEGMENT_NAME
---------- ---------- ---------- ------------------------------
         8      11392       8192 TAB_SYS
         8      12416       8192 TAB_SYS
         8      13440       6272 TAB_SYS
         8      14224        896 Free Space
         8      14336       8192 TAB_SYS
         8      15360    1925120 Free Space

Note the free space after the 6MB extent. Remember the flow that got us to this situation. We filled the table, then deleted rows, shrank the table and inserted more rows. At first, the last extent of the table was 8MB in size, and when we shrank the table, the extent was reduced to about 6MB, releasing about 2MB back to the tablespace. When we inserted new rows, Oracle needed to allocate a new extent for them; however, it seems that extents cannot start at any block in the file. In our case, the new 8MB extent could not start at block 14224 (which is 111.125MB from the beginning of the file), but started at block 14336 which is exactly 112MB from the beginning of the file.

Since this tablespace uses system allocation extent management, other tables that need small extents may allocate this free space. Large tables, however, will not be able to allocate this space, and when allocating their next extent will leave an unused area.

To sum up the problem, when we shrink a table in a system allocation extent management tablespace, a part at the end of the table is released back to the tablespace’s free space. Usually, this leaves a smaller than usual last extent in the table. The next extent of the table will not always be able to start at exactly the next block, depending on the size of the requested extent. If this scenario happens, the space left between the extents can be allocated only for small extents, so usually not for extents of the same table or other large tables.

Now, there are two issues to talk about, one is how to defrag a tablespace and the other one is how to avoid the fragmentation.

Defrag the Tablespace

If we already have a fragmented tablespace, in order to eliminate the fragmentation we will have to drop the “problematic” extents. The problem is that we usually can’t do that as there is too much data in the table and the extents are too close to the beginning of the table to de-allocate. The only solution I can think of is to recreate the table by using the “alter table … move” command or “insert … select”. When the table is recreated, it allocates a new segment with new extents, releasing the old segment. After the old segment is dropped, the fragmentation doesn’t exist anymore, but this is a painful operation. In later versions of Oracle this can be completely online, but will still affect the system, so we better have a good plan.

Avoid the Fragmentation

The best thing to do is to avoid this problem altogether. It depends on your specific system and how you use the database. These are some points that might be worth considering:

• If you use system allocation tablespaces with big tables, and you delete and insert to these tables, you should consider not using the “shrink table” feature. That way new rows can use the space the deleted rows left without any problem or fragmentation.
• If you use “shrink table” on small tables, you probably won’t have problems.
• If you use “shrink table” on big tables, but not often, and you also have all kind of small tables allocating new extents on this tablespace, the fragmentation issue might not be a big one. The few empty spaces caused by the not frequent “shrink table” operations will be used by the small tables.
• If you only have large tables on a system allocation tablespace and you have to shrink the tables periodically, consider moving to uniform allocation with adequate extent size to avoid the fragmentation problem.

Summary

The “shrink table” command is very useful in many cases. However, there are use cases in which a serious fragmentation might occur. Handling this fragmentation is difficult, so it’s better to think ahead in order to avoid it.

In this article I tried to give you a real life example I ran into, in order to help you understand and prevent this fragmentation problem. I hope you will find it useful in your own environments.