Fragmentation is a common issue which we try to avoid whenever possible. It can appear in many ways and components and can cause all kind of problems. In this article I will discuss tablespace fragmentation, which causes a waste of space. There can be many causes of tablespace fragmentation, however, I never thought that a frequent “shrink table” command would cause such a fragmentation so quickly.
Until Oracle 10gR1, when the High Water Mark (HWM) of the table moved forward (as new rows were inserted into the table), it could not be moved backwards in order to decrease the table size and de-allocate space, releasing it back to the tablespace free space. When we deleted many rows from the table, the HWM and the table size remained and the only way to decrease its size was by truncating the table. In 10gR1, Oracle introduced an exciting feature called “shrink table”.
How does it work? When we perform the shrink command, Oracle uses row movement (which must be enabled on the table) to move rows from the last blocks of the table to the beginning of the table. After moving the rows, a table lock takes place while Oracle moves the HWM backwards. Then, the blocks after the HWM can be released and the table size is reduced.
As you can see below, in normal operation, a table has “used blocks” (the blue one) and “empty blocks” (the orange ones). When rows are inserted into table and there is no space in the “used block”, Oracle moves the HWM (the black line) towards the end of the table, marking the “empty blocks” as “used”. Once there are no free blocks left, another extent is allocated.
Now let’s see how shrink table works. In the diagram below we see that the HWM is at the end of the table. Assuming we deleted many rows, there are now many places in the table blocks to contain new rows. The shrink table command will move the rows from the end of the table into free places closer to the beginning of the table (first drawing). Then, Oracle can move the HWM to the last row of the table, which is now not at the end of the table (second drawing). Once the HWM has been moved, the blocks beyond it are considered as free blocks (third drawing) and can then be released from the table back to the tablespace (last drawing)
The shrink table command works only for segments located in a locally managed tablespace with automatic segment space management.
When working with locally managed tablespaces, we have two ways to configure extent allocation:
Let’s see how system allocation works:
The above query will show the extents and their size. In my case it returns 202 rows, so I’ll sum it up: The first 16 extents were the size of 64KB (8 blocks of 8KB in my database). The next 63 extents were the size of 1MB. The next 120 extents were the size of 8MB. The three left are 64MB. The idea is quite simple to understand.
We saw the idea of shrink table and we understand that it is a very useful command. Now we’ll try to understand how much space is released back to the tablespace. In order to do this, we will perform the following:
Now we will use the query we used before to get the extent allocation:
Like before, I won’t post the entire output here, just the summary:
Now we will delete rows from the tables and shrink the tables to see the extent map.
Now the extent map shows:
The last thing we’ll do is to add 2500 rows back to the tables:
And now the extent map shows:
The problem resides in something we didn’t check or think about. 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:
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:
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.
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 that requires planning and downtime.
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:
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.
Liron is a senior DBA, with over 11 years of experience. During these years Liron worked as a senior consultant with a large number of companies in various fields and managed an Oracle Consulting Team. He mainly specializes in high availability solutions, performance, backup and recovery, and other infrastructure and application database areas. He is also a well known senior instructor and lectures in Oracle courses, events and forums. Liron is currently the VP of Professional Services at Brillix, a leading Oracle consulting company in Israel.