Shrink Table and Fragmentation in Oracle, Part 1

This is an article that was published in the past, however, it’s not available anymore so I decided to post it here.

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 Shrink Table 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 using “alter table … enable row movement”) 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:

1. 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.
2. 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:

SQL> select extent_management, allocation_type 
  2  from dba_tablespaces 
  3  where tablespace_name='USERS';

EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL      SYSTEM

SQL> create table big_table(t char(1000)) tablespace users;

Table created.
SQL> begin
  2  insert into big_table values('test');
  3  for i in 1..20
  4  loop
  5     insert /*+ append */ into big_table select * from big_table;
  6     commit;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from big_table;

  COUNT(*)
----------
   1048576

SQL> select extent_id, bytes/1024 
  2  from user_extents 
  3  where segment_name='BIG_TABLE' 
  4  order by extent_id;

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.

SQL> create tablespace tbs_uni
  2  datafile 'C:\ORACLE\ORADATA\DB112\tbs_uni.dbf' size 2000m
  3  extent management local uniform size 1m;

Tablespace created.

SQL>
SQL> create tablespace tbs_sys
  2  datafile 'C:\ORACLE\ORADATA\DB112\tbs_sys.dbf' size 2000m
  3  extent management local;

Tablespace created.

SQL> select
  2     tablespace_name,
  3     extent_management,
  4     allocation_type,
  5     initial_extent 
  6  from dba_tablespaces 
  7  where tablespace_name like 'TBS%';

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO INITIAL_EXTENT
------------------------------ ---------- --------- --------------
TBS_SYS                        LOCAL      SYSTEM             65536
TBS_UNI                        LOCAL      UNIFORM          1048576

SQL> create table tab_uni (id number, a char(1024))
  2  pctfree 0 tablespace tbs_uni;

Table created.

SQL> create table tab_sys (id number, a char(1024)) 
  2  pctfree 0 tablespace tbs_sys;

Table created.

SQL> alter table tab_uni enable row movement;

Table altered.

SQL> alter table tab_sys enable row movement;

Table altered.

SQL> begin
  2     for i in 1..100000
  3     loop
  4             insert into tab_sys values(i,'text');
  5             insert into tab_uni values(i,'text');
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Now we will use the query we used before to get the extent allocation:

select extent_id, bytes/1024
from user_extents
where segment_name='&tab_name'
order by extent_id;

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.

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

SQL> delete from tab_uni where id<=2500;

2500 rows deleted.

SQL> delete from tab_sys where id<=2500;

2500 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table tab_uni shrink space;

Table altered.

SQL> alter table tab_sys shrink space;

Table altered.

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:

SQL> begin
  2     for i in 1..2500
  3     loop
  4             insert into tab_sys values(i,'text');
  5             insert into tab_uni values(i,'text');
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

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 86^th) is 8MB, but the 85^th extent stayed just over 6MB and didn’t grow to 8MB again.

Short Summary and Part 2

The original article is a bit long, so I decided to break it into 2 posts. The first one (this) describes the shrink command and how extents are allocated and de-allocated. The next part describes a problem I found with this behavior and how to avoid it.