Managing Fragmentation and Table Shrink in Oracle Database

Introduction

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.

The Table Shrink Command

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)

Locally Managed Tablespaces

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:

• System allocation - Oracle decides on the extent sizes, regardless of any user configuration. The segments start with small extents and as they grow, Oracle allocates larger and larger extents.
• Uniform size - all extents in the tablespace are of the same size, configured when the tablespace was created.

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.

How Much Space Does Shrink Table Release?

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:

• 1.Create two tablespaces, one (tbs_uni) will use uniform extent allocation of 1MB, the other one (tbs_sys) will use system extent allocation.
• 2.Create a table in each tablespace (with the same structure and data).
• 3.Enable row movement for both tables.
• 4.Check the extents of the tables.
• 5.Delete rows from both tables.
• 6.Shrink both tables.
• 7.Check the extents of the tables to see the released space.

 




 




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:

• For table TAB_UNI, which is located in a uniform allocation tablespace, we see 113 extents, 1MB each.
• For table TAB_SYS, which is located in a system allocation tablespace, we see 86 extents. As expected, the first 16 are 64KB, the next 63 are 1MB, and the last 7 are 8MB.



Impressed with our Course Content?
Attend a Free Demo Session to Experience our Quality!

BOOK A FREE DEMO



Now we will delete rows from the tables and shrink the tables to see the extent map.



Now the extent map shows:

• For table TAB_UNI we see 111 extents, 1MB each. Two extents less than before.
• For table TAB_SYS we see 85 extents. One extent less than before. The first 84 extents are exactly as before, while the last one was reduced to just over 6MB. This makes sense, since the extents grow in time, and we can allocate extents at any size.

The last thing we’ll do is to add 2500 rows back to the tables:

 




And now the extent map shows:

• For table TAB_UNI we see 113 extents, 1MB each. Exactly as in the beginning.
• For table TAB_SYS we see 86 extents again. However, the first 84 are exactly the same as before, and the new one (the 86th) is 8MB, but the 85th extent stayed just over 6MB and didn’t grow to 8MB again.

So Where Is The Problem?

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.

Defrag a 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 that requires planning and downtime.

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.




• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy