Fragmentation and Table shrink in Oracle database

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.

The shrink table command works only for segments located in a locally managed tablespace with automatic segment space management

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:

·         Create two tablespaces, one (tbs_uni) will use uniform extent allocation of 1MB, the other one (tbs_sys) will use system extent allocation.

·         Create a table in each tablespace (with the same structure and data).

·         Enable row movement for both tables.

·         Check the extents of the tables.

·         Delete rows from both tables.

·         Shrink both tables.

·         Check the extents of the tables to see the released space.

rajesh@PDB1> select tablespace_name,initial_extent,extent_management,allocation_type

  2  from dba_tablespaces

  3  where tablespace_name in ('TBS_SYSTEM','TBS_UNIFORM') ;

 

TABLESPACE_NAME INITIAL_EXTENT EXTENT_MAN ALLOCATIO

--------------- -------------- ---------- ---------

TBS_UNIFORM            1048576 LOCAL      UNIFORM

TBS_SYSTEM               65536 LOCAL      SYSTEM 

2 rows selected.

rajesh@PDB1>

rajesh@PDB1> create table tbl_uniform (id number, x char(1024))

  2  enable row movement

  3  pctfree 0 tablespace tbs_uniform; 

Table created. 

rajesh@PDB1>

rajesh@PDB1> create table tbl_system (id number, x char(1024))

  2  enable row movement

  3  pctfree 0 tablespace tbs_system; 

Table created. 

rajesh@PDB1> begin

  2     insert into tbl_uniform(id,x)

  3     select level , '*'

  4     from dual

  5     connect by level <= 100000;

  6

  7     insert into tbl_system(id,x)

  8     select level , '*'

  9     from dual

 10     connect by level <= 100000;

 11

 12     commit;

 13  end;

 14  / 

PL/SQL procedure successfully completed.

rajesh@PDB1>

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_UNIFORM'

  4  group by segment_name,blocks

  5  order by 2; 

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_UNIFORM                          1024        113 

1 row selected. 

rajesh@PDB1>

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_SYSTEM'

  4  group by segment_name,blocks

  5  order by 2;

 

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_SYSTEM                             64         16

TBL_SYSTEM                           1024         63

TBL_SYSTEM                           8192          7 

3 rows selected. 

rajesh@PDB1>

 

For table TBL_UNIFORM, which is located in uniform tablespace, we see 113 extents each 1MB 

For table TBL_SYSTEM, which is located in system allocation we see total 86 extents of which first 16 are 64Kb, next 63 are 1MB and last 7 are 8MB.  

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

rajesh@PDB1> delete from tbl_uniform where id <= 2500; 

2500 rows deleted. 

rajesh@PDB1> delete from tbl_system where id <= 2500; 

2500 rows deleted. 

rajesh@PDB1> commit; 

Commit complete. 

rajesh@PDB1> alter table tbl_uniform shrink space; 

Table altered. 

rajesh@PDB1> alter table tbl_system shrink space; 

Table altered. 

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_UNIFORM'

  4  group by segment_name,blocks

  5  order by 2; 

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_UNIFORM                          1024        111

 

1 row selected.

 

rajesh@PDB1>

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_SYSTEM'

  4  group by segment_name,blocks
  5  order by 2;

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_SYSTEM                             64         16

TBL_SYSTEM                           1024         63

TBL_SYSTEM                           6208          1

TBL_SYSTEM                           8192          5 

4 rows selected.

 

rajesh@PDB1>

 

Now the extent maps shows, 

For table TBL_UNIFORM we see 111 extents, each 1MB (two extents less than before) 

For table TBL_SYSTEM we see 85 extents (one extent less than before), the first 84 extents are exactly as before, while the last one was reduced to over 6Mb. 

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

rajesh@PDB1> begin

  2     insert into tbl_uniform(id,x)

  3     select level , '*'

  4     from dual

  5     connect by level <= 2500;

  6
  7     insert into tbl_system(id,x)

  8     select level , '*'

  9     from dual

 10     connect by level <= 2500;

 11

 12     commit;

 13  end;

 14  / 

