Shrinking fragmented free space on Oracle tables

Oracle tables can become fragmented due to excessive Data Manipulation Language (DML) operations such as insert, update, and delete.

When rows are inserted into a table, the High Water Mark (HWM), which indicates the boundary between used and unused space in a table, moves forward to accommodate the new rows. However, when rows are deleted, Oracle does not allow the HWM to shift backward, which means the space from the deleted rows is not immediately released or reused.

As a result, even though the number of rows in the table might be less, due to fragmentation, the table can consume more space. This is because Oracle acquires new blocks to accommodate new rows for insertion, which can create “holes” or pockets of unused space in the table.

What is the High Water Mark (HWM) and why is it important?

In an Oracle database, the High Water Mark (HWM) refers to the maximum amount of storage space ever used by a table. Imagine a table as a series of blocks that hold data. The HWM signifies the rightmost block that has ever contained data, even if that data is now deleted.

Here’s why the HWM is important:

• Performance Impact: During operations like full table scans, Oracle reads all blocks up to the HWM, regardless of whether they currently hold data. This can significantly slow down performance if a large portion of the table space is empty beyond the actual data.
• Storage Utilization: A high HWM can indicate wasted space. Even though data might be deleted, the table still allocates those blocks, potentially impacting storage efficiency.

Here’s an analogy: Think of the HWM like the highest water level a lake has ever reached. Even during dry spells, the shoreline may extend further due to the high water mark, impacting the usable area.

Understanding the HWM helps database administrators:

• Optimize performance: By analyzing the HWM and data usage patterns, they can identify space-wasting tables and potentially reclaim unused storage.
• Plan for future growth: Knowing the HWM allows for anticipating future storage needs and preventing unexpected space limitations.
• Troubleshoot performance issues: A high HWM can be a clue that full table scans are inefficient due to excessive empty blocks.

During operations like full table scans, Oracle reads all blocks up to the HWM, regardless of whether they currently hold data.

Overall, the HWM is a valuable metric for understanding storage utilization and optimizing performance in Oracle databases.

How to reclaim wasted space within a table?

The ALTER TABLE SHRINK SPACE command in Oracle can be a helpful tool to reclaim wasted space within a table. Here’s a breakdown of how it works and some things to consider:

What it Does:

• This command instructs Oracle to reorganize the data blocks used by a table.
• During this process, it attempts to identify and reclaim unused space within the allocated extents.
• Ideally, this frees up storage space that can be used for new data insertions.
• Invoked with the COMPACT clause, the HWM will not be repositioned and DML operations are allowed.
• Invoked without the COMPACT clause, the HWM will be moved down to its new value. The downside is that any DML operation will be blocked during this process.
• Rows movement must be previously enabled on the table.

How to Use It:

The basic syntax is:

ALTER TABLE <tablename> ENABLE ROW MOVEMENT;
-- Enable rows to be moved and, subsequently, the ROWIDs may be changed.

ALTER TABLE <tablename> SHRINK SPACE COMPACT; 
-- It moves rows into contiguous blocks but does not change the HWM.
-- DML operations and queries are allowed during the compaction process.


ALTER TABLE <tablename> SHRINK SPACE; 
-- It moves rows into contiguous blocks and ADJUSTS the HWM
-- DML operations are BLOCKED during the compaction process.

Hands-on

In the following example, we are creating and populating a table named TEST and checking out the reclaimable free space before and after a massive delete. It’s worth noticing that the reclaim porcentaje rockets from a 19% up to a 91%. After the shrinking of the table it comes back to nearly a 17%.

CREATE TABLESPACE TS_TEST DATAFILE '/u01/app/oracle/oradata/ORCL/ts_test01.dbf' SIZE 1M AUTOEXTEND ON;
TABLESPACE TS_TEST created.

CREATE TABLE TEST TABLESPACE TS_TEST AS (select * from all_objects);
Table TEST created.

COLUMN FILE_NAME FORMAT A50
COLUMN OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A10

select TABLESPACE_NAME, FILE_NAME, round(BYTES / 1024 / 1204,2) MB
from dba_data_files
where TABLESPACE_NAME = 'TS_TEST';

