LLI Wiki — My Digital Knowledge Base

Oracle: detect and reclaim table and tablespace fragmentation

Queries to measure fragmentation of Oracle tablespaces, tables and datafiles, and ways to reclaim space (shrink, move, index rebuild, undo).

This article gathers queries to detect space fragmentation in an Oracle database (tablespaces, tables, datafiles) and the usual ways to reclaim the wasted space.

Concepts

The high water mark (HWM) is the boundary between blocks that have been used and blocks that have never been used in a segment. After many deletes, a table can hold a large number of empty blocks below the HWM: full scans still read them and the datafile cannot be shrunk.

You can compute the HWM as follows (DBMS_SPACE.UNUSED_SPACE also gives it):

High Watermark = Total blocks - Unused blocks - 1
select blocks from dba_segments where owner = upper('HR') and segment_name = upper('JOBS');

analyze table hr.jobs estimate statistics;

select empty_blocks from dba_tables where owner = upper('HR') and table_name = upper('JOBS');

Identify fragmented tablespaces

Fragmentation index per tablespace

Number of free chunks, largest free chunk and a fragmentation index (the higher the index, the less fragmented the tablespace):

SELECT
  tablespace_name,
  count(*) free_chunks,
  decode(round((max(bytes) / 1024000),2),
         null,0,
         round((max(bytes) / 1024000),2)) largest_chunk,
  nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
FROM
  sys.dba_free_space
GROUP BY
  tablespace_name
ORDER BY 2 desc, 1;

Content of a tablespace

select distinct segment_type from dba_segments where tablespace_name = 'MY_TABLESPACE';

select distinct owner, segment_name, partition_name, segment_type, segment_subtype, tablespace_name
from dba_segments
where tablespace_name = 'MY_TABLESPACE';

Datafiles of a tablespace

column FILE_NAME format a80
column BLOCKS format 999999999
column TABLESPACE_NAME format a30
SELECT
  FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM
  DBA_DATA_FILES
WHERE
  TABLESPACE_NAME = 'MY_TABLESPACE';

Extent map of a tablespace

Free space and extents of a tablespace, ordered by file and block (SQL*Plus substitution variable &ts):

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;

Identify fragmented tables

The queries below compare the space allocated to a table (blocks) with the space the rows should use (num_rows * avg_row_len). They rely on optimizer statistics: make sure they are recent. They also assume a given block size (8 KB for blocks*8, 16 KB for blocks*16): adapt the factor to your db_block_size.

Fragmented tables of the whole database

Tables with more than 100 MB of difference between allocated and actual size:

set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner, table_name, blocks, num_rows, avg_row_len,
       round(((blocks*8/1024)),0) "TOTAL_SIZE",
       round((num_rows*avg_row_len/1024/1024),0) "ACTUAL_SIZE",
       round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) "FRAGMENTED_SPACE"
from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT')
  and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100
order by 8 desc;

Top 25 fragmented tables of a schema

select * from (
  select owner, table_name, round((blocks*8),2) "size (kb)",
         round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
         (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)",
         ((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) /
           round((blocks * 8), 2)) * 100 - 10 "reclaimable space % "
  from dba_tables
  where owner in ('&SCHEMA_NAME')
    and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
  order by 5 desc )
where rownum < 25;

Fragmentation percentage of one table

