Oracle DB - tablespace (de)fragmentation tutorial

This article describes how the objects are "physically" stored in Oracle DB. In tutorial is used on VM with preinstalled Oracle DB 12c.

Introduction - datafile structure

Every tablespace is one or group of multiple datafiles. Datafile has 3 levels of configuration

• File sizing - file size on hard disk, increasing size and max size.
• Extent management - size of extents etc. (automatic / uniform size)
• Segment management - automatic (common) / manual (PCT_USED, FREELISTS, GROUPS ... settings is necessary)

Every extent contains blocks (usual 4KiB or 8KiB) which contain rows.

Datafile structure

Tutorial

For this tutorial, connect to db as system (sys as sysdba).

Step 1 - create tablespace with one datafile

Parameters

• Initial size 30MiB, next 20MiB if needed, maximum of size is 100MB
• Extent has uniform size 4MiB - it is good for tutorial. Usually use automatic extent management.
• ASSM - automatic segment space management

define ts = TBS_TEST_DATA

--

-- Create tablespace

--

CREATE TABLESPACE "&ts."

DATAFILE '/home/oracle/test_file01.dbf' SIZE 30M AUTOEXTEND ON NEXT 20M MAXSIZE 100M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M

SEGMENT SPACE MANAGEMENT AUTO; --  MANUAL (PCTUSED, FREELISTS, and FREELISTS GROUPS), do not use it

Step 2 - create view TABLESPACE_MAP_VIEW

This view will be used repeately - show you content of tablespace (i.e. datafile):

create view TABLESPACE_MAP_VIEW as

SELECT   'free space' owner, '      ' OBJECT, dfs.file_id, dfs.block_id, dfs.blocks, dfs.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME

    FROM dba_free_space dfs, DBA_DATA_FILES ddf

   WHERE dfs.tablespace_name = UPPER ('&ts.') and ddf.file_id = dfs.file_id

UNION

SELECT   SUBSTR (dext.owner, 1, 20), SUBSTR (dext.segment_name, 1, 32), dext.file_id, dext.block_id, dext.blocks, dext.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME

    FROM dba_extents dext, DBA_DATA_FILES ddf

   WHERE dext.tablespace_name = UPPER ('&ts.') and dext.file_id = ddf.file_id

ORDER BY 3, 4;

Step 3 - create table TEST_TABLE and check the datafile

First, check tablespace map: select * from TABLESPACE_MAP_VIEW;
Result: 28MB of freespace (3584 blocks):

Now, create table with only one row but allocate initial 16MiB space:

--
-- create tmp table
--
create table TEST_TABLE tablespace TBS_TEST_DATA
storage (initial 16M NEXT 10M
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED)
as
  select rownum id, ao.* from ALL_OBJECTS ao where rownum<2 ; -- 0MB dat


Check the map: select * from TABLESPACE_MAP_VIEW;

You see, there will be allocated 16MB for test table (4 extents with uniform size 4MB). But table is almost empty! Contains only one row!

Step 4 - Insert some data

First, insert cca 13MB: 

--

-- insert 13mb

--

insert into TEST_TABLE

select rownum id, ao.* from ALL_OBJECTS ao; -- 13MB dat

commit;

Check it - nothing changed because of enough space was allocated before:

 And now, insert next 7MB:

--

-- insert 7mb

--

insert into TEST_TABLE

select rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB dat

commit;

Check it - rest of freespace was used and next 4MB was allocated (why only 4MB and not 10MB? - db probably rounds sizes)

Step 5 - add second table (GREAT_TABLE)

Add some another table:

--

-- great

--

create table GREAT_TABLE

tablespace TBS_TEST_DATA

storage (initial 4M NEXT 4K

          PCTINCREASE 0

          MINEXTENTS 1

          MAXEXTENTS UNLIMITED)

as

select rownum id, ao.* from ALL_OBJECTS ao; -- cca 14MB data




Check the tablespace map:

• Datafile was extended to 48MiB (add  cca 20MiB)
• new table needs 16MiB space

Step 6 - Create "tablespace level" fragmentation for TEST_TABLE

Insert next 7 MB into table, delete second table:

--

-- insert 7mb

--

insert into TEST_TABLE

select rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB dat

commit;

-- make a hole

drop table GREAT_TABLE;