PL/SQL procedure successfully completed. 

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_UNIFORM'

  4  group by segment_name,blocks

  5  order by 2;

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_UNIFORM                          1024        113 

1 row selected.

rajesh@PDB1>

rajesh@PDB1> select segment_name, (blocks*8) as size_KB ,count(*)

  2  from user_extents

  3  where segment_name ='TBL_SYSTEM'

  4  group by segment_name,blocks

  5  order by 2;

 

SEGMENT_NAME                      SIZE_KB   COUNT(*)

------------------------------ ---------- ----------

TBL_SYSTEM                             64         16

TBL_SYSTEM                           1024         63

TBL_SYSTEM                           6208          1

TBL_SYSTEM                           8192          6 

4 rows selected.

rajesh@PDB1> 

For table TBL_UNIFORM we see 111 extents, each 1MB (exactly as in the beginning) 

For table TBL_SYSTEM we see 86 extents again, the first 84 are exactly the same, the new one is 8Mb but the 85^th extent stayed just over 6Mb and did not grow to 8Mb again.

So where is the problem? ,the problem resides in tablespace, 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

 

rajesh@PDB1>

rajesh@PDB1> select file_id,segment_name,tablespace_name, (blocks*8)as size_Kb,

  2        min(block_id) min_blks, max(block_id) max_blks,

  3        count(block_id) as cnt_blks

  4  from (
  5  select file_id,segment_name,tablespace_name,blocks,block_id

  6  from dba_extents

  7  where segment_name ='TBL_UNIFORM'

  8  and owner = user

  9  union all
 10  select file_id,'Free_Space',tablespace_name,blocks,block_id
 11  from dba_free_space

 12  where tablespace_name ='TBS_UNIFORM'

 13       )

 14  group by file_id,segment_name,tablespace_name, blocks

 15  order by file_id,max_blks;

   FILE_ID SEGMENT_NAME    TABLESPACE_NAME    SIZE_KB   MIN_BLKS   MAX_BLKS   CNT_BLKS

---------- --------------- --------------- ---------- ---------- ---------- ----------

        13 TBL_UNIFORM     TBS_UNIFORM           1024        128      14464        113

        13 Free_Space      TBS_UNIFORM        1980416      14592      14592          1

 

2 rows selected.

 

rajesh@PDB1> 

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

rajesh@PDB1> select file_id,segment_name,tablespace_name, (blocks*8)as size_Kb,

  2        min(block_id) min_blks, max(block_id) max_blks,

  3        count(block_id) as cnt_blks

  4  from (

  5  select file_id,segment_name,tablespace_name,blocks,block_id

  6  from dba_extents

  7  where segment_name ='TBL_SYSTEM'

  8  and owner = user

  9  union all

 10  select file_id,'Free_Space',tablespace_name,blocks,block_id

 11  from dba_free_space

 12  where tablespace_name ='TBS_SYSTEM'

 13       )

 14  group by file_id,segment_name,tablespace_name, blocks

 15  order by file_id,max_blks;

 

   FILE_ID SEGMENT_NAME    TABLESPACE_NAME    SIZE_KB   MIN_BLKS   MAX_BLKS   CNT_BLKS

---------- --------------- --------------- ---------- ---------- ---------- ----------

        14 TBL_SYSTEM      TBS_SYSTEM              64        128        248         16

        14 TBL_SYSTEM      TBS_SYSTEM            1024        256       8192         63

        14 TBL_SYSTEM      TBS_SYSTEM            6208      13440      13440          1

        14 Free_Space      TBS_SYSTEM             960      14216      14216          1

        14 TBL_SYSTEM      TBS_SYSTEM            8192       8320      14336          6

        14 Free_Space      TBS_SYSTEM         1974272      15360      15360          1

 

6 rows selected. 

rajesh@PDB1>

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. 

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.

 

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 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.