TABLESPACE_NAME                FILE_NAME                                                  MB
------------------------------ -------------------------------------------------- ----------
TS_TEST                        /u01/app/oracle/oradata/ORCL/ts_test01.dbf              12,81


exec dbms_stats.gather_table_stats('SYS','TEST');
select owner, table_name, round((blocks*8),2) fragm_kb, round((num_rows*avg_row_len/1024),2) actual_kb, round(blocks*8 - num_rows*avg_row_len/1024,2) reclaim_kb ,round((blocks*8-num_rows*avg_row_len/1024)/(blocks*8)*100,2) reclaim_pc
from dba_tables where table_name ='TEST' AND OWNER LIKE 'SYS';

OWNER      TABLE_NAME   FRAGM_KB  ACTUAL_KB RECLAIM_KB RECLAIM_PC
---------- ---------- ---------- ---------- ---------- ----------
SYS        TEST            11392    9210,87    2181,13      19,15


delete from test where owner in ('SYS', 'PUBLIC');
63.663 rows deleted.

COMMIT;

exec dbms_stats.gather_table_stats('SYS','TEST');
select owner, table_name, round((blocks*8),2) fragm_kb, round((num_rows*avg_row_len/1024),2) actual_kb, round(blocks*8 - num_rows*avg_row_len/1024,2) reclaim_kb ,round((blocks*8-num_rows*avg_row_len/1024)/(blocks*8)*100,2) reclaim_pc
from dba_tables where table_name ='TEST' AND OWNER LIKE 'SYS';

OWNER      TABLE_NAME   FRAGM_KB  ACTUAL_KB RECLAIM_KB RECLAIM_PC
---------- ---------- ---------- ---------- ---------- ----------
SYS        TEST            11392    1004,31   10387,69      91,18


ALTER TABLE test ENABLE ROW MOVEMENT;
Table TEST altered.

ALTER TABLE test SHRINK SPACE;
Table TEST altered.
 
exec dbms_stats.gather_table_stats('SYS','TEST');
select owner, table_name, round((blocks*8),2) fragm_kb, round((num_rows*avg_row_len/1024),2) actual_kb, round(blocks*8 - num_rows*avg_row_len/1024,2) reclaim_kb ,round((blocks*8-num_rows*avg_row_len/1024)/(blocks*8)*100,2) reclaim_pc
from dba_tables where table_name ='TEST' AND OWNER LIKE 'SYS';

OWNER      TABLE_NAME   FRAGM_KB  ACTUAL_KB RECLAIM_KB RECLAIM_PC
---------- ---------- ---------- ---------- ---------- ----------
SYS        TEST             1208    1004,31     203,69      16,86

Important Considerations:

• Understanding the HWM (High Water Mark): While ALTER TABLE SHRINK SPACE COMPACT reclaims unused space, it doesn’t necessarily adjust the HWM. This means the table might still show as occupying the same amount of space even after reclaiming some.
• Potential Downtime: This operation might lock the table for a period, impacting any ongoing transactions. Consider using it during off-peak hours or planned maintenance windows.
• Data Row Movement: For ALTER TABLE SHRINK SPACE to work effectively, Oracle might need to physically move data rows within the table. This can be resource-intensive for large tables.
• ROWIDs changes: ALTER TABLE SHRINK SPACE rearranges data blocks within the table. This may involve physically moving rows to different locations, resulting in new ROWIDs being assigned.
• Large Objects: Large Object (LOB) fields in your Oracle tables can be a hidden culprit behind performance issues. While convenient for storing large data like text or images, frequent updates and deletes on LOB fields can lead to fragmentation.
• Alternative Approaches: Depending on the scenario, alternative methods like exporting data, dropping and recreating the table, or using ALTER TABLE MOVE might be more efficient for reclaiming space.

Additional Tips:

• You can use the CASCADE option with ALTER TABLE SHRINK SPACE to automatically adjust indexes associated with the table.
• Remember, reclaiming space is just one aspect of good database management. Regularly analyze your tables and consider techniques like partitioning for efficient data storage and retrieval.

Was this post helpful? Consider subscribing or leaving a comment.