See the map:

Again insert:

insert into TEST_TABLE

select rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB dat

commit;

The hole will not be filled because of "segment level fragmentation"!!!. Instead new extend will be allocated:

After delete some space...
 

-- delete "random", no space will be free

delete from TEST_TABLE where id < 60000;

commit;

...no space will be free: 







Step 7 - Defragmentation of TEST_TABLE ("tablespace level" and also "segment level" fragmentation)

Check the table block usage:

set serveroutput on;

-- fragmentation in blocks

declare

   v_unformatted_blocks number;

   v_unformatted_bytes  number;

   v_fs1_blocks         number;

   v_fs1_bytes          number;

   v_fs2_blocks         number;

   v_fs2_bytes          number;

   v_fs3_blocks         number;

   v_fs3_bytes          number;

   v_fs4_blocks         number;

   v_fs4_bytes          number;

   v_full_blocks        number;

   v_full_bytes         number;

 begin

   dbms_space.space_usage('SYS',

                          'TEST_TABLE',

                          'table',

                          v_unformatted_blocks,

                          v_unformatted_bytes,

                          v_fs1_blocks,

                          v_fs1_bytes,

                          v_fs2_blocks,

                          v_fs2_bytes,

                          v_fs3_blocks,

                          v_fs3_bytes,

                          v_fs4_blocks,

                          v_fs4_bytes,

                          v_full_blocks,

                          v_full_bytes);

   

   dbms_output.put_line('Unformatted Blocks = ' || v_unformatted_blocks);

   dbms_output.put_line('Unformatted Bytes = ' || v_unformatted_bytes);

   dbms_output.put_line('FS1 Bytes (at least 0 to 25% free space) = ' || v_fs1_bytes);

   dbms_output.put_line('FS1 Blocks(at least 0 to 25% free space) = ' || v_fs1_blocks);

   dbms_output.put_line('FS2 Bytes (at least 25 to 50% free space)= ' || v_fs2_bytes);

   dbms_output.put_line('FS2 Blocks(at least 25 to 50% free space)= ' || v_fs2_blocks);

   dbms_output.put_line('FS3 Bytes (at least 50 to 75% free space) = ' || v_fs3_bytes);

   dbms_output.put_line('FS3 Blocks(at least 50 to 75% free space) = ' || v_fs3_blocks);

   dbms_output.put_line('FS4 Bytes (at least 75 to 100% free space) = ' || v_fs4_bytes);

   dbms_output.put_line('FS4 Blocks(at least 75 to 100% free space)= ' || v_fs4_blocks);

   dbms_output.put_line('Full Blocks in segment = ' || v_full_blocks);

   dbms_output.put_line('Full Bytes in segment  = ' || v_full_bytes);

 end;

Many blocks are fragmented:

• Low HWM and HWM is at the end => by adding new data will not be used free space and will be allocated a new extent (see Segment Space and the High Water Mark on page https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm)
• Wasting space - many blocks are filled 0-75%, only few are full.

Shrinking table makes some data free:

--

-- "release free space" - shrink

--

alter table TEST_TABLE enable row movement;

alter table TEST_TABLE shrink space;

Alter table TEST_TABLE shrink space cascade;

alter table TEST_TABLE disable row movement;

 -- deallocate unused extents

ALTER TABLE TEST_TABLE DEALLOCATE UNUSED;

Table is almost defragmented (one block is 50% to 75%)

See the map:

 

Defragmentation by move storage. Init 16MiB for new extents:

-- "free space" - move to new extents

--

alter table TEST_TABLE move

storage (initial 16M NEXT 10M

          PCTINCREASE 0

          MINEXTENTS 1

          MAXEXTENTS UNLIMITED);

Result of block usages:

 Move storage contains init size (in this case 16MB), therefore 4 extents are allocated. And also first 8MB are free because there was the table stored before move:

"Move storage" benefits:

• choose some parameters similar to table creation (init size etc...)
• better "block level" defragmentation
• should be fast 

"shrink" benefits:

• less capacity (does not create new extends)
• in some cases better "extent level" defragmentation

Step 8 - cleanup

--

-- DROP

--

drop table TEST_TABLE;

drop table GREAT_TABLE;

DROP TABLESPACE "&ts." INCLUDING CONTENTS AND DATAFILES;