select table_name, avg_row_len,
       round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
       round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
       round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
       (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables
where table_name = '&TABLE_NAME';

Physical size versus data size

Physical size occupied by the table in the datafiles:

select table_name, round((blocks*8)/1024,2)||'MB' "size"
from dba_tables
where table_name = 'MY_TABLE' and owner = 'MY_SCHEMA';

Estimated size of the data it actually holds:

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size"
from dba_tables
where table_name = 'MY_TABLE' and owner = 'MY_SCHEMA';

Estimated gain with DBMS_SPACE

A method based on DBMS_SPACE estimates the percentage of space a reorganisation would give back. It needs a helper function (table_saving_function) and the create_table_cost / table_saving scripts described in the article linked in the references. The selection of candidate tables is:

select a.owner, a.table_name, table_saving_function(a.owner, a.table_name) as percentage_gain
from dba_tables a
where a.owner = 'MY_SCHEMA'
and a.status = 'VALID'             -- In valid state
and a.iot_type is null             -- IOT tables not supported by dbms_space
--and external = 'no'              -- starting from 12cR2
and not exists (select 'x' from dba_external_tables b
                where b.owner = a.owner and b.table_name = a.table_name)
and temporary = 'N'                -- Temporary segment not supported
and a.last_analyzed is not null    -- Recently analyzed
order by 3 desc;

Reclaim the space

Common solutions, from the least to the most intrusive:

  1. Rebuild the indexes.
  2. Shrink the table, or move it (to another tablespace or the same one).
  3. Export and re-import the data (Data Pump, expdp / impdp).
  4. COALESCE the free extents (to be tested before use).

In locally managed tablespaces, Oracle can also defragment tables and indexes online.

Shrink a table

SHRINK SPACE needs row movement enabled and compacts the data online. Gather statistics before and after to measure the result:

select table_name, last_analyzed, stale_stats from dba_tab_statistics where table_name = 'MY_TABLE';

EXEC dbms_stats.gather_table_stats('MY_SCHEMA','MY_TABLE');

ALTER TABLE MY_SCHEMA.MY_TABLE enable ROW movement;

ALTER TABLE MY_SCHEMA.MY_TABLE shrink SPACE;

EXEC dbms_stats.gather_table_stats('MY_SCHEMA','MY_TABLE');

With the helper scripts mentioned above, the same sequence is wrapped by a before and after measure:

@create_table_cost MY_SCHEMA MY_TABLE
@table_saving MY_SCHEMA MY_TABLE
-- enable row movement, shrink space, gather statistics (see above)
@table_saving MY_SCHEMA MY_TABLE

Move a table and rebuild its indexes

A MOVE rewrites the table but makes its indexes UNUSABLE: rebuild them afterwards and gather statistics.

alter table MY_SCHEMA.MY_TABLE move;

select index_name from dba_indexes where table_name = 'MY_TABLE';

alter index MY_INDEX_1 rebuild online;
alter index MY_INDEX_2 rebuild online;

EXEC dbms_stats.gather_table_stats(ownname => 'MY_SCHEMA', tabname => 'MY_TABLE', method_opt => 'for all indexed columns size skewonly', granularity => 'ALL', degree => 8, cascade => true, estimate_percent => 15);

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'MY_TABLE';

Shrink a datafile

If the datafile holds a single object, an online shrink may be enough to compact the data and free the empty blocks so that the datafile can be resized. A shrink only compacts the data: it does not guarantee that free blocks end up at the end of the file. If used blocks remain at the end, the datafile cannot be resized smaller and you must move the objects or use another method.

Extents per tablespace and segment type, and index segments:

select tablespace_name, count(*) as "# of extents", SEGMENT_TYPE,
       sum(BYTES) as "Total of Byte", sum(BLOCKS) as "Total of block"
from dba_extents
group by tablespace_name, SEGMENT_TYPE
order by tablespace_name;

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
--AND OWNER = 'HR'
ORDER BY SEGMENT_NAME;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' AUTOEXTEND OFF;

Recreate the undo tablespace

An oversized undo tablespace cannot always be shrunk. Switch to a temporary undo tablespace, drop the old one, recreate it with the wanted size, switch back, then drop the temporary one:

create undo tablespace UNDOTBS2 datafile '/path/undotbs2.dbf' size 500m;
alter system set undo_tablespace = undotbs2 scope=both;

drop tablespace UNDOTBS1 including contents and datafiles;

create undo tablespace UNDOTBS1 datafile '/path/undotbs1.dbf' size 500m autoextend on next 100m maxsize 10000m;
alter system set undo_tablespace = undotbs1 scope=both;

drop tablespace UNDOTBS2 including contents and datafiles;

Notes

References

Mastodon LLIMastodon SocialFosstodon