Oracle Tablespace Fragmentation

Extents and segments let you control the way Oracle stores data, while still optimizing storage. The interaction between different extents within a tablespace can create a problem that can impact performance.

• Tablespace fragmentation
  A tablespace can contain many different segments for different database objects. Each of these extents can be a different size. Extents are added to a tablespace as additional storage space for each database object is needed. Extents can also be dropped from a tablespace, as when their database objects are dropped. This can lead to a condition know as tablespace fragmentation, where a tablespace contains holes that cannot be reused for new extents. The following series of images show how this type of fragmentation can occur.

Tablespace fragmentation in Oracle can be identified by analyzing the free and used space in a tablespace. You can use the following SQL statement to examine tablespace fragmentation by querying the "DBA_FREE_SPACE" and "DBA_EXTENTS" views:
SQL Query to Identify Tablespace Fragmentation

SELECT 
    tablespace_name,
    COUNT(*) AS fragment_count,
    MAX(bytes) AS largest_fragment,
    MIN(bytes) AS smallest_fragment,
    AVG(bytes) AS average_fragment_size,
    SUM(bytes) AS total_free_space
FROM 
    dba_free_space
GROUP BY 
    tablespace_name
ORDER BY 
    tablespace_name;

Explanation of the Query

• tablespace_name: Groups the fragmentation analysis by tablespace.
• COUNT(*): Shows the number of free space fragments, indicating potential fragmentation if the count is high.
• MAX(bytes): Displays the size of the largest free space fragment in the tablespace.
• MIN(bytes): Displays the size of the smallest free space fragment.
• AVG(bytes): Calculates the average size of free space fragments.
• SUM(bytes): Shows the total amount of free space in the tablespace.

Analyzing the Results

1. A high fragment count indicates significant fragmentation.
2. A large difference between largest_fragment and smallest_fragment suggests uneven space distribution.
3. A small average fragment size relative to the required extent size can lead to allocation failures for large objects.

Additional Steps
If you want to analyze the used and free space distribution more closely, you can join "DBA_FREE_SPACE" with "DBA_EXTENTS" to understand how free and allocated extents are spread across the tablespace.
Query for Free and Allocated Space

SELECT 
    a.tablespace_name,
    a.total_space,
    NVL(b.free_space, 0) AS free_space,
    (a.total_space - NVL(b.free_space, 0)) AS used_space,
    ROUND((NVL(b.free_space, 0) / a.total_space) * 100, 2) AS free_percent,
    ROUND(((a.total_space - NVL(b.free_space, 0)) / a.total_space) * 100, 2) AS used_percent
FROM 
    (SELECT tablespace_name, SUM(bytes) AS total_space FROM dba_data_files GROUP BY tablespace_name) a
LEFT JOIN 
    (SELECT tablespace_name, SUM(bytes) AS free_space FROM dba_free_space GROUP BY tablespace_name) b
ON 
    a.tablespace_name = b.tablespace_name
ORDER BY 
    a.tablespace_name;

Recommendations for Resolving Fragmentation

1. Tablespace Coalescing: For dictionary-managed tablespaces, use:

   ALTER TABLESPACE <tablespace_name> COALESCE;
       

2. Locally Managed Tablespaces: Locally managed tablespaces generally do not suffer from fragmentation due to their uniform extent size allocation. Consider converting dictionary-managed tablespaces to locally managed.
3. Export/Import: Export the fragmented objects, drop them, and re-import them to create contiguous extents.

By running these queries and analyzing the output, you can detect and address tablespace fragmentation effectively.

• If there is no SQL statement, then which tool should I use?
  Answer: It depends on how you define "fragmentation". In my opinion, in version 8.1.5 (Oracle8i and up), fragmentation is an impossible situation to be in. My defininition of fragmentation is that you have many (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape).
  In Oracle8i, we would all use locally managed tablespaces. These would use either UNIFORM sizing (my favorite) or our automatic allocation scheme. In either case, it is pretty much impossible to get into a situation where you have unusable free space. To see if you suffer from "fragmentation", you can query DBA_FREE_SPACE (best to do an alter tablespace coalesce first to ensure all contigous free regions are made into 1 big free region). DBA_FREE_SPACE will report the size of all free extents. You would look for ANY free extent that is smaller then the smallest NEXT extent size for any object in that tablespace.
  Below I artifically introduce this issue by using a dictionary managed tablespace and objects with pctincrease=50. I create two tables and then allocate extents to them one after the other so that they are "interleaved". Then I drop one of the tables and find all of the free extents that are too small to hold the next extent for the smallest next extent in that tablespace.

ggould@GGOULD817> drop tablespace t including contents;
Tablespace dropped.

ggould@GGOULD817> create tablespace t
  2  datafile 'c:\temp\t.dbf' size 10m
  3  reuse
  4  /
Tablespace created.

ggould@GGOULD817> create table t_t1 ( x int )
  2  storage ( initial 1k next 1k pctincrease 50 )
  3  tablespace t
  4  /
Table created.

ggould@GGOULD817> create table t_t2 ( x int )
  2  storage ( initial 1k next 1k pctincrease 50 )
  3  tablespace t
  4  /

Table created.

ggould@GGOULD817> alter table t_t1 allocate extent;
Table altered.

ggould@GGOULD817> alter table t_t2 allocate extent;
Table altered.

... (above 2 commands executed in order over and over) ....

ggould@GGOULD817> drop table t_t1;
Table dropped.

ggould@GGOULD817> select *
  2    from dba_free_space
  3   where tablespace_name = 'T'
  4     and bytes <= ( select min(next_extent)
  5              from dba_segments
  6             where tablespace_name = 'T')
  7   order by block_id
  8  /

TABLESPACE_NAME             FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------- ---------- ---------- ---------- ---------- ------------
T                                9          2      16384          2            9
T                                9          6       8192          1            9
T                                9          8      16384          2            9
T                                9         12      24576          3            9
T                                9         18      40960          5            9
T                                9         28      81920         10            9
T                                9         48     122880         15            9
T                                9         78     163840         20            9
T                                9        118     245760         30            9
T                                9        178     368640         45            9

10 rows selected.

ggould@GGOULD817> spool off

The overall effect of this fragmentation is twofold:

1. Your Oracle database will require more storage space, because the tablespace contains a large portion of wasted space, and
2. accessing data within the fragmented tablespace will take longer, because the larger tablespace will require more disk head movement.

• Diagnosing tablespace fragmentation
  The problem of tablespace fragmentation only occurs as your tablespace is used over time. How will you know when your tablespaces have become fragmented?
  You can use a variety of queries against the data dictionary views that report on space usage in the tablespace, but one of the easiest ways to determine whether you have a tablespace fragmentation problem is to use the Tablespace Manager. The Tablespace Manager is a part of the Add-on Tuning Pack for Oracle Enterprise Manager.
• Fragmentation and Reorganization:
  Fragmentation is a problem that can negatively impact performance and one that many DBAs have struggled to manage in the past. Fragmentation can be an unwanted phenomenon if it results in small parts of noncontiguous "free space" that cannot be reused. In Oracle, a collection of contiguous blocks is referred to as an extent. A collection of extents is referred to as a segment. Segments can contain anything that takes up space. For example, a table, an index, or a rollback segment. Segments typically consist of multiple extents. As one extent fills up, a segment begins to use another extent. As fragmentation occurs, by database activity that leaves "holes" in the contiguous space represented by extents, segments acquire additional extents. As fragmentation grows, increased I/O activity results in reduced performance.

The next lesson shows how to avoid and fix fragmentation.